Multi level menu with PHP

Question

I have a subject table like this:

id
title
parent_id
full_path

full_path is for finding parent as recursive. Like this:

+----+-----------+-----------+-----------+
| id | title     | full_path | parent_id |
+----+-----------+-----------+-----------+
| 40 | home      | 40        |         0 |
| 41 | myhome1   | 41        |         0 |
| 42 | ****      | 40-42     |        40 |
| 43 | *****     | 41-43     |        41 |
| 44 | ***       | 44        |         0 |
| 45 | ****      | 45        |         0 |
| 46 | *****     | 46        |         0 |
| 49 | ******    | 49        |         0 |
| 50 | **** **   | 40-42-50  |        42 |
| 51 | **** **   | 40-42-51  |        42 |
| 52 | **** **   | 40-42-52  |        42 |
| 53 | *******   | 40-53     |        40 |
| 54 | ****      | 40-54     |        40 |
| 55 | ***       | 41-55     |        41 |
| 56 | **** **** | 40-42-56  |        42 |
| 57 | *******   | 44-57     |        44 |
+----+-----------+-----------+-----------+

How i can get an recursive array like this:

array
(
    40 => array
    (
        42 => array
        (
            50,51,52,etc.
        ),
        53,
        54
    )
    41 => array
    (
        43,
        55,
    ),
    44 => array
    (
        57,
    ),
    etc...
)

Can I use full_path for create multilevel menu?

Answer 1

I edited Hugo's code:

MySQL's code: sqlfiddle

our table like this:

ID | Categories_name | Parent_id

our Data is(the numbers on picture, shows id categories):

our PHP codes:

<?php
    $db=mysql_connect("127.0.0.1","root","");
    $db_name = "test";
    mysql_select_db($db_name,$db);


    $query = "SELECT `id`,`cat_name`,`parent_id` FROM `categories`";
    $result=mysql_query($query);
    $num=mysql_num_rows($result);

    $level_each_rows = array();
    $rows = array();
    for($i = 0 ; $i < $num ; $i++)
    {
        $q_data = mysql_fetch_array($result);
        $rows[] = $q_data;
    }

    function getChildren_string($p)
    {
        global $rows;
        global $level_each_rows;
        $r = array();
        $i = 0;
        $return = '';
        foreach($rows as $row)
        {
            if ($row['parent_id'] == $p)
            {
                if($row['parent_id'] == 0)
                {
                    $level_each_rows[$row['id']]['i'] = 0;
                }
                else
                {
                    $level_each_rows[$row['id']]['i'] = $level_each_rows[$row['parent_id']]['i'] + 1;
                }
                $return = $return.'
                <tr>
                    <td>'.$row['parent_id'].'</td>
                    <td><div style="margin:0px '.($level_each_rows[$row['id']]['i'] * 35).'px;">['.$row['id'].'] - '.$row['cat_name'].'</div></td>
                </tr>

                ';
                $return = $return.getChildren_string($row['id']);
                $i++;
            }
        }
        //---
        return $return;
    }

    $childs = getChildren_string(0);

    echo '
    <div dir="ltr">
        <table dir="ltr" border="1">
            <tr>
                <td>Parent ID</td>
                <td>Child ID</td>
            </tr>
    ';
    echo $childs;
    echo '
        </table>
    </div>
    ';
?>

Result:

Answer 2

You could use the code below to do this. Keep in mind that this works because your subjects array will be very small and the recursion that happens will be minimal. Dont use this approach on large arrays.

<?php
$query = "SELECT id, parent_id FROM subjects";
//execute with your prefered method, eg mysqli

$rows = array();
while($row = $result->fetch_array(MYSQLI_ASSOC))
{
  $rows[] = $row;
}

function getChildren($p) {
  global $rows;
  $r = array();
  foreach($rows as $row) {
    if ($row['parent_id']==$p) {
      $r[$row['id']] = getChildren($row['id']);
    }
  }
  return $r;
}

$final = getChildren(0);
?>