[thelist] help with SQL and PHP

[rudy]

> - A menuitem MUST have a category

set its foreign key NOT NULL, and fix existing anomalies before proceeding


> - A category MIGHT NOT have a menuitem attached
> (it may have only subcategories)

ok-diddley-dokey

we will drive your reporting requirements from the top level categories
(which are the ones that don't have a parent category)

        from category as cat
            where cat.parent_id is null

subcategories and their menuitems are, if i understand you correctly, the
most frequent situation

so the columns you want are category name, subcategory name, and menuitem
name (and perhaps other columns, from their respective rows)

     select cat.catname, subcat.catname, menuitem.name
        from category as cat
           left outer join category as subcat
                         on subcat.parent_id = cat.id
              left outer join menuitem
                         on menu_cat = subcat.id

left outer joins ensure you get categories with or without subcategories,
and subcategories with or without menuitems

not finished yet!!

you also need menuitems attached directly to categories

there are other ways to do this, but i recommend keeping the same result set
layout as the above query

if it were any other database, i'd say UNION, but with mysql you'll have to
do two queries and merge the results yourself, dude -- my condolences, but
that's what your data model (menuitems tied to both categories and
subcategories) requires

anyhow, your second query is

     select cat.catname, null, menuitem.name
        from category as cat
            left outer join menuitem
                  on menu_cat = cat.id

notice how the subcat column is null

now if you could do this with UNION, you'd just conclude with

    order by cat.catname, subcat.catname, menuitem.name

and you're all done, subject to printing logic to detect category and
subcategory control breaks

but seeing as it's mysql, you'll have to choose one of several unattractive
options --

  - run each of the two queries into a temp table, select from the temp
table with order by, drop the temp table

  - concatenate each of the two queries into a php array, knock yourself out
sorting and printing

  - prohibit menuitems tied to categories: instead, tie them to
subcategories, and "fake" a category if necessary for those "sub"categories
that have menuitems

this last strategy is quite attractive and solves a lot of problems

holler if any of this does not make sense


rudy