Organizing mySQL data using recursive PHP

Question

I am creating a questionnaire for a client that requires the questions to be organized by 3 layers of levels. I've successfully created the U.I. however I've been trying for the last 3 hours to pull data from a database in such a way that everything loads in the right place. The database is organized like so by the client so i have no control over it:

id    description    parentId    about
1      Level 1        0           This is the top level or in my case tab1
2      Level 2        0           This is the next tab in the top level
3      Level 1a       1           This is the first category under tab1
4      Level 1b       1           This is the next category under tab1
5      Level 1a1      3           This is the content under the first category of tab1

So anything with a parentId of 0 is the top level and will contain anything of the second level with the parentId of 1 and so on. Confusing yes, I can barely make sense of this but this is how I've been told to do it.

What approach would be the best way to execute something like this? An example from another question I'm using as a reference is attached below (although not working)

foreach (mysql_query("SELECT * FROM pB_test ORDER BY id ASC") as $row) {
  $menuitem = array_merge(array(), $row);
  $menuLookup[$menuitem['id']] = $menuitem;
  if ($menuitem['parent'] == null) {
    $menuitem['path'] = "/" . $menuitem['name'];
    $menu[] = $menuitem[];
  } else {
    $parent = $menuLookup[$menuitem['parent']];
    $menuitem['path'] = $parent['path'] . "/" . $menuitem['name'];
    $parent['menu'][] = $menuitem;
  }
}

Any help would be greatly appreciated. Cheers

Answer 1

It might be worth doing this in PHP, as opposed to SQL if you're working with an external database. I haven't benchmarked the following, so try with your data and see if performance is problematic or not.

You can choose yourself what to do with orphaned records (which reference parentIDs that don't exist anymore).

Ordering in PHP like this requires that you have all of your data beforehand, so use something like PDO's fetchAll(PDO::FETCH_ASSOC) method, which should result in something like this:

$data_from_database = array(
    array("id" => 1, "parentId" => 0, "description" => "Level 1"),
    array("id" => 2, "parentId" => 1, "description" => "Level 1a"),
    array("id" => 3, "parentId" => 1, "description" => "Level 1b"),
    array("id" => 4, "parentId" => 0, "description" => "Level 2"),
    array("id" => 5, "parentId" => 2, "description" => "Level 1a1"),
    array("id" => 6, "parentId" => 5, "description" => "Level 1a11a"),
    array("id" => 7, "parentId" => 5, "description" => "Level 1a11b"),
    array("id" => 8, "parentId" => 9, "description" => "Level 3"),
);

First off, you'll want to have the primary key (ID) as the array's keys. The following also adds the keys "children" and "is_orphan" to every record.

$data_by_id = array();
foreach($data_from_database as $row)
    $data_by_id[$row["id"]] = $row + array(
        "children" => array(), 
        "is_orphan" => false
    );

This will look something like this:

$data_from_database = array(
    1 => array("id" => 1, "parentId" => 0, "description" => "Level 1", 
               "children" => array(), "is_orphan" => false),
    ...
 );

Now, it gets tricky: we'll loop through the array and add references.

foreach($data_by_id as &$row)
{
    if($row["parentId"] > 0)
    {
        if(isset($data_by_id[$row["parentId"]]))
            $data_by_id[$row["parentId"]]["children"][] = &$row;
        else
            $row["is_orphan"] = true;
    }
}
unset($row); // Clear reference (important).

The last step is to clean up the 'root' of the array. It'll contain references to duplicate rows.

foreach($data_by_id as $id => $row)
{
    // If you use this option, you'll remove
    // orphaned records.
    #if($row["parentId"] > 0)
    #    unset($data_by_id[$id]);

    // Use this to keep orphans:
    if($row["parentId"] > 0 AND !$row["is_orphan"])
        unset($data_by_id[$id]);
}

Use print_r($data_by_id) after every step to see what happens.

If this proves to be a time consuming operation, try to build up the tree by only doing SELECT id, parentId FROM ... and then later fetching the metadata such as description. You could also store the result in Memcache or serialized into a database.

Answer 2

i also had the same kind of problem but after lot of googling and stackoverflowing :-) i found my answer.... Here is my way of coding.

function showComments($parent = 0)
{
$commentQuery = "SELECT * FROM comment WHERE parent = ".mysql_real_escape_string($parentId);
$commentResult = mysql_query($commentQuery)

while ($row = mysql_fetch_array($commentResult))
{
echo '[Table containing comment data]';
showComments($row['commentID']);
}
}

showComments();

Answer 3

If you have exactly 3 levels, then you can try this:

http://sqlfiddle.com/#!2/70e96/16

(
  SELECT 1 AS lvl,
         top_level.description AS o1, top_level.id AS id1,
                          NULL AS o2,         NULL AS id2,
                          NULL AS o3,         NULL AS id3,
         top_level.*
  FROM   node AS top_level
  WHERE  top_level.parentId = 0
)UNION ALL(
  SELECT 2 AS lvl,
         top_level.description      AS o1, top_level.id      AS id1,
         category_level.description AS o2, category_level.id AS id2,
                               NULL AS o3,              NULL AS id3,
         category_level.*
  FROM       node AS top_level
  INNER JOIN node AS category_level ON category_level.parentId = top_level.id
  WHERE      top_level.parentId = 0
)UNION ALL(
  SELECT 3 AS lvl,
         top_level.description      AS o1, top_level.id      AS id1,
         category_level.description AS o2, category_level.id AS id2,
         last_level.description     AS o3, last_level.id     AS id3,
         last_level.*
  FROM       node AS top_level
  INNER JOIN node AS category_level ON category_level.parentId = top_level.id
  INNER JOIN node AS last_level ON last_level.parentId = category_level.id
  WHERE      top_level.parentId = 0
)
ORDER BY o1,o2,o3;

I added a lvl field to the selects, different value for each level. Also added o1,o2,o3 for ordering nested levels nicely, of course you may have another needs. You could process all rows in PHP, for example split them into 3 arrays (one for each level), or maybe create a lookup table by id, etc.