[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