Confused on Select Statement

[artisticre]

OK I get this concept however, I can't figure out how to write the select statement. I know I need to select from category, colors, price, product, size where category.categoryID = price.priceID but I am not sure how to do the rest. I listed the tables below. Any help with the select statement would be great. I am slowly starting to understand all this.

This link is the structure of my database

CREATE TABLE category (
categoryID int(5) NOT NULL auto_increment,
category varchar(20) NOT NULL default '',
PRIMARY KEY (categoryID)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

CREATE TABLE colors (
colorID int(11) NOT NULL default '0',
color varchar(30) NOT NULL default ''
) TYPE=MyISAM;

CREATE TABLE price (
categoryID int(5) NOT NULL default '0',
sideID int(5) NOT NULL default '0',
price float(10,2) NOT NULL default '0.00'
) TYPE=MyISAM;

CREATE TABLE product (
productID int(5) NOT NULL auto_increment,
prod_name varchar(30) NOT NULL default '',
prod_desc varchar(255) NOT NULL default '',
categoryID int(5) NOT NULL default '0',
PRIMARY KEY (productID)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

CREATE TABLE product_colors (
productID int(5) NOT NULL default '0',
colorID int(5) NOT NULL default '0'
) TYPE=MyISAM;

CREATE TABLE product_images (
imageID int(5) NOT NULL auto_increment,
productID int(5) NOT NULL default '0',
image_path varchar(100) NOT NULL default '',
PRIMARY KEY (imageID)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

CREATE TABLE product_sizes (
ProductID int(5) NOT NULL default '0',
sideID int(5) NOT NULL default '0'
) TYPE=MyISAM;

CREATE TABLE size (
sideID int(5) NOT NULL auto_increment,
size varchar(15) NOT NULL default '',
PRIMARY KEY (sideID)
) TYPE=MyISAM AUTO_INCREMENT=7 ;

[dalecosp]

<<The below is not a flame, not intended to be "sharp", "hateful", or any kind of "reprimand" other than the slightest hint that I don't think this is the best way. >> 😉

I'm certainly no database expert, so you'll want a second opinion, but my first thought is a quote from Mr. Scott (of Star Trek fame):

The more they overthink the plumbin', the easier 'tis to stop up the draen"

Are you sure that this is the database schema you want to use?

We are talking about a product database, presumably for online shopping, right? So what would be wrong with having just one table, perhaps called "products", which had all the relevant data in it? Surely you don't have that many products without a <<random attribute>>, so what are you accomplishing with such a complex structure ... other than the obvious accomplishment of making it hard on yourself when attempting to construct queries?

Like I said, just my $0.02 ... get another opinion or two...

[artisticre]

Thanks for the suggestion. I have a tshirt design that comes in different colors and the price is different for each size. If I make it one big table, there will be a lot of empty space and or redundant space. I just need help with the SELECT statement

[siwis]

I tend to agree in part with dalecosp that your database is far more complex than it needs to be.

I would treat sizes as options too

You would therefore have tables:

#
# Table structure for table `optionGroups`
#

CREATE TABLE optionGroups (
  groupID smallint(3) NOT NULL auto_increment,
  groupName text NOT NULL,
  PRIMARY KEY  (groupID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `options`
#

CREATE TABLE options (
  optionID mediumint(6) NOT NULL auto_increment,
  groupID smallint(3) NOT NULL default '0',
  optionValue varchar(64) NOT NULL default '',
  PRIMARY KEY  (optionID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `productCats`
#

CREATE TABLE productCats (
  catID mediumint(6) NOT NULL auto_increment,
  catName text NOT NULL,
  PRIMARY KEY  (catID)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `productOptions`
#

CREATE TABLE productOptions (
  productID mediumint(6) NOT NULL default '0',
  optionID mediumint(6) NOT NULL default '0',
  optionPrice double NOT NULL default '0',
  optionImage tinyint(1) NOT NULL default '0',
  optionImageFile varchar(255) NOT NULL default '',
  optionUseImage mediumint(6) NOT NULL default '0'
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `products`
#

CREATE TABLE products (
  productID mediumint(6) NOT NULL auto_increment,
  prodName text NOT NULL,
  prodDesc text NOT NULL,
  catID smallint(3) NOT NULL default '0',
  PRIMARY KEY  (productID)
) TYPE=MyISAM;

Table optionGroups contains the option headings, eg size, colour etc
Table products carries the core product information
Table options carries the option definitions and relates to the optionGroups table
Table productCats carries the category information
Table productOptions caries the links between all the tables. Each "option" carrying the price for that option and an image reference. I plonked in a "optionUseImage" field such that the image designated for another "option" could be used.

You query then needs to feature a number of LEFT JOINS

SELECT 
	productOptions.optionPrice, 
	productOptions.optionImage, 
	productOptions.optionUseImage, 
	optionGroups.groupName, 
	options.optionValue
FROM
	productOptions
LEFT JOIN
	products
ON
	productOptions.productID = products.productID
LEFT JOIN
	options
ON
	productOptions.optionID = options.optionID
LEFT JOIN
	optionGroups
ON
	options.groupID = optionGroups.groupID
WHERE
	products.productID = $id
ORDER BY
	optionGroups.groupID ASC