1

I would like to store a nested associative array in MySQL. I need this for a nestable navigation menu on my site. There should be no limit to the level of nesting.

I have researched the nested set model and the adjacency list, but am unsure of which to use for my needs.

Ideally, I will be able to query my table in a way that I can reconstruct my associative array in PHP and then use this to construct my navigation menu. Records may be added / changed / re-ordered, but this will only happen infrequently.

I am looking for an example table structure for MySQL (InnoDB), queries to get and re-order the records, and update, delete and add new records. I am using PDO in PHP so any example code of how to turn the record set into the associative array would also be helpful.

Improve this question
8
  • 1
    Might help, I'm still using this solution: stackoverflow.com/questions/9224229/… Commented May 16, 2014 at 17:47
  • Unfortunately, I can not see how this would work with an id - parent_id setup. The recursive function that refactors the array to include child arrays requires that an item's parent must already be in the array before it can be added. There is no way to guarentee this with a select statement in SQL even with ORDER BY because the id of the item has no relation to it's order in the list Commented May 16, 2014 at 18:26
  • I'm using the above code with MySQL and it works perfectly. Here's my table: pastie.org/9182539 navigation_link_id is the "parent ID". I just use select * from tbl order by display_order. I think it's the same as Muthu's suggestion. Commented May 16, 2014 at 18:28
  • 1
    Order only matters per <ul> list and doesn't need to be unique. If there is a parentID assigned but it doesn't exist, that item would not be shown. I'm a little hungover to explain it right now, but like I said I've been using the code from that post for a long time now. I run a CMS where the user makes the navigation. Commented May 16, 2014 at 18:41
  • 2
    This should be a useful read: stackoverflow.com/questions/4048151/… Commented May 16, 2014 at 20:53

2 Answers 2

Reset to default
0

Try the following design:

  1. Column 'BEFORE'
  2. Column 'AFTER'
  3. Column 'NAME'
  4. Column 'ID'

Now, just store ID as required.

Example: The navigation menu might be

A --> B --> C

Just give an ID to each entry in the menu and store it with an unique ID. Then enter the BEFORE and AFTER values as IDs into the table.

So, for B we have

Entry for B:
BEFORE = ID(C) 
AFTER = ID(A) 
NAME = 'B'

This can be easily expanded for multiple nested menus.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I would suggest following design for implementing the solution.

Table : Navigation

Columns : 
ID (INT NOT NULL IDENTITY),
Description (VARCHAR),
ParentID (INT NULL) - Refer its own table ID column,
AdditionalColulmns..
Improve this answer

3 Comments

Okay I understand the whole foreign key referencing the id column in the same table (this is the adjacency list I believe), but how do I turn the results into the associative array? What should the SQL select look like?
can you please provide me some sample how you are expecting the array, so that I can try to give my query.
The array should be an array of arrays. The child arrays are associative arrays and contain keys like title, href etc. Each child array may have a key called children, which would be an array containing title, href and so forth. This array may also have children which is an array containing ... the list goes on. Does that make sense?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.