Cannot perform correct update

renesis

Right, I now I'm newbie, at least I try to get better.
Assume I have 2 tables, orders(orderNumber, BuyerID, ...) and status(orderNumber, status). The first table works good when I want to check the orders. Now, I need to track the orders status. That's why I created the 2nd table. The problem is that when I update status (pending or delivered), ALL my orders get the same value. I mean, I cannot update the status for just one product. When one order status is updated to e.g "delivered", all orders status get the same value.
Any ideas? Probably the problem is that I do not put any "WHERE orderNumber=..." but I don't know how to tell MySQL that the orderNumber for both tables is the SAME!
Thanks in advance.

rincewind456

You should be creating the records in both tables at the same time, the order is pending as soon as the order is placed, so the default value for the status should be pending, if you do it this way you can use mysql_insert_id to get the last insert number like this

$sql = "INSERT INTO orders VALUES('',)"; //the rest of your query here

if (!mysql_query($sql, $connection)) die('Error inserting to database:' .  mysql_error() .'<br>Please contact the system administrator.');

$id = mysql_insert_id();  //$id is now your autoincrement number for the status table

$sql = "INSERT INTO status ('orderNumber') VALUES('$id')"; //id of this table cannot be auto

if (!mysql_query($sql, $connection)) die('Error inserting to database:' .  mysql_error() .'<br>Please contact the system administrator.');

Then when you come to altering the tables you will know that the id numbers are the same across both tables.