query question
Moderator: General Moderators
query question
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
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
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
use a relational linking.. :: http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
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.
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.
would making another table with the product id and category id still be the best way?
many thanx
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 ;many thanx
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
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 '',
)