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.

Ben Guest
  • 1,488
  • 2
  • 17
  • 29
  • 1
    Might help, I'm still using this solution: http://stackoverflow.com/questions/9224229/how-can-i-build-a-nested-html-list-with-an-infinite-depth-from-a-flat-array – Wesley Murch May 16 '14 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 – Ben Guest May 16 '14 at 18:26
  • I'm using the above code with MySQL and it works perfectly. Here's my table: http://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. – Wesley Murch May 16 '14 at 18:28
  • If you have a record like `id:7, title:'Hello', parent:12, order:1` how does this function work? The record's id is 7, it's parent's id is 12, and it's order in the sub-list is 1. What should I order this by to ensure I have already processed the parent item? Or should `order` be unique and therefore not reset for child lists? (When I say reset I mean that if we have 2 records at root with order 1 and 2 respectively, 2 has a child list, the order for the first item in the child list would be 3 not 1) – Ben Guest May 16 '14 at 18:33
  • 1
    Order only matters per `
      ` 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.
     – Wesley Murch May 16 '14 at 18:41
  • Okay, I think I understand. Haha okay no problem! Okay that's what I'm ultimately going for - a user editable navigation menu. If you have time at some point, could I ask how you are allowing the user to update / add / delete items from the lists? For example: do you just remove all the records from the table and re-add them when the user edits the list? I guess this would be a simple way to ensure the table correctly represents the list. – Ben Guest May 16 '14 at 20:01
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/53845/discussion-between-wesley-murch-and-ben-guest) – Wesley Murch May 16 '14 at 20:06
  • 2
    This should be a useful read: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – John May 16 '14 at 20:53

2 Answers2

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.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Kanishka Ganguly
  • 1,252
  • 4
  • 18
  • 38
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..
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
classic_vmk
  • 495
  • 2
  • 15
  • 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? – Ben Guest May 16 '14 at 18:08
  • can you please provide me some sample how you are expecting the array, so that I can try to give my query. – classic_vmk May 16 '14 at 18:47
  • 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? – Ben Guest May 16 '14 at 20:49