Is it possible to retrieve values set by an Update statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Is it possible to retrieve values set by an Update statement

Post by TipPro »

Basically all I am trying to do is add 1 to a field and then retrieve what the new value is... but multiple programs (not written by me) are using the same database so I am worried about race conditions.

- - - - - - - -

Is it possible to retrieve values set by an Update statement?

For example...

Code: Select all

$query = "UPDATE inventory_main SET inventoryNumber = inventoryNumber + 1 WHERE inventoryId = 7";
$dbResults = $db->query($query);
Is it possible to know what inventoryNumber is without running a Select statement after the Update statement? I am worried about race conditions since several users may update inventoryNumber in between the time I run the Update statement and the Select statements.

- - - - - - - -

I did write some code that I believe will work and prevent race conditions, but there probably is a much simpler way to go about this.

$success = false;

Code: Select all

while(!$success){
 
    $query = "SELECT inventoryNumber FROM inventory_main WHERE inventoryId = 7";
    $dbResults = $db->query($query);
    $row = $dbResults->fetch_assoc();
 
    echo "Inventory Number: ".$row['inventoryNumber']."<br />";
    $newFieldValue = $row['inventoryNumber'] + 1;
 
    echo "Attempt: ";
    $query = "UPDATE inventory_main SET inventoryNumber = IF (inventoryNumber = ".$row['inventoryNumber'].", inventoryNumber + 1, inventoryNumber) WHERE inventoryId = 7 LIMIT 1";
    $dbResults = $db->query($query);
    if($db->affected_rows == 1) $success = true;
}
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Is it possible to retrieve values set by an Update statement

Post by Eran »

Strictly speaking, no, you can't have an update statement return a value. An UPDATE updates, A SELECT selects (I know, it's crazy!)
There are two ways you can deal with your requirements -
1. Use a SELECT ... FOR UPDATE query before the update (this will lock the row until after the update statement has completed). http://dev.mysql.com/doc/refman/5.0/en/ ... reads.html
2. Run both queries as part of a transaction http://dev.mysql.com/doc/refman/5.0/en/commit.html
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Re: Is it possible to retrieve values set by an Update statement

Post by TipPro »

Thanks for the reply pytrin. The SELECT... FOR UPDATE reference page was helpful. I am able to do what I need with this one statement.

Code: Select all

 
$query = "UPDATE inventory_main SET inventoryNumber = LAST_INSERT_ID(inventoryNumber + 1) WHERE inventoryId = 7";
$dbResults = $db->query($query);
$newValue = $db->insert_id;
 
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Re: Is it possible to retrieve values set by an Update statement

Post by TipPro »

I have a couple quick questions about my code in my last post. I want to make sure I have no threat of a race condition.

Does $db->insert_id query the database again?

Does it matter how much time is between the time I called my first statement to update a record and the $db->insert_id?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Is it possible to retrieve values set by an Update statement

Post by Eran »

Well, you're using some abstraction library that you didn't disclose so it's hard to tell without knowing what $db is.
Instinctively I would say that insert_id is available from the moment you execute your query. You should take a look at the source of the library you are using and verify if it's critical.
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Re: Is it possible to retrieve values set by an Update statement

Post by TipPro »

Sorry I should have included more detail. I am using the MySQLi class.

Code: Select all

$db = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
The reference page, http://us.php.net/manual/en/mysqli.insert-id.php for ->insert_id makes me believe that this value is preset from the last query that was performed.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Is it possible to retrieve values set by an Update statement

Post by Eran »

In that case, yes :)
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Re: Is it possible to retrieve values set by an Update statement

Post by TipPro »

Thank you so much for your help. I really appreciate the time you took to answer my questions.
Post Reply