How to do a Select and an Update in a single command with pdo?


I need to recover and increment a counter, which is not a self-incremental id in a table, and avoid concurrency errors. I do not want to do table locks because of its high cost in resources.

I have been viewing this article ( link ) and I've tried the part that says "Select and Update in Single Statement" and it works perfect for me in MariaDB.

I've been trying to implement it with PDO and I do not succeed.

This is my function:

$dbConnection = new \PDO('mysql:host='.$host.';dbname='.$dbal_dbname.';charset=utf8', $dbal_user, $dbal_password, [\PDO::ATTR_PERSISTENT => true, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);    
$stmt = $this->dbConnection->prepare('UPDATE config SET config_value = (@cur_value := config_value) + 1 WHERE config_name = "proximo_asiento"');
$stmt = $this->dbConnection->prepare('SELECT @cur_value;');
$returned_field = $stmt->execute();

/* Tambien he probado sustitutendo la ultima linea con: */

$returned_field = $stmt->fetch(\PDO::FETCH_ASSOC);

The table counter, that is, the config_value field corresponding to the config_name of "next_seat" is increased well but the result, the returned_field, is always 1 even if the field "next_seat is 235".

The connection, the PDO commands, ... are checked.

I think the problem is in how I retrieve the last SQL. But I can not find where it is wrong.

A little help ... thanks.

asked by Carlos 25.08.2018 в 21:23

0 answers