Problem retrieving data from mysql

[Anon]

Hello.
I am having some problem with my code. I have two tables, the order and orderdetail table. The primary key for order table is orderid which is auto_incremement. After finished inserting data into the order table, I would then retrieve the orderid from the order table and insert the details into the orderdetail table.

Code 1:
$purchase="INSERT INTO order VALUES('', '$memberid', '$date', '$time', $shipping, $total)";
$purchase2=mysql_query($purchase);

Code 2:
if ($purchase2)
{
$ret="SELECT orderid FROM order WHERE custid='$memberid' and order_date='$date' and order_time='$time' and shipping=$shipping and amount=$total";
$ret2=mysql_query($ret);
}

Code 1 executed ok but the problem is with Code 2. When I want include the amount=$total criteria in my SQL statement, it fails to find the matching record eventhough the record does actually exist and met all the criteria. Could anyone comment on this? By the way, if I exclude the amount=$total criteria, then everthing would run fine.

[[deleted]]

Only thing I can suggest is to echo the $ret variable out and see exactly how that query is being built. See what happens when you run it manually against the database. Is 'amount' the actual field name? Or is it something like 'Amount'? I don't see any blatent errors, it's got to be something along these lines....

Matt Wade
http://codewalkers.com/

[Anon]

I tried echoing out the $ret and the statement seems to be fine and all variables' value were correct. However, when I run the SQL statement manually against the database, it failed. Yup, amount is actually a field in the order table. I have no idea what is going on because I can do a select using the shipping field which is of float type but not with the amount field.