Recursive function with

Question

currently I am displaying data in Text format with recursive function like..

Food    
    Fruit
        Red
            Apple
        Yellow
            Banana
    Meat
        Beef
        Pork

and I am using following code to display this text

function display_children($parent, $level) { 
    $result = mysql_query('SELECT * FROM category '.
    'WHERE parant_id="'.$parent.'";'); 

    while ($row = mysql_fetch_array($result)) { 
        echo str_repeat('    ',$level).$row['name']."\n"; 
        display_children($row['id'], $level+1); 
    } 
} 

display_children(0,0);

But now I want to display the above text in <ul> <li> menu like

<ul>
    <li>Food
        <ul>
            <li>Fruit
                <ul>
                    <li>Red
                        <ul>
                            <li>Apple</li>
                        </ul>
                    </li>
                    <li>Yellow
                        <ul>
                            <li>Banana</li>
                        </ul>
                    </li>
                </ul>
            </li>
            <li>Meat
                <ul>
                    <li>Beef</li>
                    <li>Pork</li>
                </ul>
            </li>
        </ul>
    </li>
</ul>

All data is stored in table in following format

+----+-----------+--------+
| id | parant_id |  name  |
+----+-----------+--------+
|  1 |         0 | Food   |
|  2 |         1 | Fruit  |
|  3 |         1 | Meat   |
|  4 |         2 | Red    |
|  5 |         2 | Yellow |
|  6 |         4 | Apple  |
|  7 |         5 | Banana |
|  8 |         3 | Beef   |
|  9 |         3 | Pork   |
+----+-----------+--------+

please help me to display whole data in <ul> <li> format

Thanks

@hakre I have seen that answer can you please help me to do this work? — Sumit Bijvani, Jun 23 '13 at 10:49
Yes sure, I'm happy to help you to get this finished. But right now I've got weekend, if you wanna save some bucks, contract me weekdays. — hakre, Jun 23 '13 at 10:56

Zoltán Tamási · Accepted Answer · 2013-06-23T11:36:45.887

// pass a full row to the function instead of only the id
// also, with this solution you don't need the level, but I kept it there, you can use it for styling, etc.
function display_with_children($parentRow, $level) { 
    echo '<li>'.$parentRow['name']; 
    // if your id column is integer, you don't need the quotation mark
    $result = mysql_query('SELECT * FROM category WHERE parant_id='.$parentRow['id'].';');
    if (mysql_num_rows($result) != 0) {
        echo '<ul>';
        // use the fetch_assoc to get an associative array
        while ($row = mysql_fetch_assoc($result)) { 
            display_with_children($row, $level+1); 
        } 
        echo '</ul>';
    }
    echo '</li>';
}

And use it like

$rootRow = ....;
echo '<ul>';
display_with_children($rootRow, 0);
ehco '</ul>;

EDIT: Note that this solution is rather theoretical, because it has performance issues (see comments). If you don't have too many categories, it's better to fetch them all together from the database, and process them recursively from memory, which would be something similar, except for the way you retrieve the children of each category. If you have a huge amount of nested categories or tags, and you need to process usually a partial tree of them, you could consider using the nested set model.

OK, I didn't care about performance, but wanted to give a solution close to his try. IF I wrote it from scretch, and there are no more than ~100 categories, I would fetch them at once, and processed them from memory. — Zoltán Tamási, Jun 23 '13 at 10:37

robert.little · Answer 2 · 2013-06-23T13:49:02.777

I prefer to perform only one mysql query and then try to avoid recursion (because of memory usage). The ouput is an array in which all the items are ordered => Child element is always following its parent. Very useful is the children where is the list of all direct children. There is only one limitation: Maximal count of children of each parent is 99 because of $digits=2;. You can change it to $digits=3; and count raises to 999 ;)

DB RECORDS:

id  parent  name
1     0     Food
2     1     Fruit
3     2     Red
4     3     Apple
5     2     Yellow
6     5     Banana
7     1     Meat
8     7     Beef
9     7     Pork

CODE:

$query = "SELECT * FROM `menu` ORDER BY `parent`";
$result = mysqli_query($dbc, $query);
$data = array();
while ($row = mysqli_fetch_assoc($result)) {
    $data[$row['id']] = $row;
}


/******************* ARRAY LIST GENERATOR *******************/
$digits = 2;
$menu = array();
$added = 1;
while ($added) {
    $added = 0;
    foreach ($data as $k => &$v) {
        if (!isset($v['key'])) {
            if (!$v['parent']) {
                $key = sprintf("%0{$digits}d", ++$added);
                $menu[$key] = $v;
                $v['key'] = $key;
                $menu[$key]['parentKey'] = 0;
                $menu[$key]['level'] = 1;
            } else {
                if (isset($data[$v['parent']]['key']) && isset($menu[$data[$v['parent']]['key']])) {
                    if (isset($menu[$data[$v['parent']]['key']]['children'])) {
                        $key = $data[$v['parent']]['key'] . sprintf("%0{$digits}d", count($menu[$data[$v['parent']]['key']]['children']) + 1);
                        $menu[$data[$v['parent']]['key']]['children'][] = $key;
                    } else {
                        $key = $data[$v['parent']]['key'] . sprintf("%0{$digits}d", 1);
                        $menu[$data[$v['parent']]['key']]['children'][] = $key;
                    }
                    $menu[$key] = $v;
                    $menu[$key]['parentKey'] = $data[$v['parent']]['key'];
                    $menu[$key]['level'] = $menu[$menu[$key]['parentKey']]['level'] + 1;
                    $v['key'] = $key;
                    $added++;
                }
            }
        }
    }
}

ksort($menu, SORT_STRING);

/******************* UL LI GENERATOR *******************/
$li = array();
$ul = array();
$levelLL = 0;
echo "<ul>\n";
$ul[1] = 1;
foreach ($menu as $k => &$v) {
    if ($v['level'] < $levelLL) {
        for ($i = $levelLL; $i > $v['level'] - 1; $i--) {
            if (isset($li[$i])) {
                echo "</li>\n";
                unset($li[$i]);
            }
            if (isset($ul[$i]) && $i > $v['level']) {
                echo "</ul>\n";
                unset($ul[$i]);
            }
        }
    } elseif ($v['level'] == $levelLL) {
        echo "</li>\n";
    }
    echo "<li>\n";
    $li[$v['level']] = 1;
    echo $v['name'] . "\n";
    if (isset($v['children'])) {
        echo "<ul>\n";
        $ul[$v['level'] + 1] = 1;
    }
    $levelLL = $v['level'];
}
if (0 < $levelLL) {
    for ($i = $levelLL; $i > -1; $i--) {
        if (isset($li[$i])) {
            echo "</li>\n";
            unset($li[$i]);
        }
        if (isset($ul[$i]) && $i > 0) {
            echo "</ul>\n";
            unset($ul[$i]);
        }
    }
}

OUTPUT:

This might be an old answer, but this is exactly what I was looking for in my project :) thanks @robert.little ! — Beeelze, Jul 17 '15 at 13:17

Recursive function with

2 Answers2