php/MySQL Shopping Cart query

[luckyblackcat]

Hi,
I'm writing a shopping cart using Flash-php-MySQL

$username & $itemsincart are passed from Flash to this script.
I wish to echo back to Flash the four arrays at the bottom.
I am not happy that the 2 SELECT queries are correct/efficient.
It doesn't work and I've been messing with it for so long I can't think straight anymore. I need a break.
Meanwhile can anybody out there help me??

Thanks for reading this...

usual db link stuff .....

for($i=1; i<$itemsincart; $i++){
$query = &quot;SELECT item_no FROM cart WHERE username = '$username' ORDER BY 'item_no' ASC LIMIT ($i-1),1&quot;;
$result=mysql_query($query);
$query = &quot;SELECT * FROM `items` WHERE item_no = '$result'&quot;;
$result=mysql_query($query);
while($row=mysql_fetch_array($result)){
$title[$i] = &quot;$row[0]&quot;;
$price[$i] = &quot;$row[2]&quot;;
$item_no[$i] = &quot;$row[4]&quot;;
$status[$i] = &quot;$row[6]&quot;;
}
echo &quot;&title[&quot;.&quot;$i&quot;.&quot;]=&quot;.&quot;$title[$i]&&quot;;
echo &quot;&price[&quot;.&quot;$i&quot;.&quot;]=&quot;.&quot;$price[$i]&&quot;;
echo &quot;&item_no[&quot;.&quot;$i&quot;.&quot;]=&quot;.&quot;$item_no[$i]&&quot;;
echo &quot;&itemstatus[&quot;.&quot;$i&quot;.&quot;]=&quot;.&quot;$status[$i]&&quot;;
}

close link ....

 

[piti]

of course it doesn't work ;-)
you are using query result as a scalar parameter for the next query

$result=mysql_query($query);
$query = &quot;SELECT * FROM `items` WHERE item_no = '$result'&quot;;

must be (if you want it to work)
$result=mysql_query($query);
$query = &quot;SELECT * FROM `items` WHERE item_no = &quot;.mysql_result($result,0);

but try this

$query = &quot;SELECT items.* FROM cart INNER JOIN items ON items.item_no = cart.item_no WHERE cart.username = '$username' ORDER BY items.item_no ASC&quot;;
$result=mysql_query($query);
$i=1
while($row=mysql_fetch_array($result)){
echo &quot;&title[&quot;.$i.&quot;]&quot;=&quot;.$row[0];
echo &quot;&price[&quot;.$i.&quot;]&quot;=&quot;.$row[2];
echo &quot;&item_no[&quot;.$i.&quot;]&quot;=&quot;.$row[4];
echo &quot;&itemstatus[&quot;.$i.&quot;]&quot;=&quot;.$row[6];
}

this way - only one access to the db instead of 2*itemsincart

keep in mind - the code is not tested

hope that helps