CODEWITHSUNDEEP
phpmysqlrecursion
Kevin
Kevin

Reputation: 2688

Convert Recursive Directory Listing to MySQL Table

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?

Upvotes: 7

Views: 2257

Answers (5)

phpkode
phpkode

Reputation: 250

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

Upvotes: 2

David Hayes
David Hayes

Reputation: 196

A really useful way to structure your database is with nested sets. Joomla uses this for things like article categories. These can be infinitely nested under one another.

enter link description here

Upvotes: 0

Atul Jindal
Atul Jindal

Reputation: 976

Every row in your Mysql directory table has three columns -

Directory Id, Directory name, parent id

For each insert the parent id will specify the parent of current directory in a row. For the Root directory parent id is 0

Upvotes: 0

manian
manian

Reputation: 1438

I think you need to insert folder names too as records into the DB, otherwise you wont get parent ID. But, in your result the folder names are missing as record items. I modified your code with assumption that you don't need the result as array but the SQL statement only.

<?php
$qry = array();
$result = $conn->query("SELECT MAX(FileID) as lastid FROM YOUR-TABLE-NAME-HERE");
$row = $result->fetch_assoc();
$id = $row['lastid'];
function dirToSql($dir, $parent) {
  global $qry;
  global $id;
  $result = array();
  $cdir = scandir($dir);
  foreach($cdir as $key => $value) {
    if(!in_array($value, array('.', '..'))) {
        $id++;
        $qry[] = "('".$id."', '".$dir."', '".$parent."', '".$value."')";
        if(is_dir($dir . DIRECTORY_SEPERATOR . $value)) {
            dirToSql($dir . DIRECTORY_SEPERATOR . $value, $id);
        }
    }
  }
  return $qry;
}

$dir = '/home/kevinpirnie/www';
$sql = dirToSql($dir, 0);

//Here is your SQL Statement
echo $qry = "INSERT INTO YOUR-TABLE-NAME-HERE (`FileID`, `FolderID`, `ParentFolderID`, `FileName`) VALUES ".implode(',', $sql);

You can optimize the code & the query if needed

Upvotes: 0

Sebastian
Sebastian

Reputation: 496

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);

Upvotes: 4

Related Questions