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