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:
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.