Convert Recursive Directory Listing to MySQL Table

Question

I need to list all files/folders in a given parent folder, and dump it out to mysql.

So far I have:

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$dir = '/home/kevinpirnie/www';

function dirToArray( $dir ) {
    $result = array();
    $cdir = scandir($dir);
    foreach ($cdir as $key => $value) {
        if (!in_array($value, array(".", "..")))  {
            if (is_dir($dir . DIRECTORY_SEPARATOR . $value)){
                $result[$value] = dirToArray($dir . DIRECTORY_SEPARATOR . $value);
            } else {
                $result[] = $value;
            }
        }
    }
    return $result;
}

$res = dirToArray($dir);

echo '<hr />';
echo '<pre>';
print_r($res);
echo '</pre>';

What I am stuck on is how I can assign ID's to the directories, and then associate them with their parent ID's.

Right now, this code sort of does what I need it to, I just need to be able to convert it to mysql insert statements, yet keep the associative structure, and I am braindead from a long long week of work.

In the end, I am looking to have a table structure similar to:

FileID, FolderID, ParentFolderID, FileName

How can I do this?

Answer 1

try something like this:

function dirToDb($res, $parentId = 0)
{
    foreach ($res as $key => $value) {
        if (is_array($value))  {
            $db->exec("insert into table (path, parentId) VALUES (?, ?)", [$key, $parentId]);
            dirToDb($value, $db->fetch("SELECT LAST_INSERT_ID()"));
        } else {
            $db->exec("insert into table (path, parentId) VALUES (?, ?)", [$value, $parentId]);
        }
    }
}

$res = dirToArray($dir);

dirToDb($res);

Answer 2

I have modified your code a little. Now for every directory,

index 0 point to directory index

index 1 point to parent directory index

index 2,3,....n points to files

<?php

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$dir = '/home/kevinpirnie/www';

$GLOBALS['I'] = 0; // root folder given index 0

function dirToArray( $dir , $parent) {
    $result = array();
    $cdir = scandir($dir);
    foreach ($cdir as $key => $value) {
        if (!in_array($value, array(".", "..")))  {
            if (is_dir($dir . DIRECTORY_SEPARATOR . $value)){

                $result[$value] = [++$GLOBALS['I']]; // add folder index
                $result[$value][] = $parent; // add parent folder index

                $result[$value][] = dirToArray($dir . DIRECTORY_SEPARATOR . $value, $GLOBALS['I']);
            } else {
                $result[] = $value;
            }
        }
    }
    return $result;
}

$res = dirToArray($dir, $GLOBALS['I']);

echo '<hr />';
echo '<pre>';
print_r($res);
echo '</pre>';
echo '</pre>';

You can now insert the data into mysql tables directly using a similar recursive loop (if you do not want to use mysql auto generated id)

If you want to use auto generated mysql id, you should do insertion in two passes. In first pass insert the folder data and get the id from mysql insert id function. Then create an associative array map

$array_map[$current_folder_id] = mysqli_insert_id()

Then update this id in the second recursive pass