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?

In my opinion `full_path` is redundant data, so I would delete it from the db. — Voitcus, May 14 '13 at 14:17
Show us what you have tried and we can see where you are going wrong — Anigel, May 14 '13 at 14:20
@Voitcus without `full_path` how I can create a multi level menu? — Chalist, May 14 '13 at 14:24
To follow on from what @Voitcus has said, your parent_id would need to be the actual parent, not some other ancestor. Lets take `#50` as an example. Currently `#50`'s `full_path` is `40-42-50`, but in this case, `#42` should be `#50`'s parent, and `#40` should be `#42`'s parent. From `#50` you'd reach `#42` and from `#42` you'd reach `#40` - from there you'd stop the loop as `#40` has no parent. — James Donnelly, May 14 '13 at 14:27

score 3 · Accepted Answer · answered May 14 '13 at 14:33

3

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

answered May 14 '13 at 14:33

Hugo Delsing

13,803
5
45
72

Correct me if i'm wrong, but why not put the query inside the `getChildren` function : This query may be filtered with `WHERE parent_id=$p` so, not all rows of the table has to be tested. Does one query per node is slower than 'foreach on all rows' per node ? – Marcassin May 14 '13 at 15:00
I dont think it matters much with these numbers. If you do the second approach, make sure you have an index on parent_id. – Hugo Delsing May 14 '13 at 15:05

score 3 · Answer 2 · answered May 18 '14 at 22:50

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

enter image description here

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:

enter image description here

Multi level menu with PHP

2 Answers2

Linked