Array setup?

[SocomNegotiator]

This is my DB setup:

 

products_sizing:

sizingid    sizingname    value    type        hidden    freesize

4              Large            1          absolute

5              X-Large          1          absolute

6              Small              1          absolute

 

products:

Of course I have productid, and all the other info about the product.

Then their is a field called "sizing" This then has the id's of whatever sizing method (products_sizing) it has assigned to it.

 

productid ....... sizing

1                      4,5

 

Notice how a product does not have to have all the sizing methods. I am trying to figure out how to create an array to grab this information for each product automatically. This way I can use the array in a foreach to loop through a users selected products and show them which sizing option they can choose from for each individual product.

 

Thanks so much for your help!

[thomashw]

Look into the explode function. Use a comma as the delimiter, and the string as the sizing value from your table.

[ignace]

It would be best to use a intermediary table, like:

 

products (product_id, product_name, ..)

products_sizes (size_id, size_label, ..)

products_has_sizes (product_id, size_id, quantity) -- <-- intermediary table

 

Retrieve all sizes for a certain product:

 

SELECT products.product_id, products.product_name, sizes.size_name, products_has_sizes.quantity

FROM products_has_sizes

JOIN products USING (product_id)

JOIN sizes USING (size_id)

 

class Store
{
   public function fetchAll()
   {
      $query = 'SELECT products.product_id, products.product_name, sizes.size_id, sizes.size_name, products_has_sizes.quantity' .
               ' FROM products_has_sizes JOIN products USING (product_id) JOIN sizes USING (size_id)';
      $result = array();
      foreach ($this->db->fetchAll($query) as $row) {
         $product = new Product();
         $product->setId($row['product_id']);
         $product->setName($row['product_name']);
         $size = new ProductSize();
         $size->setId($row['size_id']);
         $size->setName($row['size_name']);
         $product->addSize($size, $row['quantity']);
         $result[] = $product;
      }
      return $result;
   }
}

 

$dao = new Store();
foreach ($dao->fetchAll() as $product) {
  echo $product->getName(), ' (', $product->getQuantity($product->getSize()), 'x', $product->getSize(), ")<br>\n";
}