UPDATE sql query and get the updated field in one single query

Question

I'm trying to update a value of a column using the codeigniter query function like this:

    $this->db->query("UPDATE table SET val = val + 1 WHERE name = 'xxxxx');

Is there any way to get the result of this update in the same query function? I have to do a select query in order to do it and it's dangerous because of the amount of users this application is managing.

If there is another query in between the update and the select, the result would not be correct.

Thanks!

mysql How can i use transactions in a codeigniter db->query function? It fails when I try it. — mcamara
– mcamara, Commented May 15, 2013 at 14:51
Sounds like it is some kind of counter for the row with name=xxxx, so an autoincrement doesn't look like it would help? — Nanne
– Nanne, Commented May 15, 2013 at 14:51

Benubird · Accepted Answer · 2013-05-15 14:53:31Z

1

Use transaction and for update. This is an example from zend, which is a similar kind of db accessing thing:

$db->beginTransaction();
$val = $db->select()->forUpdate()->from('table', 'val')->orderBy('val DESC')->limit(1)->query()->fetchColumn();
$db->update('table', 'val = '.($val+1), 'name = "xxx"');
$db->commit()

The for-update with the transaction prevents another query interfering.

Learn more about for update here: http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

and about codeigniter transactions here: http://ellislab.com/codeigniter/user-guide/database/transactions.html (thanks to @Nanne for that)

answered May 15, 2013 at 14:53

Benubird

19.7k28 gold badges97 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

UPDATE sql query and get the updated field in one single query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related