Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>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 <code>children</code> where is the list of all direct children. There is only one limitation: Maximal count of children of each parent is 99 because of <code>$digits=2;</code>. You can change it to <code>$digits=3;</code> and count raises to 999 ;)</p> <p><strong>DB RECORDS:</strong></p> <pre><code>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></pre> <p><strong>CODE:</strong></p> <pre><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 =&gt; &amp;$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']) &amp;&amp; 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 "&lt;ul&gt;\n"; $ul[1] = 1; foreach ($menu as $k =&gt; &amp;$v) { if ($v['level'] &lt; $levelLL) { for ($i = $levelLL; $i &gt; $v['level'] - 1; $i--) { if (isset($li[$i])) { echo "&lt;/li&gt;\n"; unset($li[$i]); } if (isset($ul[$i]) &amp;&amp; $i &gt; $v['level']) { echo "&lt;/ul&gt;\n"; unset($ul[$i]); } } } elseif ($v['level'] == $levelLL) { echo "&lt;/li&gt;\n"; } echo "&lt;li&gt;\n"; $li[$v['level']] = 1; echo $v['name'] . "\n"; if (isset($v['children'])) { echo "&lt;ul&gt;\n"; $ul[$v['level'] + 1] = 1; } $levelLL = $v['level']; } if (0 &lt; $levelLL) { for ($i = $levelLL; $i &gt; -1; $i--) { if (isset($li[$i])) { echo "&lt;/li&gt;\n"; unset($li[$i]); } if (isset($ul[$i]) &amp;&amp; $i &gt; 0) { echo "&lt;/ul&gt;\n"; unset($ul[$i]); } } } </code></pre> <p><strong>OUTPUT:</strong></p> <pre><code>&lt;ul&gt; &lt;li&gt; Food &lt;ul&gt; &lt;li&gt; Fruit &lt;ul&gt; &lt;li&gt; Red &lt;ul&gt; &lt;li&gt; Apple &lt;/li&gt; &lt;/ul&gt; &lt;/li&gt; &lt;li&gt; Yellow &lt;ul&gt; &lt;li&gt; Banana &lt;/li&gt; &lt;/ul&gt; &lt;/li&gt; &lt;/ul&gt; &lt;/li&gt; &lt;li&gt; Meat &lt;ul&gt; &lt;li&gt; Beef &lt;/li&gt; &lt;li&gt; Pork &lt;/li&gt; &lt;/ul&gt; &lt;/li&gt; &lt;/ul&gt; &lt;/li&gt; &lt;/ul&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload