Two Updates in one file
Moderator: General Moderators
Two Updates in one file
I have a php file that updates data in one of my tables. Then I want to update another table using the new values that were just updated in the other table. How do I retrieve the updates on the first table before updating the data in the second table within the same file?
- Jonah Bron
- DevNet Master
- Posts: 2764
- Joined: Thu Mar 15, 2007 6:28 pm
- Location: Redding, California
Re: Two Updates in one file
Just run the same query again on the other table?
Re: Two Updates in one file
The problem is that it is not retrieving the new values. Here is my code.
The second update is not picking up the new values of price and qty from the previous updates. Is there a statement I must do between the two to get the new values.
Code: Select all
$update = "UPDATE job_inventory_xref SET seq = '$seq', qty = '$qty', price ='$price',
qty_issued = '$qty_issued',status_code = '$status_code', joblocation_code = '$joblocation_code' WHERE id='$id' ";
$rsUpdate = mysql_query($update);
$query3 = "UPDATE job SET inventorycost = (SELECT SUM(price * qty) FROM job_inventory_xref WHERE job.id = job_inventory_xref.job_id) WHERE job.id = '$id'";
$results3 = mysql_query($query3);
Re: Two Updates in one file
The second query is simply not a valid query, so it's not executing. You have two WHERE clauses
Best to check the result of a mysql_query() call to make sure there are no syntax errors
Best to check the result of a mysql_query() call to make sure there are no syntax errors
- Jonah Bron
- DevNet Master
- Posts: 2764
- Joined: Thu Mar 15, 2007 6:28 pm
- Location: Redding, California
Re: Two Updates in one file
The second "WHERE" should be an "AND".
Re: Two Updates in one file
Actually, that's not it, I didn't notice it was a subselect. This is the wrong way to try to write a query like this, you have to specify all involved tables in the UPDATE part and write it like a join -
I'm not sure if using aggregate functions such as SUM() is allowed in this kind of query. If not, you should run a separate select query.
Code: Select all
UPDATE job,job_inventory_xref SET inventorycost = SUM(price * qty) WHERE job.id = job_inventory_xref.job_id AND job.id = '$id'Re: Two Updates in one file
The second update works fine when I execute it in phpmyadmin. The problem is when I execute it within my code right after the other update, it doesn't pick up the new values that I set price and qty to in the xref table.