Build a multidimensional array using recursive function

Question

Problem:

I am trying to build a recursive tree using a function and data from a MySQL. However, the results are not as expected.

PHP code:

function buildTree($root, $next = array()) 
{
 // Sanitize input
 $root = (int) $root;
 // Do query
 $query = "SELECT CID, Item, Parent FROM betyg_category WHERE Status = '1' AND Parent = '{$root}'";
 $result = mysql_query($query) or die ('Database Error (' . mysql_errno() . ') ' . mysql_error());
 // Loop results
 while ($row = mysql_fetch_assoc($result)) 
 {
 $next[$row['CID']] = array (
 'CID' => $row['CID'], 
 'Item' => $row['Item'], 
 'Parent' => $row['Parent'], 
 'Children' => buildTree($row['CID'], $next)
 );
 }
 // Free mysql result resource
 mysql_free_result($result);
 // Return new array
 return $next;
}
$testTree = buildTree(0);
echo "<xmp>".print_r($testTree, true)."</xmp>";

The table in the database look like this:

enter image description here

I would like the array to be like this:

Array
(
 [1] => Array
 (
 [CID] => 1
 [Item] => Litteratur
 [Parent] => 0
 [Children] => Array
 (
 [2] => Integration av källorna
 [3] => Belysning av egna resultat
 [4] => Referenser
 )
 )
 and so forth..
)

That is to say, for each parent => produce children, then move on to next parent, etc. Thank you in advance for any advice.

• Lee: why don't you provide the results your getting so people can see what your currently producing from your code
• kexxcream: @Lee I would like to but the results just keep looping in all eternity and I have to press escape so the browser won't crash. In other words, the list is too long and I felt it is not adequate to include it.
• Lee: change buildTree($row['CID'], $next) to buildTree($row['CID']) ... now what happens ?
• Clockwork-Muse: How about adding a counter for how many rows have passed, and just return after that point, for debugging purposes. Also, you should really be using prepared statements. I know you're mostly safe from SQL Injection by casting the argument to an integer, but it's the principle of the thing. Also, I'm assuming that php allows you to use whatever for array indicies, or the top level of your array is likely to be too sparse (it's been too long).

Answer 1

You do not need recursion here. In fact, it will be very inefficent since you end up with a SELECT N+1 issue. Just order the result set by parent:

$query = "SELECT CID, Item, Parent FROM betyg_category WHERE Status = '1' ORDER BY Parent";
$result = mysql_query($query);
$tree = array();
while($row = mysql_fetch_assoc($result)) {
 if($row['Parent'] == 0) {
 $row['Children'] = array();
 $tree[$row['CID']] = $row;
 } else {
 $tree[$row['Parent']]['Children'][] = $row;
 }
}

This will produce the following:

Array
(
 [1] => Array
 (
 [CID] => 1
 [Item] => Litteratur
 [Parent] => 0
 [Children] => Array
 (
 [0] => Array
 (
 [CID] => 2
 [Item] => Integration av källorna
 [Parent] => 1
 )
 [1] => Array
 (
 [CID] => 3
 [Item] => Belysning
 [Parent] => 1
 )
 [2] => Array
 (
 [CID] => 4
 [Item] => Referenser
 [Parent] => 1
 )
 )
 )
 [5] => Array
 (
 [CID] => 5
 [Item] => Validitet
 [Parent] => 0
 [Children] => Array
 (
 [0] => Array
 (
 [CID] => 6
 [Item] => Huvudsyfte
 [Parent] => 5
 )
 )
 )
)

If you only want the name of each children, change, use $tree[$row['Parent']]['Children'][] = $row['Item']; instead.

• kexxcream: This works great, except I had to change it to: $tree[$row['Parent']]['Children'][$row['CID']] = $row['Item']; - but a problem remains. How do I sort the array index and more importantly, the index of Children?
• alexn: @kexxcream How do you want to sort it?
• kexxcream: I wanted it sorted by index key, but @sephoy08 provided an answer to that problem.

Answer 2

Try this one:

$array = array();
while ($row = mysql_fetch_assoc($result)) 
{
 if($row['parent'] == '0')
 {
 $array[$row['parent']] = '';
 $array[$row['parent']]['CID'] = $row['CID'];
 $array[$row['parent']]['Item'] = $row['item'];
 $array[$row['parent']]['Parent'] = $row['parent'];
 $array[$row['parent']]['Children'] = '';
 }
 else
 {
 $array[$row['parent']]['Children'][$row['CID']] = $row['item'];
 }
}
echo "<pre>";
print_r($array);

---

First in your query. Add ORDER BY CID ASC then

$count = array_keys($array);
foreach($count as $arr)
{
 ksort($array[$arr]['Children']);
}

• kexxcream: Thanks, thanks to @alexn code I managed to arrive at this solution as well. But now I am only missing a way to sort the $array and Children by their index.
• kexxcream: Great, this works well. I have posted the final solution below.

Answer 3

Final solution from all comments:

$query = "SELECT * FROM betyg_category WHERE Status = '1' ORDER BY CID ASC";
$result = mysql_query($query) or die ('Database Error (' . mysql_errno() . ') ' . mysql_error());
$tree = array();
while($row = mysql_fetch_assoc($result)) 
{
 if($row['Parent'] == 0) 
 {
 $row['Children'] = array();
 $tree[$row['CID']] = array(
 'CID' => $row['CID'], 
 'Item' => $row['Item'], 
 'Parent' => $row['Parent']
 );
 } 
 else 
 {
 $tree[$row['Parent']]['Children'][$row['CID']] = $row['Item'];
 }
}
$count = array_keys($tree);
foreach ($count as $array)
{
 ksort($tree[$array]['Children']);
}
echo "<xmp>".print_r($tree, true)."</xmp>";