Re: 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 x----------------------------------------x | order_history_id | order_id | itemname |

|        1         |    3     | burger 1 |
|        2         |    3     | chips    |
|        3         |    3     | drink    |

x----------------------------------------x 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/