Is there a way to get nested data out of MySQL without using recursion?

So let us say that I have a menu system with all the navigation items stored in a MySQL table like so:

Table: Menu
-------------------------------------------------------
| id | title      | url                   | parent_id |
-------------------------------------------------------
| 1  | Home       | /home                 | 0         |
| 2  | About      | /about                | 0         |
| 3  | History    | /about/history        | 2         |
| 4  | Location   | /about/location       | 2         |
| 5  | Staff      | /about/staff          | 2         |
| 6  | Articles   | /blog                 | 0         |
| 7  | Archive    | /blog/archive         | 6         |
| 8  | Tags       | /blog/tags            | 6         |
| 9  | Tag Name 1 | /blog/tags/tag-name-1 | 8         |
| 10 | Tag Name 2 | /blog/tags/tag-name-2 | 8         |
-------------------------------------------------------

As you can see this table is quite simple with the only complication being the self referencing column parent_id, which defines how the menu should be nested.

So this would produce the following menu:

- Home
- About
    - History
    - Location
    - Staff
- Articles
    - Archive
    - Tags
        - Tag Name 1
        - Tag Name 2

Is there a way to get this structure from the aforementioned table without making use of a recursive function in PHP (but it could be Python, Java or any other language) that queries the database with each iteration?

Ideally this could be handled with one MySQL query. Perhaps the table structure needs to be changed to accommodate this - if so how?

Answers


You could pull all of it out in one single pull, and then work with it recursively in PHP. That way you save some of the query time, but gain a little scripting time.

I would do something like this:

Get all data, ordered by parent id
Put row into $data[$parent_id][]

define function to build menu, takes one param which is id
get $data[$id] and work with that array, building the array.

while looping through the items, check if size of $data[current-item-id] > 0
if so, call above function with 0 as param

This way, you only query the database once, but use a little more of the servers ram.


Need Your Help

NodeJS with Socket.IO delay emitting data

javascript node.js socket.io

I am using the example from the Socket.IO homepage (http://socket.io/). It works and everything, but there is a huge delay between the time data is sent, and when that data is received on the other...

convert color image to grayscale

java image-processing bufferedimage

I want to convert a color image to grayscale.First I getting the data of color image and change this data but when I want to create a gary image from this data I have a error like this...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.