How to generate a PHP array from MySQL database record for multi-level folder structure

Question

I am wanting to build an Evernote style note app with PHP and MySQL.

Evernote allows top level folders with note records in them.

I would like to make my app allow folders to be inside of folders so a top level folder can have a child folder which then has note records inside of the child folder.

Like this image below:

I have this PHP function which will build the structure menu shown in the image above...

This code build the menu shown

<?php
function tree($array, $parent, $parts = array(), $step = 0) {

    if (!count($array)) {
        return '';
    }

    $tid = ($step == 0) ? 'id="tree"' : '';
    $t = '<ul class="unstyled" '.$tid.'>';

    foreach ($array as $key => $item) {
        if (is_array($item)) {
            $open = $step !== false && (isset($parts[$step]) && $key == $parts[$step]);

            $t .= '<li class="directory'. ($open ? ' open' : '') .'">';
                $t .= '<a href="#" data-role="directory"><i class="glyphicon glyphicon-folder-'. ($open ? 'open' : 'close') .'"></i>&nbsp; ' . $key . '</a>';
                $t .= tree($item, "$parent/$key", $parts, $open ? $step + 1 : false);
            $t .=  '</li>';
        } else {
            $selected = (isset($parts[$step]) && $item == $parts[$step]);
            $t .= '<li class="file'. ($selected ? ' active' : '') .'"><a href="'. $parent .'/'. $item . '">'.$item.'</a></li>';
        }
    }

    $t .= '</ul>';

    return $t;
}
?>

The code above build the tree menu by calling _getTree()

protected function _getTree($dir = LIBRARY)
{
    $return = array('directories' => array(), 'files' => array());

    $items = scandir($dir);
    foreach ($items as $item) {
        if(preg_match($this->_ignore, $item)) {
            if($this->_force_unignore === false || !preg_match($this->_force_unignore, $item)) {
                continue;
            }
        }

        $path = $dir . DIRECTORY_SEPARATOR . $item;
        if (is_dir($path)) {
            $return['directories'][$item] = $this->_getTree($path);
            continue;
        }

        $return['files'][$item] = $item;
    }

    uksort($return['directories'], "strnatcasecmp");
    uksort($return['files'], "strnatcasecmp");

    return $return['directories'] + $return['files'];
}

_getTree() above builds an array in the format shown below by scanning directories on server looking for folders and files. It then passes that array into tree(). My goal is to make a function build this same style array but from MySQL Database folder and note records.

I need to write a function that could build array like below from two database tables.

MY database tables will be

notebooks with column name parent to denote the parent folder. THe notebook ID will go into parent field if the notebook is a child folder of a parent notebook. 0 == parent level folder.
notes with parent column to be linked to ID of notebook.

Code:

Array(
    [top_level_notebook_1] => Array(
            [child_note_of_parent_notebook_1.html] => some_note.html
            [child_level_notebook_1] => Array(
                    [note_1.html] => some_note.html
            )

            [child_level_notebook_2] => Array(
                    [note_2] => cache.js
                    [note_3] => cache.js.md
                    [note_4] => Confirmation-Dialog.js
            )

            [child_level_notebook_3] => Array(
                    [crud.php] => crud.php
                    [error2mysql.php] => error2mysql.php
                    [upload_file_from_remote_url.php] => upload_file_from_remote_url.php
            )

    )

    [top_level_notebook_2] => Array(
            [note_7.html] => some_note.html
    )

    [root_level_note.html] => Dev_Bookmarks.html
)

Hmm.. Is this asking for an opinion on how to structure your database or once you have done that how to query it? — Bob Nocraz
– Bob Nocraz, Commented Feb 11, 2016 at 22:11
@BobNocraz On how to query it. I updated question to show DB structure with parent columns — JasonDavis
– JasonDavis, Commented Feb 11, 2016 at 22:14

Nate I · Accepted Answer · 2016-02-12 02:01:55Z

1

Unless you want to get incredibly complex with your query, I'd do something more along the lines of getting all records and building an array with your script. Something like this:

Script now including the Database stuff (Updated):

//Change these credentials!
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$query = "SELECT nb.`name` AS 'notebook', no.`name` AS 'note', (SELECT `name` FROM `notebooks` WHERE `id` = nb.`parent`) AS 'nbparent', no.`parent` AS 'noparent' FROM `notebooks` nb RIGHT JOIN `notes` no ON no.`parent` = nb.`id` WHERE (nb.`active` = 1 OR nb.`active` IS NULL) AND (no.`active` = 1 OR no.`active` IS NULL)";

$result = $mysqli->query($query);

$myArray = array();

while($row = $result->fetch_assoc()){
    if($row['nbparent']){
        if($row['note']) $myArray[$row['nbparent']][$row['notebook']][] = $row['note'];
    } else {
        if($row['note']) {
            if($row['notebook']) $myArray[$row['notebook']][] = $row['note'];
                else $myArray[] = $row['note'];
        }

    }

}

var_dump($myArray);

This should work! I tested it on my local dev environment and got this result with a set of test data:

array (size=4)
  'Parent Notebook1' => 
    array (size=6)
      0 => string 'Note1' (length=5)
      1 => string 'Note2' (length=5)
      2 => string 'Note5' (length=5)
      'Child Notebook1' => 
        array (size=1)
          0 => string 'Note6' (length=5)
      'Child Notebook2' => 
        array (size=1)
          0 => string 'Note7' (length=5)
      'Child Notebook3' => 
        array (size=1)
          0 => string 'Note8' (length=5)
  'Parent Notebook2' => 
    array (size=1)
      0 => string 'Note3' (length=5)
  'Parent Notebook3' => 
    array (size=1)
      0 => string 'Note4' (length=5)
  0 => string 'Note With No Parent :(' (length=22)

What that's doing is looping through each result. Surely you'll need to change the column names, like in $row['notebook'], to what your actual column is named. It'll work one-level deep. If you need more, you'll have to tweak a bit. This is the general idea, though, and should get an array built from your structure.

Quick Note

The code above will not be strict-friendly. If you're concerned with warnings, the above code will give you warnings about undefined keys. It'll create them for you, as we've all grown to love/hate about PHP. If you want to keep the key warnings out, you can easily pop in some key checks with if(array_key_exists()).

Potential 'Gotcha'

This works on names. If you allow the same name for 'notebooks', I'd suggest taking it a step further and building the array by id rather than a text-based column like the title of your notebook. From there, you'll need to add a new key inside of each notebook with the name itself. I replicated the array you provided in your example when making this script, but I did want to be absolutely sure you understood the potential (frustrating) "gotcha" involved with this method.

Let me know if you have any questions, hope this helps.

edited Feb 12, 2016 at 2:01

answered Feb 11, 2016 at 22:53

Nate I

9574 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

JasonDavis Over a year ago

Thanks, my database exist only in my head at the moment so I will set up a test and report back. Thanks for the help!

Nate I Over a year ago

No problem, hope it helps! Let me know if this doesn't quite solve your needs and I'll edit my answer :)

JasonDavis Over a year ago

This image shows my test results... apollowebstudio.com/screenshots/2016/… and my sample DB pastebin.com/xChA7Btj

Nate I Over a year ago

Do you already have your script getting a dataset from your database using this query?

JasonDavis Over a year ago

Actually I don't really have anything yet except the test DB I just made. Changes can go anyway needed for best results at this point to get desired output of notebook folder that allow child notes and child notebooks

|

Collectives™ on Stack Overflow

How to generate a PHP array from MySQL database record for multi-level folder structure

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related