Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>Something like this would do the trick:</p> <pre><code>function display_children($parent, $level) { $result = mysql_query("SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu` a LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=" . $parent); $indent = str_repeat('-', $level); $tpl = '&lt;option value="%s"&gt;%s&lt;/option&gt;'; while ($row = mysql_fetch_assoc($result)) { echo sprintf($tpl, $row['link'], $indent . $row['label']); if ($row['Count'] &gt; 0) { display_children($row['id'], $level + 1); } } } </code></pre> <p>I think you could also simplify your query slightly</p> <pre><code> $result = mysql_query("SELECT a.id, a.label, a.link, (SELECT COUNT(id) FROM `menu` WHERE parent = a.id) AS Count FROM `menu` a WHERE a.parent=" . $parent); </code></pre> <p><strong>Reworked example</strong></p> <p>The following would allow you to complete the whole process with 1 query. It uses PHP 5.3 due to closures.</p> <pre><code>// I assume this is how your data comes out of MySQL $items = array( array('id' =&gt; 1, 'label' =&gt; 'Item One', 'link' =&gt; 'Link One', 'parent' =&gt; 0), array('id' =&gt; 2, 'label' =&gt; 'Child One', 'link' =&gt; 'Link Two', 'parent' =&gt; 1), array('id' =&gt; 3, 'label' =&gt; 'Child Two', 'link' =&gt; 'Link Three', 'parent' =&gt; 1), array('id' =&gt; 4, 'label' =&gt; 'Item Two', 'link' =&gt; 'Link Four', 'parent' =&gt; 0), array('id' =&gt; 5, 'label' =&gt; 'Child One', 'link' =&gt; 'Link Five', 'parent' =&gt; 4), array('id' =&gt; 6, 'label' =&gt; 'Child Two', 'link' =&gt; 'Link Six', 'parent' =&gt; 4), array('id' =&gt; 7, 'label' =&gt; 'Child Three', 'link' =&gt; 'Link Six', 'parent' =&gt; 6), array('id' =&gt; 8, 'label' =&gt; 'Child Four', 'link' =&gt; 'Link Six', 'parent' =&gt; 6), ); // Loop using references to build a tree $childs = array(); foreach($items as &amp;$item) { $childs[$item['parent']][] = &amp;$item; } unset($item); foreach($items as &amp;$item) { if(isset($childs[$item['id']])) { $item['children'] = $childs[$item['id']]; } } // We now have a tree with 'children' key set on each node that has children $tree = $childs[0]; // Prepare a template and recursive closure (note reference on &amp;$print) $tpl = '&lt;option value="%s"&gt;%s %s&lt;/option&gt;'; $print = function($item, $indent = '') use (&amp;$print, $tpl) { echo sprintf($tpl, $item['link'], $indent, $item['label']) . "\n"; if(isset($item['children'])) { foreach($item['children'] as $child) { $print($child, $indent . '-'); } } }; echo '&lt;select onchange="showProduct(this.value);"&gt;'; // Call the function for each top-level node foreach($tree as $row) { $print($row); } echo '&lt;/select&gt;'; /* &lt;select onchange="showProduct(this.value);"&gt; &lt;option value="Link One"&gt; Item One&lt;/option&gt; &lt;option value="Link Two"&gt;- Child One&lt;/option&gt; &lt;option value="Link Three"&gt;- Child Two&lt;/option&gt; &lt;option value="Link Four"&gt; Item Two&lt;/option&gt; &lt;option value="Link Five"&gt;- Child One&lt;/option&gt; &lt;option value="Link Six"&gt;- Child Two&lt;/option&gt; &lt;option value="Link Six"&gt;-- Child Three&lt;/option&gt; &lt;option value="Link Six"&gt;-- Child Four&lt;/option&gt; &lt;/select&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