Convert hierarchical SQL data to HTML list in a single query...

Question

Has anyone been successful doing this w/o php, asp, etc...? I can get this far: http://www.pastey.net/113859/20 but can't figure out a simple way to make it an HTML list... any help would be GREATLY appreciated.

Answer 1

If you have access to create stored procedures, then you could use a cursor to loop through your data set and throw your output into a return variable.

Either that or you could get probably 90% there with a few CASE statements, but I don't know how you'd do the closing tags.

Answer 2

Hierarchical data is best dealt with using common table expressions (CTEs). I'm not sure if MySQL supports them yet, though.