title: Listing Categories then subcategories in PHP from MySQL database
body: <p>I've been working on a project to build a simple ecommerce system that's been going fine until I started working on displaying the navigation for categories.</p>
  
  <p>When I build the categories in MySQL I have something like:</p>
  
  <pre><code>id             name             parent_id
  1              books            null
  2              art              1
  3              biography        1
  4              games            null
  5              electronics      null
  6              FPS              4
  </code></pre>
  
  <p>I've written the following SQL to retrieve the parent categories then the child categories:</p>
  
  <pre><code>SELECT parent.name AS parent_name, child1.name AS child1_name
  FROM categories AS parent
  LEFT OUTER JOIN categories AS child1 ON child1.parent_id = parent.id
  WHERE parent.parent_id IS NULL
  ORDER BY parent_name, child1_name
  </code></pre>
  
  <p>The result of this is an array containing multiple arrays :</p>
  
  <pre><code>Array
  (
      [0] =&gt; Array
      (
          [parent_name] =&gt; Books
          [child1_name] =&gt; Art
      )
  
      [1] =&gt; Array
      (
          [parent_name] =&gt; Books
          [child1_name] =&gt; Biography
      )
  
      [2] =&gt; Array
      (
          [parent_name] =&gt; Clothes, Shoes &amp;amp; Watches
          [child1_name] =&gt; 
      )
  
      [3] =&gt; Array
      (
          [parent_name] =&gt; Computers &amp;amp; Office
          [child1_name] =&gt; 
      )
  
      [4] =&gt; Array
      (
          [parent_name] =&gt; Electronics
          [child1_name] =&gt; 
      )
  )
  </code></pre>
  
  <p>I'm having trouble displaying this information as an unordered list. The desired effect would be something like this:</p>
  
  <pre><code>&lt;ul&gt;
      &lt;li&gt;Books
          &lt;ul&gt;
              &lt;li&gt;Art&lt;/li&gt;
              &lt;li&gt;Biography&lt;/li&gt;
          &lt;/ul&gt;
      &lt;/li&gt;
      &lt;li&gt;Computers&lt;/li&gt;
      &lt;li&gt;Electronics&lt;/li&gt;
  &lt;/ul&gt;
  </code></pre>
  
  <p>Does anybody have a solution for this problem? Do I need to change my MySQL code or even the database structure?</p>
  
  <p>Thanks in advance.</p>