How to display a category hierarchy from a database?

MarkB423

Say my category table looks like this:

ID | CatName | ParentID

Then another table has all of the product-to-category associations, like this:

ID | ProductID | CatID

A third table has an ID field for each product, plus the product's information.

And to display a certain category, the query looks something like:

SELECT * FROM products AS p, associations AS a
WHERE p.ID = a.ProductID AND a.CatID = '$id'

Naturally, I wouldn't be selecting everything (*) from these two tables.

Now, how would I display at the top of the page the category hierarchy?

Such as "Category1 > SubCategory1 > CurrentCategory"

This is where I'm stumped at finding the most efficient solution...

Also, PART 2 of my question:

How can I output an entire category table of names into, say, a drop-down list?

For instance, inside this list would be printed:

Autos
Autos > Acura
Autos > BMW
Autos > Jeep
Autos > Jeep > Grand Cherokee
Autos > Jeep > Grand Cherokee > Laredo
Autos > Jeep > Grand Cherokee > Limited

sarahk

How longs a piece of string 🙂

If you look at all the directories, search engines etc you will see any number of methods used to display a hierarchy. Pick one you like, it's your design.

Hard to tell you how to code something to do with presentation until you've designed it though.

First steps then, find out what you like, then hard code an example. Look at the table structures, javascript etc needed to present it right. Then, and only then, look at the php which is needed to generate the html that you've hardcoded.

MarkB423

Okay, I was thinking... I have thought of two ways to do the list thing...

1) Grab all of the categories into an array with each key being the category ID, then having its value be an array of the name and parent category ID

Then it would be possible to loop through and figure out each "hierarchy."

2) This option seems more practical: add another field in the category table, which would be a comma-delimited list of the parent categories.

Then, I could explode() this list and simply loop through this array while using the values to reference the proper ID of the array of all of the categories in the table (like in the first example).

Option two seems a little simpler to me... as it would be easier to figure out the hierarchy (since it is done beforehand), AND I wouldn't have to select the entire table when I want to display single category hierarchy on a product page...

What do you think? 1 or 2...