Create table fields with types

[Adamhumbug]

Hi All,

I apologise for the poor post name but I wasn't sure how best to word this.

I am wanting to store menus in a table - by menu I mean (starter, main, dessert)

So I have the following:

menu_id

menu_item1

menu_item2

...

menu_item20

 

I would ideally like to have 8 menu items that identify themselves as starters with the same for main and dessert.

I am not sure if this is possible but I am struggling to find the right way of storing this information so that I can easily output them to the correct section of the page based on what course they are.

I wanted to try and avoid having 3 tables but this may be the only way...

Any advice on this would be greatly appreciated.

 

Kind Regards

 

[Barand]

How you store them is going to depend on your menu structure and the relationships between menus and dishes and dishes and other dishes.

EG

• 1 menu with
  • choice of all starters
  • choice of all main dishes
  • choice of all desserts

or

• Menu 1
  • choice of some starters
  • choice of some main dishes
  • choice of some desserts
• Menu 2 (Vegan)
  • choice of some starters
  • choice of some main dishes
  • choice of some desserts

As you are the only one who knows this you are pretty much on your own. I advise you read up on "Data Normalization"

[Adamhumbug]

Hi,

Thanks for your reply, the second option would be the one that i will be using.

menu 1 may have 2 starter, 2 mains and 2 desserts

menu 2 may have 5 starters, 5 mains and 5 desserts

When the menu is picked i need starters to go into one section, mains into another and so on.

I feel that i may have to have a seperate table for starters, mains and desserts.

I will for sure heed your suggestion.

Kind regards

[Barand]

Could the same dish appear on more than one menu?

[Adamhumbug]

Arguably yes

[Barand]

Then I'd go with something like this

+-----------------+                                    +-------------------+            +-----------------+
|     menu        |                                    |     dish          |            |  dish_type      |
+-----------------+                                    +-------------------+            +-----------------+
|  menu_id        |--+                         +-------|  dish_id          |     +------|  dish_type_id   |
|  description    |  |                         |       |  name             |     |      |  description    |
+-----------------+  |                         |       |  dish_type_id     |>----+      +-----------------+
                     |                         |       +-------------------+
                     |                         |
                     |     +---------------+   |
                     |     |  menu_dishes  |   |
                     |     +---------------+   |
                     +----<|  menu_id      |   |
                           |  dish_id      |>--+
                           +---------------+

 

[Adamhumbug]

Ahh,  i like this.  I see the benefit of doing it this way. Thanks very much

[Barand]

FYI - Proof of Concept;

DATA

menu table                menu_dish table       dish_type table                dish table                          
+---------+-------------+ +---------+---------+ +--------------+-------------+ +---------+-----------------------------+--------------+
| menu_id | description | | menu_id | dish_id | | dish_type_id | description | | dish_id | name                        | dish_type_id |
+---------+-------------+ +---------+---------+ +--------------+-------------+ +---------+-----------------------------+--------------+
|       1 | Lunch Menu  | |       1 |       1 | |            1 | Starter     | |       1 | Tomato and coriander soup   |            1 |
|       2 | Dinner Menu | |       1 |       3 | |            2 | Main        | |       2 | Trout with almonds          |            1 |
+---------+-------------+ |       1 |       5 | |            3 | Dessert     | |       3 | Avocado on Toast            |            1 |            
                          |       1 |       7 | +--------------+-------------+ |       4 | Smoked Salmon               |            1 |
                          |       1 |       9 |                                |       5 | Chicken liver pate          |            1 |
                          |       1 |      11 |                                |       6 | Tomato and mozzarello salad |            1 |
                          |       1 |      14 |                                |       7 | Mixed grill                 |            2 |
                          |       1 |      15 |                                |       8 | 8oz sirloin steak           |            2 |
                          |       2 |       1 |                                |       9 | Rissoto with wild mushrooms |            2 |
                          |       2 |       2 |                                |      10 | Cider braised pork          |            2 |
                          |       2 |       4 |                                |      11 | Cod, chips and mushy peas   |            2 |
                          |       2 |       5 |                                |      12 | Quail stuffed with prunes   |            2 |
                          |       2 |       6 |                                |      13 | Wild boar chops             |            2 |
                          |       2 |       8 |                                |      14 | Apple pie                   |            3 |
                          |       2 |       9 |                                |      15 | Ice cream                   |            3 |
                          |       2 |      10 |                                |      16 | Fruit salad                 |            3 |
                          |       2 |      13 |                                |      17 | Rhubarb crumble             |            3 |
                          |       2 |      15 |                                |      18 | Cheese selection            |            3 |
                          |       2 |      16 |                                +---------+-----------------------------+--------------+
                          |       2 |      17 |
                          |       2 |      18 |
                          +---------+---------+

QUERY

select m.description as Menu
     , dt.description as Course
     , GROUP_CONCAT(d.name SEPARATOR ', ') as Choices
FROM menu_dish md
     JOIN menu m USING (menu_id)
     JOIN dish d USING (dish_id)
     JOIN dish_type dt USING (dish_type_id)
GROUP BY md.menu_id, d.dish_type_id;

RESULT

+-------------+---------+---------------------------------------------------------------------------------------------------------------+
| Menu        | Course  | Choices                                                                                                       |
+-------------+---------+---------------------------------------------------------------------------------------------------------------+
| Lunch Menu  | Starter | Tomato and coriander soup, Chicken liver pate, Avocado on Toast                                               |
| Lunch Menu  | Main    | Cod, chips and mushy peas, Rissoto with wild mushrooms, Mixed grill                                           |
| Lunch Menu  | Dessert | Ice cream, Apple pie                                                                                          |
| Dinner Menu | Starter | Smoked Salmon, Tomato and coriander soup, Tomato and mozzarello salad, Chicken liver pate, Trout with almonds |
| Dinner Menu | Main    | Wild boar chops, 8oz sirloin steak, Cider braised pork, Rissoto with wild mushrooms                           |
| Dinner Menu | Dessert | Rhubarb crumble, Cheese selection, Fruit salad, Ice cream                                                     |
+-------------+---------+---------------------------------------------------------------------------------------------------------------+

(Soup, rissotto and ice-cream on both menus)

[Adamhumbug]

Thanks for this, some excellent choices here also!!

[Adamhumbug]

Hi Guys, so i have built a slight variation of this.

I have combined the course and the item name into one table.

$sql = "SELECT menu_name, menu_price, menu_item_name, menu_item_catagory
FROM ssm_menu a
INNER JOIN
ssm_menu_connection b
on a.menu_id = b.menu_id
INNER JOIN
ssm_menu_items c
on b.menu_item_id = c.menu_item_id
WHERE a.menu_id = ?";

This above is the sql i use to get the data out.

The menu name i need one, the price i need one, but obviously there will be many dishes that come back to form the full menu.

I am having some trouble echoing all of this out.

I tried the following but the while statement does not seem to be doing anything.  All that i am getting out is the table with the header row.  I have run the sql in the db and it is selecting the correct information.

$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $_GET['q']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($mname, $mprice, $mitname, $mitcat);
$stmt->fetch();
$stmt->close();

echo "<table class='table table-striped table-hover table-bordered text-center mt-3'>";
echo "<tr>";
echo "<th colspan='2'>".$mname."</th>";
echo "<th colspan='2'>".$mprice."</th>";
echo "</tr>";
while ($stmt->fetch()){
	echo "<tr>";
	echo "<td>" . $mitname . "</td>";
	echo "<td>" . $mitcat . "</td>";
	echo "<td><input /></td>";
	echo "<td><input /></td>";
	echo "</tr>";
}
echo "</table>";

I am sure that i am doing something simple wrong but google is not being my friend at the minute.

 

Kind regards

Adam

[Barand]

12 minutes ago, Adamhumbug said:

I have combined the course and the item name into one table.

Why would you want to do that? Now, if you want "Entree" to appear on the menu instead of "Starter" you now have to change it in every starter item. With my normalised model you change it in on place only. Database tables are not spreadsheets.

Closing the statement before fetching the records from it probably isn't your best idea either.

Having read the first record to get the menu name and price you need to use a do { ... } while() loop instead of a while loop. Otherwise you lose item and category fro the first record.

[Adamhumbug]

10 minutes ago, Barand said:

Why would you want to do that? Now, if you want "Entree" to appear on the menu instead of "Starter" you now have to change it in every starter item. With my normalised model you change it in on place only. Database tables are not spreadsheets.

Closing the statement before fetching the records from it probably isn't your best idea either.

Having read the first record to get the menu name and price you need to use a do { ... } while() loop instead of a while loop. Otherwise you lose item and category fro the first record.

I undertand what youre saying here - and you are of course correct.

 

Thank you, that really helped.  I have moved my statement close to the end and that did the trick.

Now my drama is i want to list the starters under the header starters, the mains under the header main and so on.

Thanks so much

Kind Regards

Edited February 8, 2019 by Adamhumbug

[Barand]

Have you checked the first item in your menu is there?

[Adamhumbug]

Ahh, you are very good at this.

No i am not getting the first one

[Adamhumbug]

20 minutes ago, Barand said:

Have you checked the first item in your menu is there?

I found a way to make it work - looks nasty though but it works.

$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $_GET['q']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($mname, $mprice, $mitname, $mitcat);

$first = true;
while($stmt->fetch()){
    if($first) {
echo "<form>";
echo "<table class='table table-striped table-hover table-bordered text-center mt-3'>";
echo "<tr>";
echo "<th colspan='5'><div style='width:50%;float:left;'>".$mname."</div><div style='width:50%;float:right;'>".$mprice."</div></th>";
echo "</tr>";
	$first = NULL;
}
	echo "<tr>";
	echo "<td>" . $mitname . "</td>";
	echo "<td>" . $mitcat . "</td>";
	echo "<td><input /></td>";
	echo "<td><input /></td>";
	echo "</tr>";
}

echo "</table>";
echo "<input type='submit' action='' class='btn btn-primary' value='Submit'>";

$stmt->close();
?>

 

[Barand]

I'd move the form and table opening tags above the while() loop then close them after the loop (at present you don't close the form)

[Adamhumbug]

3 minutes ago, Barand said:

I'd move the form and table opening tags above the while() loop then close them after the loop (at present you don't close the form)

Thanks - this is done.

So i have been thinking about how to output my results in section in the form having a header of each of the $mitcat (the starter, main, dessert)

So i would have the header as it is, then a tr that outputs starter then all the starters under it then a mains header and so on.

Is this doable with what i have or have i shot myself in the foot not taking your advice earlier on

[Barand]

It's still doable.

I'd fetch the results into an array (key = category) then loop throught array to output

$data = [
         'starter'  => ['Soup', 'Pate'],
         'main'     => ['Cod', 'Steak', 'Quail'],
         'dessert'  => ['Kippers & custard', 'Sago pudding']
        ];

then

foreach data as course => items
    output course
    foreach items as i
        output i
    end
end

 

Edited February 8, 2019 by Barand

[Adamhumbug]

Ok, sounds like a plan.

Do i need to change my sql for this or is it something that i do in my php?

[Barand]

SQL's fine - it's a job for php