query question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

query question

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

all planes.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

quite likely, yes. It should save a bunch of space.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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 '', 
)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that looks correct.
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it requires a join query, yes.. INNER JOIN probably..
User avatar
C_Calav
Forum Contributor
Posts: 395
Joined: Wed Jun 02, 2004 10:55 pm
Location: New Zealand

Post by C_Calav »

thanx feyd
Post Reply