Page 1 of 1
query question
Posted: Sun Feb 06, 2005 1:55 am
by C_Calav
hi guys,
so im using "select * from planes where P_Category = $P_Category" to list the contents of planes in my DB.
can i have a plane in say, 3 category's?
so when the planes get displayed they get displayed in 3 categorys in stead of one?
if so,how can i do this?
thanx
Posted: Sun Feb 06, 2005 2:00 am
by feyd
Posted: Sun Feb 06, 2005 2:12 am
by C_Calav
thanx feyd, what bit in that pdf exacting am i ment to read?
does it mean i have to have another table inbetween my products and category tables to link them?
Posted: Sun Feb 06, 2005 2:20 am
by feyd
generally yes.. a table of just plane_id and category_id, which links to the categories table and planes table.
unless the rest of the fields inside the planes table is very low in number (like 3-4 or under) and space requirements (for those fields) are low, I would definitely suggest using a linking table setup.
Posted: Sun Feb 06, 2005 2:14 pm
by C_Calav
thanx feyd i see now..
do i only put the planes that i need in multipule categories in the new table with just plane_id and category_id or do i pull all my planes in there?
thanx
Posted: Sun Feb 06, 2005 2:19 pm
by feyd
all planes.
Posted: Tue Feb 08, 2005 8:31 pm
by C_Calav
hi,
here is my planes table (product)
i dont have a category table. so with what you sugested there would be alot of altering etc wouldnt there?
i have the category defined in my planes table as stated below.
Code: Select all
CREATE TABLE `planes` (
`itemId` int(11) NOT NULL auto_increment,
`P_Stock` varchar(20) NOT NULL default '',
`P_Name` varchar(150) NOT NULL default '',
`P_Cat` varchar(50) NOT NULL default '',
`P_Scale` varchar(10) NOT NULL default '',
`P_Length` varchar(10) NOT NULL default '',
`P_Span` varchar(10) NOT NULL default '',
`P_CostPrice` decimal(10,2) NOT NULL default '0.00',
`P_Price` decimal(10,0) NOT NULL default '0',
`P_Desc` text NOT NULL,
PRIMARY KEY (`itemId`),
UNIQUE KEY `id` (`itemId`),
FULLTEXT KEY `P_Name` (`P_Name`,`P_Desc`)
) TYPE=MyISAM AUTO_INCREMENT=254 ;
would making another table with the product id and category id still be the best way?
many thanx
Posted: Tue Feb 08, 2005 8:49 pm
by feyd
quite likely, yes. It should save a bunch of space.
Posted: Tue Feb 08, 2005 9:02 pm
by C_Calav
sorry feyd for going on about this so much!
i have done DB courses etc but just getting a bit muddled up with the scenerio i have at the moment.
i should have designed from the start for multiple categories but that wasnt a requirement untill now.
so would i take P_Cat out of the planes table?
am i on the right track?
thanx
Code: Select all
CREATE TABLE `planes` (
`itemId` int(11) NOT NULL auto_increment,
`P_Stock` varchar(20) NOT NULL default '',
`P_Name` varchar(150) NOT NULL default '',
`P_Scale` varchar(10) NOT NULL default '',
`P_Length` varchar(10) NOT NULL default '',
`P_Span` varchar(10) NOT NULL default '',
`P_CostPrice` decimal(10,2) NOT NULL default '0.00',
`P_Price` decimal(10,0) NOT NULL default '0',
`P_Desc` text NOT NULL,
PRIMARY KEY (`itemId`),
UNIQUE KEY `id` (`itemId`),
)
Code: Select all
CREATE TABLE `category` (
`C_ID` int(11) NOT NULL auto_increment,
`C_Category` varchar(20) NOT NULL default '',
PRIMARY KEY (`C_ID`),
UNIQUE KEY `id` (`C_ID`),
)
Code: Select all
CREATE TABLE `link_table` (
`itemId` int(11) NOT NULL auto_increment,
`C_ID` int(11) NOT NULL default '',
)
Posted: Tue Feb 08, 2005 9:05 pm
by feyd
that looks correct.
Posted: Tue Feb 08, 2005 9:07 pm
by C_Calav
thankyou.
how would my query go?
something like...
select * from planes where *****
would i have to have a sql join table?
thanx
Posted: Tue Feb 08, 2005 9:19 pm
by feyd
it requires a join query, yes.. INNER JOIN probably..
Posted: Tue Feb 08, 2005 9:26 pm
by C_Calav
thanx feyd