Re: [PHP-DB] Multiple inserts into a database
Daniel Smith wrote:
select itemname from deals d, items i, deal_items di where
d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1";
Would I be correct in thinking that the above is in effect the same as:
select itemname from deals, items, deal_items
where deals.dealid=deal_items.dealid
and items.itemid = deal_items.itemid
and dealname="meal 1";?
I haven't seen the use of "deals d", I'm guessing this basically means
"the table called deals but from now on I shall call it d" Does this
offer are benefits over my version above? I currently use this rather
long winded query because I find it helps me to work out what I am
using. I suppose x years down the line when I'm better at constructing
my queries, I would use your shorter version.
Yep they are the same. I'm using a table alias. See
http://www.designmagick.com/article/32/page/4 (it's a postgres article
but works just the same in mysql).
The problem I am having is going from the kind of select query you
mentioned to then using the results to go into a kind of order_history
table.
Using your example of the 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"
I think I'm going to get:
x--x
| itemname |
| burger 1 |
| chips|
| drink|
x--x
What I want to do is then take this data and insert it into an
order_history kind of table containing other fields, so ultimately I can
establish that customer x on 01/01/2001 at 12:00 ordered meal 1 which
consists of (burger 1, chips, drink), part of the purpose being so I can
individual items that can be ordered as part of a group as well as
individual items. The whole customer x part is stored elsewhere and I
am intending to tie the order information with something like:
x-x
| order_id | customer_id | timestamp |
| 1|32 |2006-11-30 18:01:07 |
| 2|13 |2006-11-30 19:01:07 |
| 3|46 |2006-11-30 20:01:07 |
x-x
xx
| order_history_id | order_id | itemname |
|1 |3 | burger 1 |
|2 |3 | chips|
|3 |3 | drink|
xx
When I get the results from your query, in order to get the above table,
would i be best to do something
foreach ($array as $value) {
INSERT blah, blah INTO blah blah, WHERE blah = $value
}
You might want to include the price in the order history as well -
otherwise as prices change, your old orders will be affected. Whether
that's a good or bad thing you have to decide but something to think
about anyway.
You can also just copy the data straight in using an insert into select
query:
http://dev.mysql.com/doc/refman/4.1/en/insert-select.html
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple inserts into a database
> select itemname from deals d, items i, deal_items di where
> d.dealid=di.dealid and i.itemid = di.itemid and dealname="meal 1";
Would I be correct in thinking that the above is in effect the same as:
select itemname from deals, items, deal_items
where deals.dealid=deal_items.dealid
and items.itemid = deal_items.itemid
and dealname="meal 1";?
I haven't seen the use of "deals d", I'm guessing this basically means
"the table called deals but from now on I shall call it d" Does this
offer are benefits over my version above? I currently use this rather
long winded query because I find it helps me to work out what I am
using. I suppose x years down the line when I'm better at constructing
my queries, I would use your shorter version.
The problem I am having is going from the kind of select query you
mentioned to then using the results to go into a kind of order_history
table.
Using your example of the 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"
I think I'm going to get:
x--x
| itemname |
| burger 1 |
| chips|
| drink|
x--x
What I want to do is then take this data and insert it into an
order_history kind of table containing other fields, so ultimately I can
establish that customer x on 01/01/2001 at 12:00 ordered meal 1 which
consists of (burger 1, chips, drink), part of the purpose being so I can
individual items that can be ordered as part of a group as well as
individual items. The whole customer x part is stored elsewhere and I
am intending to tie the order information with something like:
x-x
| order_id | customer_id | timestamp |
| 1|32 |2006-11-30 18:01:07 |
| 2|13 |2006-11-30 19:01:07 |
| 3|46 |2006-11-30 20:01:07 |
x-x
xx
| order_history_id | order_id | itemname |
|1 |3 | burger 1 |
|2 |3 | chips|
|3 |3 | drink|
xx
When I get the results from your query, in order to get the above table,
would i be best to do something
foreach ($array as $value) {
INSERT blah, blah INTO blah blah, WHERE blah = $value
}
Note, the above is only illustrative and not meant to be my attempt at a
working foreach statement.
Thanks for the help so far
Danny
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple inserts into a database
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
