title: PHP & Mysql tree navigation menu with unlimited sub categories
body: <p>Im looking for a way to extract the data from the mysql database to create an unlimited subcategry menu.
  The menu has about 1500 categories in it and they are stored into the database table (menu) in the following format :</p>
  
  <p>category_id , category title , parent_id</p>
  
  <p>i want to do 1 mysql query to put all the data into an array.</p>
  
  <pre><code>$menu_list =array();
  
  $query = mysql_query("SELECT * FROM menu ORDER BY category_id ASC");
  
  if(mysql_num_rows($query) != 0) {
          while($row = mysql_fetch_array($query)){
                $category_id = $row['category_id'];
                $title = $row['category_title'];
                $parent_id = $row[parent_id'];
                $menu_list[] = array($category_id,$title,$parent_id);
          }
  }
  </code></pre>
  
  <p>That is the code that i am currently using to turn the data into the array.</p>
  
  <p>I then need to loop through the array to build a menu up.</p>
  
  <p>I will then hide all subcategories and expand with jquery ( I can do this bit no problem )</p>
  
  <p>The problem i have is going through the array and displaying the data in the right order.
  The menu has unlimited subcategories so it will probably need to be a recursive function to retrieve the data.
  I have folllowed alot of the examples on here but then i get lost how to display the data..</p>
  
  <p>i now need to end up with :</p>
  
  <pre><code>main item
    sub cat
    sub cat
        sub cat 
            sub cat 
  main item
     sub cat
  main item
  main item
     sub cat
         sub cat
            sub cat
               sub cat
               sub cat
  
  etc...  
  </code></pre>
  
  <p>with each category in its own div 
  I will then need to be able to edit each category (ie title name and position if need be)
  then if i change where a sub category is based a quick page refresh will reload the menu in the new order..</p>
  
  <p>eventually i would like to make this into a drag and drop but that will be at a later date.</p>
  
  <p>I hope this has explained it enough..</p>
  
  <p>Thanks in advance..</p>
  
  <hr>
  
  <p>So the problem has come back to haunt me again...
  My method of ajax calling another mysql select was ok for the CMS section as it was only used from time to time.
  But now we have got to the front end.
  Each time the page is viewed the first 3 levels of the menu are pulled using numerous mysql requests.
  As the categories are getting bigger this is now resulting in 1600+ categories being pulled numerous times each, and even at 1000 visits a day will result in more than a 1000000 sql requests a day.
  So i think i will definately need to pull the categories into an array, and then recursively go through the array.</p>
  
  <p>I have looked at the solutions above and they seem to work on paper but not in practise.</p>
  
  <p>If anyone can attempt to give me another solution it would be appreciated..</p>
  
  <p>just to recap in my db i have : id , category_name , parent_id</p>
  
  <p>I am now using PDO with mysql and prepared statements for added security..</p>
  
  <p>Thanks in advance..</p>