Help with database design and quering rows

Question

What's this database design and it is displaying three rows of id in names in products and three rows of prices, and three row of each field when I am looking for to display the name, variety and price in one row then the next row display another different set of field values different than the first row. How can I achieve that with that with the database design below?

CREATE TABLE products (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(255)
);

CREATE TABLE product_varieties (
product_id INT,
variety VARCHAR(100),
price DOUBLE,
description TEXT,
PRIMARY KEY (product_id, variety)
);

INSERT INTO products (id, name) VALUES (1, 'Cotél de camarone');
INSERT INTO product_varieties (product_id, variety, price) VALUES (1, 'Small Tray',   2.9);
INSERT INTO product_varieties (product_id, variety, price) VALUES (1, 'Medium Tray', 6.9);
INSERT INTO product_varieties (product_id, variety, price) VALUES (1, 'Large Tray', 8.9);

Go here to see an example of what I am talking. Again I want the price 2.9 to display in small tray of the first row, then 6.9 display in the medium tray of the first row, and 8.9 display on large tray of the first row. Right now is display prices for row...

help.

Answer 1

try converting your data into a tree-alike structure (array of arrays), which is much simpler to output in the "grouped" form

something like this (pseudocode)

 $tree = array();
 $sql = "select * from products
     left join product_varieties on product_varieties.product_id = products.id";
 $sth = query($sql);
 while($row = fetch($sth)) {
    $id = $row['id'];
    $tree[$id]['name'] = $row['name'];
    if($row['variety'])
       $tree[$id]['varieties'][] = $row;
 }

this builds an array like

   1 => array
      name => 'Cotél de camarone',
      varieties => array
          array(1, 'Small Tray', 2.9)
          array(1, 'Medium Tray', 6.9)
          etc
   2 => array
      name => 'Something else',
      varieties => array
          array(1, 'Small Tray', 2.9)
          array(1, 'Medium Tray', 6.9)
          etc

loop over this array and generate a html structures you need

foreach($tree as $product)
    <div>
    echo $product['name']
    foreach($product['varieties'] as $variety)
         etc etc
    </div>

hope i'm making sense