Help: multi level menu from product list

[elrayyes]

I have quite an issue in creating a multi level menu in php by extracting the category details for the menu from the product database.

My table was created as follows:

CREATE TABLE products (
id int(11) unsigned NULL auto_increment,
pid varchar(100) NULL default'',
description text NULL default '',
category1 varchar(100) NULL default'',
category2 varchar(100) NULL default'',
category3 varchar(100) NULL default'',
category4 varchar(100) NULL default'',
familyID varchar(15) NULL default'',
PRIMARY KEY (id),
KEY pid(pid),
KEY description(description),
KEY category1(category1),
KEY category2(category2),
KEY category3(category3),
KEY category4(category4),
KEY familyID(familyID),
)TYPE=MyISAM;

The idea is to create the hierachical menu using the categories (1 through 4) from the product table. Category1 being the parent, category2 being child, category 3 being grand-child, and category 4 being great-grand-child.

There is also one more issue that makes it too complex. In some cases two children for two distinct parents would have the same value (name). Below is a prime example of the sort of situation:

Animals
- House

• Cat
• Dog
• Hamster

- Farm

• Horse
• Cow
• Dog

Birds
- Air
- Land

As you can see, 'dog' is listed in two distinct sub-categories (being house and farm).

Any help is very much appreciated, and thanks in advance.

[Kraai]

I do not know much about coding, and someone more qualified may still resolve your issue, but I did find this post in Daniweb that may be of some help to you.

http://www.daniweb.com/forums/post838581.html#post838581

Good luck

[elrayyes]

I do not know much about coding, and someone more qualified may still resolve your issue, but I did find this post in Daniweb that may be of some help to you.

http://www.daniweb.com/forums/post838581.html#post838581

Good luck

Thanks Kraai for the reply. I did look at that example prior to posting my question. The example shown required to create a number of additional tables (which is what we have currently running on the website. But that doesn't solve the condition that I outlined in my example (at the bottom of my post). When you are querying any level, it checks the table from top to botton, and once it finds a match it lists it; eventhough it is the wrong match.

It seems there is something that I am missing in my query. The code that I would require treats my example as follows:

1) Animals --> House --> Dog --> A list of breeds
2) Animals --> Farm --> Dog --> A different list of breeds

The current query would list the "A list of breeds" under (Animals --> Farm --> Dog) rather than listing "A different list of breeds".

Thanks again for your reply.

Any takers on this?

[elrayyes]

I managed to change the way the menu (navigation) should be generated. Instead of having to utilize the categories from the product list, I constructed a new table the would outline the categories and their parents (id, name, pid).

To generate the menu, I used a recursive function as follows:

<?php
include('inc.php');
db_connect();

function get_categories($parent = 'NULL') {
	$html = '<ul>';
	$query = mysql_query("SELECT * FROM `categories` WHERE `pid` = '$parent'");
	while($row = mysql_fetch_assoc($query)) {
		$current_id = $row['id'];
		$html .= '<li><a href="#">' . $row['name'] . '</a>';
		$has_sub = NULL;
		$has_sub = mysql_num_rows(mysql_query("SELECT COUNT(`pid`) FROM `categories` WHERE `pid` = '$current_id'"));
		if($has_sub > 0) {
			$html .= get_categories($current_id);
			$html .= '</li>';
		} elseif ($has_sub == 0) {
			$html .= '</li>';
		} else;
	}
	$html .= '</ul>';
	return $html;
}
print get_categories();
?>

This seemed to generate the desired results except for one minor glitch. The last branch would generate an additional (empty) unordered list. This is an example of what happens:

<ul>
  <li><a href="#">Item 1</a>
  <ul>
    <li><a href="#">Item 1.1</a><li>
    <li><a href="#">Item 1.2</a><li>
    <li><a href="#">Item 1.3</a>
    <ul></ul>
    </li>
  </ul>
  </li>
  <li><a href="#">Item 2</a></li>
</ul>

Anybody knows where I went wrong?

[diafol]

Do you really need a DB for this? Could you store the data in a multidimensional array?

Anyway, the 'parent' model works quite well for a DB:

id        (PK, tinyint[2])  
label     (varchar [16])   displayed text in the navbar
url       (varchar [255])  the url (only for last level items) 
rankorder (tinyint[2])     the order in which items of the same parent_id are listed
parent_id (tinyint[2])     the id of the parent (0 = top level item)

The display is then just simply a matter of a reiterative php loop.

There are more sophisticated ways of storing this info.

Research adjacency lists and nested lists. There are pros and cons of using each.

For a quick and easy formatting, consider the jQuery 'superfish' plugin.