DB Type: MariaDB
Table Engine: InnoDB
I have a table where inside it has a column with a value which is being incremented (not auto, no inserting happens in this table)
When I run the following SQL query in phpMyAdmin it works just fine as it should:
UPDATE `my_table`
SET `my_column` = LAST_INSERT_ID(`my_column` + 1)
WHERE `my_column2` = 'abc';
SELECT LAST_INSERT_ID();
The above returns me the last value for the my_column table when the query happened. This query was taken directly from the mysql docs on locking: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html (to the bottom) and this seems to be the recommended way of working with counters when you don't want it to be affected by other connections.
My PDO:
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE `my_table`
SET `my_column` = LAST_INSERT_ID(`my_column` + 1)
WHERE `my_column2` = 'abc';
SELECT LAST_INSERT_ID();";
// Prepare statement
$stmt = $conn->prepare($sql);
// execute the query
$stmt->execute();
$result = $stmt->fetchColumn(); // causes general error
$result = $stmt->fetch(PDO::FETCH_ASSOC);// causes general error
// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
Exact error SQLSTATE[HY000]: General error, If I remove the lines where I try to get the result, it updates the column, but I still do not have a return result... how do I perform that update query and get the select result all in one go like I do when I run it in phpMyAdmin? This all needs to happen in one go as specified by the MySQL docs so I don't have issues where two connections might get the same counter.
There is no need to perform SELECT LAST_INSERT_ID();. PDO will save that value automatically for you and you can get it out of PDO.
Simply do this:
$conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
$sql = "UPDATE `my_table`
SET `my_column` = LAST_INSERT_ID(`my_column` + 1)
WHERE `my_column2` = 'abc'";
// Prepare statement
$stmt = $conn->prepare($sql);
// execute the query
$stmt->execute();
$newID = $conn->lastInsertId();
lastInsertId() will give you the value of the argument evaluated by LAST_INSERT_ID().