[thelist] more sql help
Tom Dell'Aringa
pixelmech at yahoo.com
Thu Nov 21 12:23:01 CST 2002
Me again on this same project. Rudy was nice enough to help me with
my table structure, but I can't get this query right.
I have 3 tables (shown below). menuitem, category and subcategory.
Each menuitem has a category. I want menuitems to also be able to be
a subcategory of a category as well, so Rudy had me make the
subcategory table.
the 'subParentID' is equal to a categoryID - its a foreign key.
I have the following query that successfully lists out everything in
nice order WITHOUT the subcategories:
$result = @mysql_query("SELECT menuitem.*, category.* FROM menuitem,
category WHERE (menuitem.categoryID = category.categoryID) ORDER BY
category, item");
But I cannot seem to successfully add the subcategories...
Below are the tables:
-----------------------------------
CREATE TABLE category (
categoryID int(11) NOT NULL auto_increment,
category varchar(24) NOT NULL default '',
PRIMARY KEY (categoryID)
)
# --------------------------------------------------------
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),
FULLTEXT KEY item (item)
)
# --------------------------------------------------------
CREATE TABLE subcategory (
subcategoryID int(11) NOT NULL auto_increment,
subcategory varchar(25) NOT NULL default '',
subParentID int(11) default NULL,
PRIMARY KEY (subcategoryID)
)
---------
TIA
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