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/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
