Ive come up with a curly problem and im wondering if anyone can shed some
lights on it. I looked under the SELECT part of Mysql but it didnt seem to
cover this and I didnt know how to phrase it.
What I have is a displayed list on the site. This data that makes up the
list comes from 2 tables. 'item' and 'location' are the 2 table names. For
each item there is 1 entry in the item table. But under location there is
several entries depending on where the part is. Its done this way to log a
history of whether the item has been sent out, or received in. Each entry
in the location table is date stamped and records the items location, and
the item_id field.
Currently I am doing my list like this.
$item_q=mysql_query("select * from item order by item name");
while ($item=mysql_fetch_array($item_q)) {
$location_q=mysql_query("select * from location where
item_id=$item_q[item_id] order by date desc limit=1");
$location=mysql_fetch_array($location_q);
echo"$itemlist[item_name] - $location[location] - $location[date]";
}
The reason I do it this way is that I firstly need it to list all items,
but then next step (and next SELECT) it grabs the most recent entry in
location for that item. My problem is, I need to be able to combine the
SELECT queries into 1, so that I can order by either Location, Date or Name
Im not sure if this sort of thing is possible but it would make life so
much easier if I could do it.
Thanks
Chris
--
Chris Aitken - Webmaster/Database Designer - IDEAL Internet
email: [EMAIL PROTECTED] phone: +61 2 4628 8888 fax: +61 2 4628 8890
--------------------------------------------
Unix -- because a computer's a terrible thing to waste!
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
