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

how to know indexpath of UITableView.visibleCells?

ios objective-c uitableview

NSArray * arrayOfVisibleTVC=[BNUtilitiesQuick ListController].tableViewA.visibleCells;

Sorting on the server or on the client?

sql database performance sorting

I had a discussion with a colleague at work, it was about SQL queries and sorting. He has the opinion that you should let the server do any sorting before returning the rows to the client. I on the...

Is it any way possible to do text shadows with current IE9 beta CSS?

javascript html css html5 shadows

So text shadows in FF and Chrome are grate. For example I have simple web page for simple video streaming server.. no shadows in IE9 for me(