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

Adding scrolling to text C#

c# text scroll

So, I'm trying to make text vertically scroll in a form and I'm having trouble doing it. I would prefer to make it a label scrolling, but I highly doubt that's possible.

Java Play2- Preventing CSRF

java playframework-2.0 csrf

I saw https://github.com/orefalo/play2-authenticitytoken but this repository gives and 404 error and he closed the issue on his git

Java NIO read() End Of Stream

java nio socketchannel

I'm using Java NIO in to accept, read and write in my server.

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.