Two Updates in one file

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Two Updates in one file

Post 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?
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Two Updates in one file

Post by Jonah Bron »

Just run the same query again on the other table?
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Re: Two Updates in one file

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Two Updates in one file

Post 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
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Two Updates in one file

Post by Jonah Bron »

The second "WHERE" should be an "AND".
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Two Updates in one file

Post 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.
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Re: Two Updates in one file

Post 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.
Post Reply