Daniel Smith wrote:
The kind of thing I'm looking to do is to insert a variable number of
records into a database as a consequence of a form entry.
I am using PHP with a MySQL database to store information that is
vaguely analogous to a fast food online ordering system. I have a table
containing individual items with a short code for each item. A user
would enter the short code and this gets stored.
x-----------------------------------x
| item_id | item_name | item_code |
| 1 | Burger | bur |
| 2 | Fries | fr |
| 3 | Cola | co |
x-----------------------------------x
What I want to do is have a way of letting the customer order multiple
items with one code, e.g. entering "meal" results in burger/fries/cola
being entered in an order table.
I realise i need in effect a translation table, that contains meal with
references to the items burger/frires/cola, something like below so I
could do something "SELECT item_id FROM deal WHERE deal_name = meal" to
get the individual items for the "meal"
x-----------------------------------x
| deal_id | deal_name | item_id |
| 1 | meal | 1 |
| 2 | meal | 2 |
| 3 | meal | 3 |
x-----------------------------------x
So far, I can understand what I need to do to achieve this. What I am
having trouble trying to understand what to do and how best to do it, is
to get the individual elements of the meal entered into an order table.
Would a foreach statement that works through the array produced by the
"meal" query, inserting each item in the meal be the best way forward?
The "deals" on offer would vary in size e.g. burger+fries/burger+fries
+cola+ice cream+toy so whatever solution is use, would have to cope with
changing size.
Note: As you might have noticed already, I'm not the world's best
PHP/MySQL programmer but I'm ready and willing to try stuff.
I'd do it a little differently.
create table deals(dealid, dealname);
create table items(itemid, itemname);
create table deals_items(dealid, itemid);
Then you have something like this:
---------------------
| dealid | dealname |
---------------------
| 1 | meal 1 |
| 2 | meal 2 |
.....
the items table contains
---------------------
| itemid | itemname |
---------------------
| 1 | burger 1 |
| 2 | burger 2 |
| 3 | chips |
| 4 | drink |
.....
then for the deal_items table it joins both together:
-------------------
| dealid | itemid |
-------------------
| 1 | 1 |
| 1 | 3 |
| 1 | 4 |
....
To get everything for "meal 1" you can then do a simple join query:
select itemname from deals d, items i, deal_items di where
d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1";
You'll need an index on deal_items(dealid, itemid) and dealid & itemid
in their own tables will be primary keys.
This is just called a "many-to-many" relationship.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
