Hi All ...
I have a mysql database with the following fields
ID (Int) - Title (VarChar) - ParentID (Int)
This table is used to display categories with it's parent. Categories with the ParentID of 0 means that it is a root category.
What'm trying to do is query the database and populate all values (with it's parent & child(s)) within a regular html dropdown box.
So the output of the dropdown whould look like:
Home > Sub Cat 1
Home > Sub Cat 1 > Sub Sub Cat 1
Home > Sub Cat 1 > Sub Sub Cat 2
Home > Sub Cat 1 > Sub Sub Cat 3
Home > Sub Cat 2 > Sub Sub Cat 1
Home > Sub Cat 2 > Sub Sub Cat 2
Home > Sub Cat 3
Home > Sub Cat 4
Home > Sub Cat 4 > Sub Sub Cat 1
Home > Sub Cat 5
Any ideas on how I can accomplish this?
As far as I know you cannot elegantly handle this sort of datastructure in MySQL (this is based on 5min of googling). If you had Oracle you could use CONNECT BY PRIOR, and with SQL Server one can use Common Table Expressions to effect much the same thing (except in a pretty clunky fashion, IMO).
As far as I can tell, MySQL has neither of those options. You'll need to do some sort of horrific recursive proc, or something like that. Although I saw references to MySQL not even being able to do that (I didn't check how contemporary those comments were).
If you can possibly ditch that way of modelling your data - it really doesn't scale so well - have a look at nested set hierarchies:
The only modification I make to that is to store the parent with each node too, which makes sibling operations much easier. Nested sets work really well with hierarchical look-ups though.
You ask 2 implicit questions, one about the data and one about the display. My suggestion is on display.
To display the parent and child nodes, you might want to think about using a cftree. This link contains a fully worked out example (at the end) which you can run directly.
Take a look at Ben Nadel's article on Recusion, it was a huge help for me when i was doing something similar. After getting his code to work in your environment, I think all you would have to do is tweak the output and populate a pulldown...
Make sure to check out the comments, there is example code in there on how to pass the previous level's value to the next itiration.