This question already has an answer here:
I'm working on a restaurant menu where I want to easily show different prices for different sizes of the same product, say pizza, for example.
I've simplified the information as the database is quite large, so please excuse me if it may get sloppy.
id | type | size | price | category
------------------------------------------------------
1 | all dressed | x-small | 7,65 | pizzas
2 | all dressed | small | 9,65 | pizzas
3 | all dressed | medium | 11,65 | pizzas
4 | all dressed | large | 18,65 | pizzas
5 | pepperoni and cheese | x-small | 6,65 | pizzas
6 | pepperoni and cheese | small | 8,65 | pizzas
7 | pepperoni and cheese | medium | 10,65 | pizzas
8 | pepperoni and cheese | large | 15,65 | pizzas
id int(11)
name varchar(255)
size varchar(60)
price varchar(50)
id | name
---|-----------
1 | pizzas
2 | subs
3 | wings
id int(11)
name varchar(255)
Pizzas
|------------------------------------------------------------------|
| Flavor x-small small medium large |
|------------------------------------------------------------------|
| all dressed 7,65 9,65 11,65 18,65 |
|------------------------------------------------------------------|
| pepperoni and cheese 6,65 8,65 10,65 16,65 |
|------------------------------------------------------------------|
The first loop was mostly to make sure the information got parsed.
----| queries.php |------------
public function my_products()
{
global $db;
$query = "SELECT * FROM foods ORDER BY name, price ASC";
return $db->select($query);
}
public function my_categories()
{
global $db;
$query = "SELECT * FROM categories ORDER BY name ASC";
return $db->select($query);
}
----| page.php |---------------
<?php $products = $query->my_products(); ?>
<?php $categories = $query->my_categories(); ?>
<table>
<?php
foreach($categories as $category)
{
echo "<h3>".$category->name."<h3>";
echo "<table>";
foreach ( $products as $product )
{
if($category->id == $product->category)
{
echo "<tr>";
echo "<td>".$product->name."</td>";
echo "<td>".$product->size."</td>";
echo "<td>".$product->price."</td>";
echo "</tr>";
}
}
echo "</table>";
}
?>
</table>
...which returns...
pizzas
| ------------|---------|-------|
| all dressed | x-small | 7,65 |
| all dressed | small | 9,65 |
| ... | | |
|-------------|---------|-------|
subs
wings
Now that I've confirmed that the information is returned, I'm trying to get the database to return the sizes of the pizzas in a particular order, but I don't want to hard code the values since the names of the sizes will vary from one product to another (pizzas would be x-small, small, medium, large, whereas subs would be 6" and 12", for example).
How could I go as to read the order of the sizes as they should be? Aside from making my field an ENUM type and going through all my entries, is there any other way to go through the $product->size and fill in the table appropriately?
</div>
You'd have to have a order attribute on the size column to specify the order to present the data back. The system simply can't know what's right, you have to give it the means to BE right.
The hackish way of doing this would be to embed non-visible ascii characters in the size column from low to high so that the engine sorts them in the desired manner.
The more correct way would be to have an order field related to the size field to specify the order of sizes for a given product's available sizes. With that then you could pivot using a pivot table
I would strongly consider normalizing your table structure more appropriately:
products
product_id - autoincrement primary key
name - varchar holding values like "all dressed"
category_id - foreign key to category table
product_sizes
product_size_id autoincrement primary key
product_id foreign key to products table
product_size - varchar holding values like 'x-small', 'large', '6"', '12"'
product_price - float
sort_order - int holds values you can use for sorting within is product_id grouping
categories
category_id - autoincrement primary key
category_name - varchar folding values like 'pizza', 'subs', etc.
Then querying them is simple:
SELECT
c.category_name AS category_name,
p.product_name AS product_name,
ps.product_size AS product_size,
ps.product_price AS product_price
FROM
categories AS c
INNER JOIN products AS p
ON c.category_id = p.category_id
INNER JOIN product_sizes AS ps
ON p.product_id = ps.product_id
ORDER BY
c.category_name ASC,
p.product_id ASC,
ps.product_order ASC
In PHP you would simply build a multi-dimensional array, with dimensions on category, product_id, and product_size,
$product_array = array();
while($row = [insert your DB fetch mechanism here]) {
$product_array[$row->category_name][$row->product_name][$row->product_size] = $row->product_price;
}
Outputting your table could look like this:
<?php
foreach ($product_array as $category => $cat_data) {
// output category heading
?>
<h2><?php echo $category; ?></h2>
<?php
// start a table perhaps
// get all sizes for this category
// this is done in a bit of a short-cut manner and assumes that each product in the category has the same options as the first product in the category
$size_array = array_keys($cat_data[key($cat_data)]);
?>
<table>
<tr>
<th>Flavor<th>
<?php
foreach($size_array as $size) {
?>
<th><?php echo $size; ?></ht>
<?php
}
?>
<th>Price</th>
</tr>
<?php
foreach ($cat_data as $prod_name => $prod_data) {
?>
<tr>
<td><?php echo $prod_name; ?></td>
foreach($prod_data as $size => $price) {
?>
<td><?php echo $price; ?></td>
<?php
}
?>
</tr>
<?php
}
?>
</table>
<?php
}
?>