PDO Update and Insert problem - can only insert and not update

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:

    title varchar(128) NOT NULL,
    slug varchar(128) NOT NULL,
    body text NOT NULL,
    PRIMARY KEY (id),
    KEY slug (slug)

The form only has two fields and a submit button…

	<dl class="w88 mga">		
		<dt class="fsl fwb"> Add News Item </dt>
		<dd> &nbsp; </dd>
			<label> Title: </label>	
			<input type="text" 	name='title' value='<?= $title ?>' />
		<dd> &nbsp; </dd>
			<label class="fll"> Body:  &nbsp; </label>	
			<textarea class="fll tal w88"	 rows="10" name='body'><?= $body ?></textarea> 
		<dd class="clb"> &nbsp; </dd>
			<input class="bg2" type="submit" 	name='submit' value='SUBMiT' />
		<dd> &nbsp; </dd>

The following script is just one of the many attempts to apply UPDATES:

	if($submit && isset($_SESSION['NEXT']) ) : 
			$sql 	= "UPDATE `news` SET `title`=?, `body`=?, `slug`=?) WHERE `id`=?";
			$stmt 	= $pdo->prepare($sql);
			$ok 	= $stmt->execute( [$title, $body, $slug, $_SESSION['NEXT'] ] );
		#	vd( $_SESSION['NEXT']);

			$sql 	= "INSERT INTO `news` (`title`, `body`, `slug`) VALUES ( ?,?,?);";
			$stmt 	= $pdo->prepare($sql);
			$ok 	= $stmt->execute( [$title, $body, $slug] );

				$sql  = 'SELECT `id` FROM `news` ORDER BY `id` DESC LIMIT 0,1';
				$stmt = $pdo->query($sql);
				$tmp  = $stmt->fetch()['id']; // 3338
				$_SESSION['NEXT'] = $tmp;
			#	vd( $_SESSION['NEXT'] );
	else :			
	endif; // ($submit) :

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.

1 Like

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.

1 Like

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=';
$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 ] );

I know I’m late to the game, but here’s my take:


$db_options = [
    /* important! use actual prepared statements (default: emulate prepared statements) */
    /* throw exceptions on errors (default: stay silent) */
    /* fetch associative arrays (default: mixed arrays)    */

$pdo = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);

 * Insert data in database (news) table
 * I personally like using named (:title) placeholders instead of
 * positional (?) placeholders as I think it is easier to follow.
$query = 'INSERT INTO news( title, body, slog VALUES ( :title, :body, :slug )';
$stmt = $pdo->prepare($query);
$result = $stmt->execute([':title' => $title, ':body' => $body, ':slug' => $slug]);

 * Update data in database (news) table

$id = 1; // or $id = $pdo->lastInsertId()

$query = 'UPDATE news SET title=:title, body=:body, slug=:slug, WHERE id =:id';
$stmt = $pdo->prepare($query);
$result = $stmt->execute([':title' => $title, ':body' => $body, ':slug' => $slug, ':id' => $id]);
1 Like

Many thanks it was indeed the spurious “)” and as @ahundiak mentioned I was also thrown with the error message.

When developing locally I rely on the php.ini setting of error_reporting and ini_set('display_errors);

Regarding using lastInsertId() there are a vast amount of PHP Manual Notes describing problems with different MySqli Versions.

I originally thought it would be such a simple task and could not understand why UPLOAD was being ignored :frowning:

Also many thanks to @Pepster64 and appreciate the additional error reporting.

Current state of the web-page can be seen here:


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.

1 Like

OK just added lastIndsertId() and also transactions.

I hope every is now happy :slight_smile:

Updates web-page showing source

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.