[thelist] help with SQL and PHP

Tom Dell'Aringa pixelmech at yahoo.com
Tue Dec 10 11:13:01 CST 2002


Still working on my menu thing and still having trouble :(. I about
have the SQL I need to get the information form the DB, but I need to
get one more piece of info out, and I'm having a hard time figuring
out the PHP to display it.

I have a simple database with 2 tables, category and menuitem (see
the bottom for the tables). A category can have a parent category,
making it a subcategory - i.e., 'side order' could be a subcategory
of 'lunch'.

Here is my SQL:
-------------------------------------
SELECT
	menuitem.menuitemID,
	menuitem.description,
	menuitem.price,
	menuitem.item,
	cats.categoryName,
	cats.blurb,
	cats.pretext,
	parentCats.categoryName,
	parentCats.blurb,
	parentCats.pretext
FROM
	menuitem
INNER
	JOIN category AS cats
		on menuitem.categoryID = cats.categoryID
LEFT OUTER
	JOIN category AS parentCats
		on cats.parentID = parentCats.categoryID
ORDER BY cats.categoryName
-----------------------------------

Rudy of course helped me nail this down. The only problem is, if I
don't have a menu item associated with a category, I don't get that
category. This is a problem because say I have 'specials' as a
subcategory of 'dinner', and I have 3 specials but NO actual dinner
items - dinner won't be displayed, then how can I display specials.
This can happen on menus where there is a main category, with no real
menu items, then a few subcategories where the real menu items are.

So Question #1 - how can I pull all the categories in my SQL above as
well as what I am already getting.

Question #2

I need to display this info like this:

(category) Lunch
  (menuitem) burger

(category) Dinner
  (subcategory) Specials
    (menuitem) soup

and so on.

My current code loops through the result set, using a flag variable
to grab each new category like so:

$lastcat = "";
while($row = mysql_fetch_array($result))
{
    $category = $row["categoryName"];
    $cid      = $row["categoryID"];
    $blurb =    $row["blurb"];

    $item =     $row["item"];
    $price =    $row["price"];
    $desc =     $row["description"];
    $id =       $row["menuitemID"];
    $parent =   $row["parentCats"];

    if($category != $lastcat)
    {
      print "$category";
      $lastcat = $category;
    }
    print "print out each line here";
}

----------

This gives me a list of all the items, under the correct category,
but NOT my subcategories at all. I can't figure out how to deal with
the parent category (parentID) in the php at all...

Sorry for the length of this but I am really stuck in the mud!

Here are the tables:

# Table structure for table `category`

CREATE TABLE category (
  categoryID int(11) NOT NULL auto_increment,
  categoryName varchar(24) NOT NULL default '',
  blurb varchar(25) default NULL,
  pretext varchar(255) default NULL,
  parentID int(11) default NULL,
  PRIMARY KEY  (categoryID)
)


# Table structure for table `menuitem`

CREATE TABLE menuitem (
  menuitemID int(11) NOT NULL auto_increment,
  item varchar(225) NOT NULL default '',
  description text,
  price decimal(7,2) NOT NULL default '0.00',
  categoryID int(11) NOT NULL default '0',
  PRIMARY KEY  (menuitemID),
)

Any help is appreciated!

Tom


=====
var me = tom.pixelmech.webDeveloper();

http://www.pixelmech.com/
http://www.maccaws.com/
[Making A Commercial Case for Adopting Web Standards]

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com



More information about the thelist mailing list