Dave Woodhall Dave Woodhall - 1 month ago 11
MySQL Question

Mysql restaurant menu

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?

Answer

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