I have a table in a MySQL database that consists of 3 columns, product_id, category_name, parent_id.
What I would like to do is populate a selectbox on a form (and also hopefully create a linkable breadcrumb navigation but that is a minor secondary task) that builds a list so the user can select a particular category to add a new part to the database.
product_id is just an incremental key value so that just increments as new fields are added to the database. But there are a group of top level categories with a parent_id of NULL. Those would be the first items in the list, with each of their child categories (if they have them) listed (preferably in alphabetical order at each level)
Thanks for any suggestions. I was originally planning on making multiple drop down select boxes that update on the fly as each category was chosen, but I think this may be a simpler method. They will be adding new categories, but I don’t expect it to ever go more than 3 levels deep (though if I build the form in this manner and the code iterates through the table to dynamically build the list I suppose they can make as many category levels as they want.
The only downside I see to this is the potential to have the list get quite long, but their product line is fairly set, they’ve been around for quite awhile and this current structure fits their needs perfectly well so I don’t expect that to become an issue.
Greg
Example:
DB Format
product_id category_name parent_id
01 Accessories (NULL)
02 Valves (NULL)
03 Lights (NULL)
04 Caps (NULL)
05 Grills 1
06 Trays 1
07 Hooks 1
08 Holder 1
09 Signs 1
10 Switches 1
11 Disc 2
12 Lever 2
13 Grommet 2
13 Incandescent 3
14 1.00 ID 11
15 1.25 ID 11
16 1.50 ID 11
17 1.75 ID 11
18 2.00 ID 11
19 2.25 ID 11
20 High 13
21 Low 13
23 1.00 OD 4
24 1.25 OD 4
25 1.50 OD 4
26 Fluorescent 3
Would build a list (that I would make into a select list on a form) like this:
Accessories
Accessories - Grills
Accessories - Holder
Accessories - Hooks
Accessories - Signs
Accessories - Switches
Accessories - Trays
Caps
Caps - 1.00 OD
Caps - 1.25 OD
Caps - 1.50 OD
Lights
Lights - Fluorescent
Lights - Incandescent
Valves
Valves - Disc
Valves - Disc - 1.00 ID
Valves - Disc - 1.25 ID
Valves - Disc - 1.50 ID
Valves - Disc - 1.75 ID
Valves - Disc - 2.00 ID
Valves - Disc - 2.25 ID
Valves - Grommet
Valves - Grommet - High
Valves - Grommet - Low
Valves - Lever
Thanks r937 that was some awesome (and very educational!) stuff. And it led me exactly where I was wanting to go, except it starts showing at 2 levels (Valves - Disc) instead of showing the parent categories in their own returned values as well. Any thoughts?
Here is a query I am running against my DB and getting back exactly as the tutorial you linked showed it should. I just need to step back on more level on my results:
SELECT root.category_name as root_category_name,
down1.category_name as down1_category_name,
down2.category_name as down2_category_name,
down3.category_name as down3_category_name
FROM tbl_category as root
LEFT OUTER JOIN tbl_category as down1 on down1.parent_id = root.product_id
LEFT OUTER JOIN tbl_category as down2 on down2.parent_id = down1.product_id
LEFT OUTER JOIN tbl_category as down3 on down3.parent_id = down2.product_id
WHERE root.parent_id IS NULL
ORDER BY root_category_name, down1_category_name, down2_category_name, down3_category_name;
Holy cow! I’m so wrapped up I wasn’t even paying attention. You wrote the Simply SQL book! I’ve grabbed my dead tree version off my bookshelf and have it lined up “next to read!” (also cued it up in my Kindle so I see it there since that’s where I do most of my reading, but I like having the physical copy smiling back from the shelves too!
Thanks for the help so far!
Basically the parent categories by themselves are not being returned, only with their child nodes. In my samples below Accessories, Caps, Lights, and Valves are not being returned by that query so my result set is lacking 4 rows that are needed for my application.
These are the results I am looking for:
Accessories
Accessories - Grills
Accessories - Holder
Accessories - Hooks
Accessories - Signs
Accessories - Switches
Accessories - Trays
Caps
Caps - 1.00 OD
Caps - 1.25 OD
Caps - 1.50 OD
Lights
Lights - Fluorescent
Lights - Incandescent
Valves
Valves - Disc
Valves - Disc - 1.00 ID
Valves - Disc - 1.25 ID
Valves - Disc - 1.50 ID
Valves - Disc - 1.75 ID
Valves - Disc - 2.00 ID
Valves - Disc - 2.25 ID
Valves - Grommet
Valves - Grommet - High
Valves - Grommet - Low
Valves - Lever
This is what is currently being returned:
Accessories - Grills
Accessories - Holder
Accessories - Hooks
Accessories - Signs
Accessories - Switches
Accessories - Trays
Caps - 1.00 OD
Caps - 1.25 OD
Caps - 1.50 OD
Lights - Fluorescent
Lights - Incandescent
Valves - Disc
Valves - Disc - 1.00 ID
Valves - Disc - 1.25 ID
Valves - Disc - 1.50 ID
Valves - Disc - 1.75 ID
Valves - Disc - 2.00 ID
Valves - Disc - 2.25 ID
Valves - Grommet
Valves - Grommet - High
Valves - Grommet - Low
Valves - Lever
Thanks to all your feedback I’ve gotten the exact results I needed. I had to do a little more PHP coding because when I had a result return that was 3 levels deep it wasn’t listing the secondary category without showing the third level.
Lights - Incandescent
Valves - Disc <-----------This line wasn’t being returned
Valves - Disc - 1.00 ID
A bit of PHP coding to test for changes between values in my while loop and that was resolved (actually my dataset is better designed for testing than I realized as I had it working, or so I thought, but there was one category that was between 2 others with 3rd level items and I realized it slipped through my code so I had to rethink my test values in PHP.
For example:
Valves - Disc
Valves - Disc - 1.00 ID
Valves - Disc - 1.25 ID
Valves - Disc - 1.50 ID
Valves - Disc - 1.75 ID
Valves - Disc - 2.00 ID
Valves - Disc - 2.25 ID
Valves - Grommet <---- In this case it was skipped because there were no 3rd level values for this item
Valves - Lever
Valves - Lever - High
Valves - Lever - Low
Hopefully this helps someone else. The Sitepoint forums as always are the best source for answers!
Greg
that’s the beauty of the hierarchical query with multiple left outer joins – you get the tree you want, but if you want “intermediate” results printed, e.g. a separate line for a node whether or not it has children, then you do that in your application language while looping over the results