Databases
StackOverflow2013
Postsdbo
POPHP & Mysql tree navigation menu with unlimited sub categories
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP & Mysql tree navigation menu with unlimited sub categories
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBill the Lizard
    1. USSouth Coast Web
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPHP & Mysql tree navigation menu with unlimited sub categories
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPHP & Mysql tree navigation menu with unlimited sub categories
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think you will get your answer on this reply: http://stackoverflow.com/a/10926952. It worked for me.
      singulars
      1. POPHP & Mysql tree navigation menu with unlimited sub categories
      1. USSilvex
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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