Mysql restaurant menu

Question

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 1

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

Answer 2

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
}
?>