[thelist] manipulating rows in the result set with PHP help

[rudy]

> http://www.pixelmech.com/test/results.html

nice job

i really like a guy who documents as he goes

coalesce picks the first non-null value going from left to right

you have

    coalesce(subcat.categoryName, cat.categoryID)
           = menuitem.categoryID

if there is a subcat for the cat, then subcat.categoryName will not be null

but then you are matching a category name to the menuitem's categoryID

i believe the first item in the coalesce list should be subcat.categoryID

fix your join and then try running it with the following SELECT list --

select concat( cat.categoryID
             , cat.categoryName ) as Category
     , cat.blurb
     , cat.parentID
     , concat( subcat.categoryID
             , subcat.categoryName ) as SubCategory
     , menuitem.categoryID
     , menuitem.item
     , menuitem.price

this will reveal the keys more easily so you can see what's joined with what

also, i think this "coalesce join" will not find menuitems attached to a
category if there are also subcategories attached to the same category, so
you might want to think about how to prohibit that (if that's what you'd
prefer) or how to print them (if you want to allow it)


rudy