I am creating an ecommerce site and am trying to figure out how to get the users to be able to select the appropriate options for a product.
At present I have a products table, this includes a column for the ID's of which attributes apply.
My attributes table then has a 'name' column and columns 'attribute1' - 'attribute15'. So if the name is 'Size' the columns 'attribute1' - 'attribute15' would have values like 'small','medium','large',etc,. with some blank columns depending on how many attributes that product has (max 15).
So then on my backend I have a form for the pricing. So for instance if a product has two attributes (size and colour), it will have each combination in the form. For example: Black - Small, Black - Medium, Black - Large, Blue - Small, Blue - Medium, Blue - Large, etc,.
Each of these combinations has a checkbox and a price input. If I check the checkbox it means that combination is availbale and I store the price value. So my pricing table has a product ID, possibly multiple attribute ID's, and a column 1-15 to say which option for that attribute this price is for.
At the moment my product page first queries my products table to see what attributes apply. It creates a dropdown for each of these but not all of them apply as that combination mightn't be available. For instance we could have a 'small black item' and a 'large black item' but no 'medium black item'. So I'm looking for a way that if they select black in the colour dropdown there is no option for medium in the other dropdown.
This is very complicated to explain what is happening so I hope it makes sense.
Inside a foreach loop I am getting the ID and column with a price from my pricing table, and then, searchng the attributes table to display the dropdown for that attribute like so:
$attribute_id = $attribute[0];
$attribute_column = $attribute[1];
$params = [$attribute_id];
$sql = "SELECT * FROM attributes WHERE id=?";
$stmt = DB::run($sql,$params);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$attribute_title = $row["name"];
}
$attribute_list .= $attribute_title.'<br />';
$attribute_list .= "<select name='attribute_price' class='attribute_price'>";
$attribute_list .= "<option value='0'>Choose an option...</option>";
$params = [$attribute_id];
$sql = "SELECT * FROM attributes WHERE id=?";
$stmt = DB::run($sql,$params);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
for($i=1; $i<=15; $i++){
$column = 'attribute'.$i;
$product_attribute = $row["$column"];
if($product_attribute != ""){
$attribute_list .= "<option value='";
$attribute_list .= $attribute_id.'-'.$column;
$attribute_list .= "'>$product_attribute</option>";
}
}
}
$attribute_list .= "</select>";
At present they can select any of the options from that set of attributes even though it mightn't apply for that product. If they do it just says that the combination isn't available.
Is there a better way to only show the attributes that apply to this product. Any help or suggestions with this would be greatly appreciated.
