Single Query Dynamic Multi-level Menu

Since writing Dynamic Multi-level CSS Menu with PHP and MySQL. SEO Ready I have learned quite a lot and in doing so found a much more efficient way of building this menu. This method varies in that it only makes one query to the menu table and compiles the results into a multidimensional array. The basic recurring function was just about the same, just taking into account the changes in data structure. Lets start with the query and array.

01	// Select all entries from the menu table

02	$result=mysql_query("SELECT id, label, link, parent FROM menu ORDER BY parent, sort, label");

03	// Create a multidimensional array to conatin a list of items and parents

04	$menu = array(

05	'items' => array(),

06	'parents' => array()

07

);

08	// Builds the array lists with data from the menu table

09	while ($items = mysql_fetch_assoc($result))

10

{

11	// Creates entry into items array with current menu item id ie. $menu['items'][1]

12	$menu['items'][$items['id']] = $items;

13	// Creates entry into parents array. Parents array contains a list of all items with children

14	$menu['parents'][$items['parent']][] = $items['id'];

15

}

The $menu contains 2 other arrays, items holds every result from the menu table query, the parents array holds a list of all item ids that have children. Next we use a while statement to run through the sql results and assign items to the arrays. If the items parent id already exists in the parents array it will be overwritten so there will only be 1 of each parent id listed.

01	// Menu builder function, parentId 0 is the root

02	function buildMenu($parent, $menu)

03

{

04	$html = "";

05	if (isset($menu['parents'][$parent]))

06

{

07	$html .= "

08

\n";

09	foreach ($menu['parents'][$parent] as $itemId)

10

{

11	if(!isset($menu['parents'][$itemId]))

12

{

13	$html .= " \n .$menu['items'][$itemId]['link']."'>".$menu['items'][$itemId]['label']."\n \n";

14

}

15	if(isset($menu['parents'][$itemId]))

16

{

17	$html .= "

18	\n '".$menu['items'][$itemId]['link']."'>".$menu['items'][$itemId]['label']." \n";

19	$html .= buildMenu($itemId, $menu);

20	$html .= " \n";

21

}

22

}

23	$html .= " \n";

24

}

25	return $html;

26

}

27	echo buildMenu(0, $menu);

This version signifigantly reduces the strain on your server if you have hundreds or thousands of pages and still allows you to keep a completely dynamic menu.