Size Chart and List/menu

[maziagha]

Hello all!

I have this webshop today that i'm using. everything works fine until i decided that want my customers to be able to chose size and colors of the products. Now the problem is every products can have diferent sizes and colors compared to others.

My question is how can i make a MYSQL table and what can i do to solve this problem. Today i'm using a newby variable thing that i don't even wanna show because it sux.

If any of you have good solutin for this please let me know.

Thank you

[HuggieBear]

Id go for something like this...

[table]
[tr][td][b]SizeType[/b][/td][/tr]
[tr][td]SizeTypeID[/td][td]ChartName[/td][td]Description[/td][/tr]
[tr][td]1[/td][td]UKSHOE[/td][td]UK Shoe Sizes[/td][/tr]
[tr][td]2[/td][td]EUROPESHOE[/td][td]European Shoe Sizes[/td][/tr]
[tr][td]3[/td][td]UKMALEWAIST[/td][td]Male waist size UK[/td][/tr]
[tr][td]4[/td][td]UKFEMALESKIRT[/td][td]Female skirt size UK[/td][/tr]
[tr][td]5[/td][td]UKMARKET[/td][td]Standard UK Market size[/td][/tr]
[/table]

[table]
[tr][td][b]Sizes[/b][/td][/tr]
[tr][td]SizeID[/td][td]SizeChartID[/td][td]Size[/td][/tr]
[tr][td]1[/td][td]1[/td][td]8[/td][/tr]
[tr][td]2[/td][td]1[/td][td]9[/td][/tr]
[tr][td]3[/td][td]1[/td][td]10[/td][/tr]
[tr][td]4[/td][td]2[/td][td]42[/td][/tr]
[tr][td]5[/td][td]2[/td][td]43[/td][/tr]
[tr][td]6[/td][td]2[/td][td]44[/td][/tr]
[tr][td]7[/td][td]3[/td][td]38[/td][/tr]
[tr][td]8[/td][td]4[/td][td]10[/td][/tr]
[tr][td]9[/td][td]4[/td][td]12[/td][/tr]
[tr][td]10[/td][td]5[/td][td]S[/td][/tr]
[tr][td]11[/td][td]5[/td][td]M[/td][/tr]
[tr][td]12[/td][td]5[/td][td]L[/td][/tr]
[tr][td]13[/td][td]5[/td][td]XL[/td][/tr]
[/table]

This is your existing product table... Just add a 'size' column linked to the UniqueID of the SizeType table
[table]
[tr][td][b]Product[/b][/td][/tr]
[tr][td]ProductID[/td][td]Name[/td][td]Size[/td][td]Colour[/td][td]Price[/td][/tr]
[tr][td]1[/td][td]Nike Air Max[/td][td]1[/td][td]Red[/td][td]35.00[/td][/tr]
[tr][td]2[/td][td]Nike Air Zoom (US)[/td][td]2[/td][td]Blue[/td][td]40.00[/td][/tr]
[tr][td]3[/td][td]Silk Skirt[/td][td]4[/td][td]Black[/td][td]22.00[/td][/tr]
[tr][td]4[/td][td]Mens Cargo Trousers[/td][td]3[/td][td]Khaki[/td][td]37.00[/td][/tr]
[/table]

You can add an additional table in there for specific product sizes.  e.g. if Nike Air Zoom (US) only come in size 43.

Regards
Rich

[maziagha]

ok! looks pretty good
So if i want a product that i only have in S and M only what do i do then?
Yeah also i want it to be a List drop down menu to choose the sizes.

[HuggieBear]

Maybe another table similar to this:

[table]
[tr][td][b]ProductQuantity[/b][/td][/tr]
[tr][td]QuantityID[/td][td]QProdID[/td][td]QSizeID[/td][td]Quantity[/td][/tr]
[tr][td]1[/td][td]4[/td][td]10[/td][td]1[/td][/tr]
[tr][td]2[/td][td]4[/td][td]11[/td][td]1[/td][/tr]
[tr][td]3[/td][td]1[/td][td]4[/td][td]6[/td][/tr]
[tr][td]4[/td][td]1[/td][td]5[/td][td]3[/td][/tr]
[tr][td]5[/td][td]1[/td][td]6[/td][td]3[/td][/tr]
[/table]

I'm not sure this is 100% effecient, but it should work.  QuantityID is another unique value, QProdID is linked to your ProductID in your product table, QSizeID is linked to SizeID in your Sizes table and quantity is the amount you have.

That way you could only display the sizes of what you have in stock.  Some SQL something similar to this...

[code]
SELECT Size
FROM Sizes
WHERE SizeID IN (
    SELECT QSizeID
    FROM ProductQuantity
    WHERE QProdID = '$ProductID'
    AND Quantity != 0)
[/code]

$ProductID is the variable passed from into the search from PHP.

I'm sure some DB experts could point you in a better direction, but this should work.

Rich

[maziagha]

That could work, but the process to add products would be to complicated.

[HuggieBear]

As you edited your post to include how you'd create the HTML for it, I'll have a stab at that too...

Lets say that you're information is going to be displayed on productdetail.php which is passed the ProductID in the URL from a previous page.

[code]
<?php
if (!isset($_POST['ProductID'])){
  header('Location: home.php'); // Lets redirect our users if they enter our page without a ProductID.
}
else{
  $ProductID = $_POST['ProductID'];
  include("connect.php"); // Include your connection strings in this seperate file
  $query = "SELECT Size FROM Sizes WHERE SizeID IN (SELECT QSizeID FROM ProductQuantity WHERE QProdID = '$ProductID' AND Quantity != 0)";

  $result = mysql_query($query); // Execute the query
  if (!$result){
      die('Could run query: ' .mysql_error());
  }

  while ($sizes = mysql_fetch_array($result, MYSQL_ASSOC)){  // While there are still rows, do this ...
      $option[]=$sizes["Size"]; //push the rows into a new array (we can do this as we know we're only selecting one column)
  }

  echo "<select name=\"sizes\">";
  foreach ($option as $size){
      echo "<option value=\"$size\">$size</option>\n"; // echo each individual item in the $option array
  }
  echo "</select>";

  include("disconnect.php"); // Include your disconnect details in a seperate file
}
?>
[/code]

This should give you the values in a drop down list.

Rich