This question already has an answer here:
- MySQL pivot table 8 answers
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.
Mysql tables
table: foods
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)
table: categories
id | name
---|-----------
1 | pizzas
2 | subs
3 | wings
id int(11)
name varchar(255)
Desired layout result
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 |
|------------------------------------------------------------------|
Tried code
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>
