Receiving duplicate answers when query a database

ok I have looked around here for a few days now, and I did find this : MySQL GROUP BY two columns This has been helpful but hasn't worked for what I am currently working on.

So here is the scope of things I have 3 different tables

table 1 (quantities) consist of:

quantity_id int(11) auto_increment not null (primary key)
product_id int(11) not null
color_id int(11) not null
size_id int (11) not null
quantity int (11) not null

table 2 (colors) consist of:

color_id int(11) auto_increment not null (primary key)
color_name varchar(255) 

table 3 (sizes) consist of:

size_id int (11) auto_increment not null (primary key)
size varchar(255)

SELECT s.size, c.color_name, q.quantity, q.size_id, q.product_id, q.color_id
FROM quantities q, colors c, sizes s
WHERE q.color_id = c.color_id
AND q.size_id = s.size_id
AND q.product_id = $pid
GROUP BY q.color_id
ORDER BY q.size_id

$pid is the product id passed by the form.

When I load the results into 2 different select statements: one for color, one for size I get multiple of the same color or size:

red             s
red             L
yellow          L

I understand that it is literally pulling the rows out of the database and displaying them, but how do I not repeat myself?

I have tried this query

SELECT s.size, c.color_name, q.quantity, q.size_id, q.product_id, q.colo开发者_JS百科r_id
FROM quantities q LEFT JOIN colors c ON q.color_id = c.color_id
LEFT JOIN sizes s ON q.size_id = s.size_id
WHERE q.product_id = $pid

Is there a better way?

Your question is a bit unclear, but the query below will give you color, size and quantity information given a product id by using joins.

Updated with Quantity Constraint

SELECT size, color_name, quantity, sizes.size_id, colors.color_id  
FROM quantities
    INNER JOIN colors ON quantities.color_id = colors.color_id
    INNER JOIN sizes ON quantities.size_id = sizes.size_id
WHERE product_id = ? and quantity > 1

You are getting repeated rows because you are getting all the combinations of color and size. I imagine there are several colors for the same size, which is normal. If you want to force the integrity, change you left join for an inner join so that you only get records that have a match.

SELECT s.size, c.color_name, q.quantity, q.size_id, q.product_id, q.color_id
FROM quantities q INNER JOIN colors c ON q.color_id = c.color_id
INNER JOIN sizes s ON q.size_id = s.size_id
WHERE q.product_id = $pid

UPDATE: If you only want to show quantities >=1 then do this:

SELECT s.size, c.color_name, q.quantity, q.size_id, q.product_id, q.color_id
FROM quantities q INNER JOIN colors c ON q.color_id = c.color_id
INNER JOIN sizes s ON q.size_id = s.size_id
WHERE q.product_id = $pid and q.quantity>=1

If you also only want to show one color and one size and the sum of all qty then do this:

SELECT min(s.size) as size, min(c.color_name) as color, sum(q.quantity) as TotalQty
FROM quantities q INNER JOIN colors c ON q.color_id = c.color_id
INNER JOIN sizes s ON q.size_id = s.size_id
WHERE q.product_id = $pid and q.quantity>=1

If you only store id and name for color it could very well be a php function, so you don't need a color table:

<?PHP 
function color($color)
{
    $colors['red']=1;
    $colors['blue']=2;
    $colors['green']=3;

    return $colors[$color];
}
//example of usage
  $sql ="... where `color`=".color($_POST['color'])." "; 
?>

Also since size is a small value (is not a big text for example) then you don't need a size table at all. All you need is a product and a quantity (better rename it to product_info) table:

mysql_query("   
CREATE TABLE `Product` (
  `product_id`  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_name` varchar(30)    NOT NULL , 
  unique(`product_name`),
  PRIMARY KEY  (`product_id`)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;") 
or die(mysql_error());  

mysql_query("   
CREATE TABLE `Product_Info` (
  `product_id`  INT UNSIGNED NOT NULL  ,
  `color_code` char(3)    NOT NULL ,  
  `prod_col_size` varchar(15)    NOT NULL ,  
  `quantity` mediumint   UNSIGNED NOT NULL  ,
  PRIMARY KEY  (`product_id`,color_code,prod_col_size), 
  CONSTRAINT `prod_id_FK` FOREIGN KEY (`product_id`)
        REFERENCES `Product` (`product_id`)
         ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=COMPACT;") 
or die(mysql_error());

This is not perfect normalization but you already choosed this way. The queries will be very easy this way. To bother with long joins in a well normalized schema makes sense. To bother for no reason at all!? You only need to make a join in two tables in case you need some filed from the product table.

    /*Find how many red items has a specific product*/

select sum(`Product_Info`.`quantity`) 
  from `Product_Info` where `color_code`='124' and `product_id`='3';