I’m sure there must be simple solution but everything I tried does not seem to work. I am not able to UPDATE the table and can only INSERT amended data?
I have a very simple table:
CREATE TABLE news (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(128) NOT NULL,
slug varchar(128) NOT NULL,
body text NOT NULL,
PRIMARY KEY (id),
KEY slug (slug)
);
Your UPDATE query has a spurious ) before the WHERE clause, unless that’s a forum typo. If that’s not it, can you explain what happens when you try? Does it hit the correct section of PHP, for example?
I take it your last query is to avoid using lastInsertId()?
I have the same question as @droopsnoot. Why don’t you use lastInsertId()? Actually, that is really no good solution to avoid it. You could have a big problems with data consistence.
I presumed because of the note about not all drivers supporting it. But you do raise a very good point - the original logic falls down as soon as two users try to add a record at virtually the same time - if the INSERT query for the second user runs after the first, but before the first SELECT query, then the first user will get the second users ID in their session variable. Perhaps a transaction could help remove that scenario.
As previously mentioned, you have a spurious ) in your sql.
What was interesting (to me at least) was that the code would run without any errors being generated. Even with error_reporting(E_ALL); You had to check for errors after executing otherwise there would be no indication. Which is a bit of a pain.
This is why setting the error mode to exceptions is so handy. The bad syntax will toss an exception for you. This is also the sort of problem that can be easier to resolve from a console window. I had originally thought that the problem was with storing the id in the session but that turned out to not be the case.
I might add that you absolutely want to use lastInsertId.
Here is some working code:
error_reporting(E_ALL); // Did not help
$dsn = 'mysql:dbname=zoo;host=127.0.0.1';
$user = 'impd';
$password = 'trumpisgoingdown';
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Almost a requirement
$sql = "INSERT INTO `news` (`title`, `body`, `slug`) VALUES ( ?,?,?);";
$stmt = $pdo->prepare($sql);
$ok = $stmt->execute( ['Title 1', 'Body 1', 'Slug 1'] );
$id = 1; // or $id = $pdo->lastInsertId();
// Spurious ) Your IDE should flag this
//ql = "UPDATE `news` SET `title`=?, `body`=?, `slug`=?) WHERE `id`=?";
$sql = "UPDATE `news` SET `title`=?, `body`=?, `slug`=?
WHERE `id`=?";
$stmt = $pdo->prepare($sql);
$ok = $stmt->execute( ['Title 1A', 'Body 1A', 'Slug 1A', $id ] );
Unless you are using some ancient or off the wall database then I think you can pretty much disregard all those notes. The ability to easily and quickly fetch the last inserted id is a core feature of mysql along with it’s autoincrement capability. Has been for the last 20 years or so at least.
Your approach could easily lead to issues. And if you really did not want to deal with the problem at all then switch to a guid base approach.
Most of the user comments are for database types (MSSQL,Postgres) that don’t/didn’t support ‘automatically’ returning a last insert id with the returned information from an insert query, were for cases where the last query executed on the connection wasn’t an insert query (the transactions/commits), were for cases where an insert query didn’t insert a row (you should always test in your code), or were repeating the database server documentation for how the last insert id value works for a multi-value insert.
For a select query to always be able to get the correct highest id value, following an insert query, you would either need to lock the table or use a transaction for the insert/select query pair, in order to prevent other queries from adding their own rows between the two queries, making the current highest id a different value than expected.
