Navigation menu from MySQL to arrays

Hi! I’m stuck with a problem here. My aim is to create a CMS for navigation menu, that’s why I use database table to save name and id of a category, as well as its predecessor (for nested unordered lists). The problem is that I don’t know what should be the best technique (also the table structure, for example, should I save paths etc) in order to get from something like this:

(Mysql DB table)
ID | Name | Predecessor

1 | Projects (Menu Level 1) | 0
2 | Project 1 (Menu Level 2) | 1

to this:

    [0] => Projects (Menu Level 1)
    [1] => Array
            [0] => Project 1 (Menu Level 2)
            [1] => Project 2 (Menu Level 2)
            [2] => Array
                    Some 1 (Menu Level 3)
                    Some 2 (Menu Level 3)

            [3] => Project 3 (Menu Level 2)
            [4] => Array
                    [0] => Some 3.1 (Menu Level 3)

    [2] => News (Menu Level 1)
    [3] => Contact (Menu Level 1)

which is the same as this:

$menu = array('Projects (Menu Level 1)',
          	array('Project 1 (Menu Level 2)',
                'Project 2 (Menu Level 2)',
					array('Some 1 (Menu Level 3)',
						  'Some 2 (Menu Level 3)'),
                'Project 3 (Menu Level 2)',
					array('Some 3.1 (Menu Level 3)')),
          	'News (Menu Level 1)',
          	'Contact (Menu Level 1)');

Any help appreciated.

There are two ways depending on how you want to go about it. The first is the most common but the second is usually faster unless you have abandoned records (ones without valid parent records).

The first is simply to a query for all items with parent = 0. I used parent simply because I’m lazy with the keyboard. Then, for each of those run subsequent queries to get the child records of these items, and so on. It’d be a recursive loop:

function getItemsByParent($ParentID){
    $Query = MySQL_Query('SELECT * FROM items WHERE parent = 0'); //assuming MySQL
    $Items = array();
    while($row = mysql_fetch_assoc($Query)){
        $row['Children'] = getItemsByParent($row['id']);
        $Items[] = $row;
    return $Items;
function getNestedItems(){
    return getItemsByParent(0);

That will get something similar but more extensive than what you asked for, so if you want to refine it go ahead :slight_smile:

The second way is essentially getting ALL rows from the table and then processing it with PHP, but from a ‘making sense of it’ point of view I prefer the first method.

jake, are you doing queries inside a loop? whoa :slight_smile:

a single query will do it, with self-joins for each level down the navigation hierarchy

see Categories and Subcategories

Rudy, the issue with that method is that you don’t know how far something will be down a list - it fixes the depth at which you can go. I’m wondering how it’d benchmark against a simpler query to just get all the rows in the table.

The looped query thing is less efficient - granted - but the question implies that the OP might not be completely comfortable with PHP so a structured approach helps the understanding.

My personal approach would be to collect the data and process it later, but for a system with basic needs doesn’t need such complication from the understanding-the-code side of things.

yeah, i know

but for a navigation menu?

srsly, past four or five levels deep and you’d need to rethink your user interface design

this is discussed in the article


In many cases its not up to the developer to decide but the user as is the case with just about every CMS.

keissfootball, are you just recording for each record the id of its parent record? Have you considered using either the “Adjacency List Model” or “Modified Preorder Tree Traversal” for recording the relationship between the records (for recording the tree structure)?

My own preference is to use “Modified Preorder Tree Traversal” due to only needing 1 query to get the tree. See this SitePoint Article for more info on the two methods.