Page 1 of 1
Two Updates in one file
Posted: Fri Sep 17, 2010 3:16 pm
by marnieg
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?
Re: Two Updates in one file
Posted: Fri Sep 17, 2010 3:42 pm
by Jonah Bron
Just run the same query again on the other table?
Re: Two Updates in one file
Posted: Fri Sep 17, 2010 4:04 pm
by marnieg
The problem is that it is not retrieving the new values. Here is my code.
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);
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.
Re: Two Updates in one file
Posted: Fri Sep 17, 2010 4:17 pm
by Eran
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
Re: Two Updates in one file
Posted: Fri Sep 17, 2010 4:28 pm
by Jonah Bron
The second "WHERE" should be an "AND".
Re: Two Updates in one file
Posted: Fri Sep 17, 2010 4:35 pm
by Eran
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 -
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'
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.
Re: Two Updates in one file
Posted: Mon Sep 20, 2010 10:15 am
by marnieg
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.