title: Get Categories (multiple levels)
body: <p>I am making a script which will load all categories from a database. I just don't know how I can do this in a way which is shorter.
  I am trying to figure out a way in which I can theoretically load infinite levels of categories.</p>
  
  <pre><code>$stmt1 = $db-&gt;query("SELECT * FROM Rubriek WHERE Hoofdrubriek IS NULL");
  while($row1 = $db-&gt;fetch($stmt1))
  {
      $stmt2 = $db-&gt;query("SELECT * FROM Rubriek WHERE Hoofdrubriek = '" . $row1['Rubrieknummer'] . "'");
      while($row2 = $db-&gt;fetch($stmt2))
      {
  
  
          $stmt3 = $db-&gt;query("SELECT * FROM Rubriek WHERE Hoofdrubriek = '" . $row2['Rubrieknummer'] . "'");
          while($row3 = $db-&gt;fetch($stmt3))
          {
  
  
              $stmt4 = $db-&gt;query("SELECT * FROM Rubriek WHERE Hoofdrubriek = '" . $row3['Rubrieknummer'] . "'");
              while($row4 = $db-&gt;fetch($stmt4))
              {
                  $row3['CATEGORY3'][] = array(
                      'CATEGORY4_NAME'    =&gt; ucfirst(strtolower($row4['Rubrieknaam'])),
                  );
              }
  
              $row2['CATEGORY3'][] = array(
                  'CATEGORY3_NAME'    =&gt; ucfirst(strtolower($row3['Rubrieknaam'])),
                  'CATEGORY4'         =&gt; $row2['CATEGORY4'],
              );
          }
  
          $row1['CATEGORY2'][] = array(
              'CATEGORY2_NAME'    =&gt; ucfirst(strtolower($row2['Rubrieknaam'])),
              'CATEGORY3'         =&gt; $row2['CATEGORY3'],
          );
      }
  
      $catagories[] = array(
          'CATEGORY_NAME' =&gt; ucfirst(strtolower($row1['Rubrieknaam'])),
          'CATEGORY2'     =&gt; $row1['CATEGORY2'],      
      );      
  }
  </code></pre>
  
  <p>I would like to have some ideas on how to make this happen.
  (I am using a little template system that processes the array that is being made)</p>
  
  <p>EDIT:</p>
  
  <p>Thanks to Ivijan Stefan Stipić I was able to solve this.
  This is what I ended up doing</p>
  
  <pre><code>$categories = build_category(0);
  </code></pre>
  
  <p>And the function:</p>
  
  <pre><code>function build_category($parent, $row = NULL)
  {
      global $db;
  
      // Initialise array
      $data = array();
  
      // Basic SQL statement
      $sql = "SELECT * FROM Rubriek";
  
      // Where condition based on $row
      if(is_null($row))
      {
          $where = " WHERE Hoofdrubriek IS NULL";
      }
      else 
      {
          $where = " WHERE Hoofdrubriek = '" . $row['Rubrieknummer'] . "'";
      }
  
      // Execute query
      $stmt = $db-&gt;query($sql . $where);
  
      // Next level parent
      $next = $parent + 1;
  
      // Fetch results
      while($row = $db-&gt;fetch($stmt))
      {
          $data[] = array(
              'CATEGORY' . $parent . '_NAME'  =&gt; ucfirst(strtolower($row['Rubrieknaam'])),
              'CATEGORY' . $next              =&gt; build_category($next, $row),
          );
      }
  
      // Return data
      return $data;
  }
  </code></pre>