simple shopping cart, two mysql queries related...

help please

[matscott]

I have the following problem that I think would be fairly simple for any vet. I am a newbie so please bear with me.
I am writing a simple shopping cart using php mysql.
I have reached a point where I need to run a query, then run another query to a different table based on the result of the first. This may need to happen more than once.
Background.
I have a table for my basket and a table for item descriptions. My basket query requests all records with a certain order_id number, this will contain the item_id. I wish to then query the item table to output the item description.

I hope this is clear enough, I really need to know the best way of doing this.

[Birdman]

How about:

$query1 = "SELECT * FROM baskettable WHERE order_id = (your value here)";
$result1 = mysql_query($query1);
while ($row1 = mysql_fetch_array($result1)){

$query2 = "SELECT * FROM items WHERE item_id = $row1['item_id']";
$result2 = mysql_query($query2);
while ($row2 = mysql_fetch_array($result2)){

Do whatever you want with the info here. Call your item vars with $row2['fieldname'] and you can access your basket vars with $row1['fieldname'].

}

Hope it helps

}

[matscott]

Thanks alot.

I feel a bit silly now, I was looking at subqueries....too complex for me, your solution is ideal!

[Birdman]

Yes, I think it can be done in one query. I'm not too great at joins but I'll give it a go.

SELECT * FROM baskettable LEFT JOIN items ON baskettable.item_id = items.id WHERE order_id = (value)

Not positive it's correct. Give it a shot and see what you get. I like to use phpmyAdmin for testing queries.

[coopster]

Welcome to WebmasterWorld, matscott!

MySQL subqueries [mysql.com] can be quite handy but there is a dependency on which version of MySQL [mysql.com] you are running.

However, BirdMan has provided (IMHO) the best option for you here in his JOIN. Be aware that if for some reason there is no matching record for the items the row of returned data will have all it's item table columns set to NULL.