How to get the last inserted record on a table with pdo_mysql?

PHP
1

I can’t find any information on how to get the last inserted record with pdo_mysql.

The phpdelusion site references using [PDO::lastInsertId] but it doesn’t give any info for newbies like me on how this is accomplished.

  1. Do I add [PDO::lastInsertId] to my existing options?
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];
  1. How do I reference this in a php code query? Would this code work without the above being added to options?
$id = $pdo->lastInsertId();

Note: Since I’m using PHP to run the query, I thought this should belong in the PHP forum. If I am wrong, please let me know and I will move it to the Database forum.

2

Beware the returned value is a string, Maybe with reading some of the comments on the following link from the manual:

https://www.php.net/manual/en/pdo.lastinsertid.php

3

The manual was of no help.

The example I used was from the manual.

I tried it in my code, and the $id is null.

I do want a string result, so that is fine.

Do I have do something with the PDO::lastInsertId ?

4

Try this example from user’s comment #22

<?php
declare(strict_types=1);


// in case anyone was wondering something like

$val = 5;
$sql = "REPLACE table (column) VALUES (:val)";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':val', $val, PDO::PARAM_INT);
$stmt->execute();
$lastId = $dbh->lastInsertId();

// will return the last inserted id, whether the record was replaced or simply inserted

// the REPLACE syntax, simply inserts, or deletes > inserts
// so lastInsertId() still works

// refer to // http://mysql.com/doc/refman/5.0/en/replace.html for REPLACE usage
5

Show us that code, so we can see whether there’s a problem with it, if the response from @John_Betong hasn’t clarified it.

Do you have an auto-incrementing column in your database table?

6

My primary key is set for auto increment.

Here’s my current code:

       $sql_add = "INSERT INTO tableSets (
            sets_nameunique, 
            sets_tabname,
            ) values (
            :setsNameUnique,
            :setsTabName,
       );";

       $pdo->prepare($sql_add)->execute([ 
            'setsNameUnique'=>addslashes($add_newNameUnique),
            'setsTabName'=>addslashes($add_newTabName),
       ]);
            
       $lastSetId = $pdo->lastInsertId();
7

I have over 20 sets of data, that each would require a separate bind parameters line.

I’m only looking for data that has been added, and not for data that has been modified.

Surely there is a way to make this work with my existing code?

I’ll do the work to change to bind parameters if I have to, but if feels like there should be an easier way.

8

I don’t know, I tend to not lump all the statements in together. Does your query actually execute?

In the first code, you’re not including the : at the start of each token name.

Not if you build an indexed array containing the parameter name and value, and then pass that array into the execute - as you do above, but with the full parameter including the opening colon.

Not sure what you mean by that, can you clarify?

9

My existing code works just fine… just can’t get last id.

I now tried the change to the bind parameters method, but it errors out:

Fatal error: Call to a member function prepare() on a non-object

10

Split the code down. I would:

$prep = $pdo->prepare($sql_add);

$params = array();
$params[':setsNameUnique']  = addslashes($add_newNameUnique);
$params[':setsTabName'] = addslashes($add_newTabName);

$prep->execute($params);
echo $pdo->lastInsertId();

ETA - not sure if you still need addslashes and the like with prepared statements.

11

The comments for the bind parameters method indicate this:
// will return the last inserted id, whether the record was replaced or simply inserted

In my case, I am only looking for a record that was inserted.

(I’m not sure if this comment meant that the bind parameters method will do both, or whether it is the lastInsertId that does both).

12

Your query only inserts a new row, it doesn’t do any replacing. I can’t say I’ve ever tried looking at the insert-id after a REPLACE query, I’ve probably just presumed it won’t return anything because nothing is being inserted. At that point the “rows affected” count might be more appropriate, as that reflects any rows that changed as a result of the query.

Bind-Parameters would work with any type of query that has replaceable parameters, whether its inserting, replacing or just retrieving data.

I’m pretty sure that comment is directed at the use of lastInsertId() rather than bind-params.

13

Well, I give up for the evening. I’ll try it again in the morning.

I tried this code. It works for inserting, but still no value from lastInsertId.

$sql_add = "INSERT INTO tableSets (sets_nameunique, sets_tabname) values (:setsNameUnique, :setsTabName);";
$prep = $pdo->prepare($sql_add);
$params = array();
$params[':setsNameUnique'] = addslashes($add_newNameUnique);
$params[':setsTabName'] = addslashes($add_newTabName);
$prep->execute($params);

$lastSetId = $pdo->lastInsertId();
14

I guess the next question must be what PDO driver are you using? There are notes on the doc page that not all PDO drivers support lastInsertId(), so maybe you’re using one that doesn’t.

15

How would I find out?

I’m assuming that is something my shared hosting service provider would have installed on the server?

16

Yes, I wasn’t sure whether you were running on your own development server, or somewhere else.

No idea. You could post the PDO-related information from phpinfo() here, and see if someone recognises the information.

17 
echo $pdo->lastInsertId();
echo $pdo->errorCode();

EDIT: Clarity of position.

18

Here’s what that outputs:

000000

19

Then the driver thinks it correctly returned the lastInsertId. Time to go full debug.

$sql_add = "INSERT INTO tableSets (sets_nameunique, sets_tabname) values (:setsNameUnique, :setsTabName);";
$prep = $pdo->prepare($sql_add);
$params = array();
$params[':setsNameUnique'] = addslashes($add_newNameUnique);
$params[':setsTabName'] = addslashes($add_newTabName);
$prep->execute($params);
echo $pdo->errorCode();
$lastSetId = $pdo->lastInsertId();
echo $pdo->errorCode();
echo gettype($lastSetId);
$prep = $pdo->prepare("DESCRIBE tableSets");
$prep->execute();
print_r($prep->fetchAll());
20

Here is the result of that:

0000000000stringArray ( [0] => Array ( [Field] => sets_id [Type] => int(11) [Null] => NO [Key] => PRI [Default] => [Extra] => auto_increment ) [1] => Array ( [Field] => sets_nameunique [Type] => varchar(150) [Null] => YES [Key] => [Default] => [Extra] => ) [2] => Array ( [Field] => sets_tabname [Type] => varchar(80) [Null] => YES [Key] => [Default] => [Extra] => ))