PDO: Get The ID Of The Last Inserted Row.

This is a short guide on how to get the ID of the last inserted row using PDO’s lastInsertIdmethod. In case you didn’t already know, PDO is a popular PHP extension that provides an interface for accessing databases.

There are various reasons why you might need to retrieve the ID of the row that you just inserted. For example, if a user creates a new blog post, then you might want to redirect them to that blog post after it has been successfully inserted into the database.

For this example, I have created a simple MySQL table called posts. The structure of this table looks like so:

1 2 3 4 5 6 7

CREATE TABLE IF NOT EXISTS `posts` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,   `body` text COLLATE utf8_unicode_ci NOT NULL,   `date_posted` datetime NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

As you can see, it’s nothing special.

In the following example, I will insert some data into this table before retrieving the ID of the last inserted row:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

//Connect to MySQL and instantiate our PDO object. $pdo = new PDO("mysql:host=$dbServer;dbname=$dbName", $dbUser, $dbPassword);   //Create our SQL statement, which inserts data into //our posts table. $insertSql = "     INSERT INTO `posts`       (`title`, `body`, `date_posted`)     VALUES       (:title, :body, :date_posted) ";   //Prepare our SQL statement. $statement = $pdo->prepare($insertSql);   //Execute the SQL statement. $statement->execute(array(     "title" => 'Test post!',     "body" => 'This is just a test!',     "date_posted" => date("Y-m-d H:i:s") ));   //Get the ID of the last inserted row by using //the PDO::lastInsertId method. $id = $pdo->lastInsertId();   //Print out the result for example purposes. echo 'The ID of the last inserted row was: ' . $id;

In the PHP code sample above, we:

1. Connected to MySQL using the PDO extension.
2. We inserted a new row into our table using prepared statements.
3. We retrieved the ID of the last inserted row by using PDO’s lastInsertId method.
4. Finally, we printed out the ID for example purposes.

Using lastInsertId with transactions.

If you are using database transactions with the PDO extension, be warned that you will need to call the lastInsertId method BEFORE you commit the changes. If you attempt to get the ID of the last inserted row after you have committed the changes, then you will receive the value ‘0’.

Here is how to use lastInsertId with transactions:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

try{     $pdo->beginTransaction();     $insertSql = "         INSERT INTO `posts`           (`title`, `body`, `date_posted`)         VALUES           (:title, :body, :date_posted)     ";     $statement = $pdo->prepare($insertSql);     $statement->execute(array(         "title" => 'Transaction example.',         "body" => 'Inserting this inside a transaction!',         "date_posted" => date("Y-m-d H:i:s")     ));     $id = $pdo->lastInsertId();     $pdo->commit();     echo 'The ID of the last inserted row was: ' . $id; } catch(Exception $e){     echo $e->getMessage();     $pdo->rollBack(); }

As you can see, we called the lastInsertId method before we committed our changes. If you change the code above and call lastInsertId after you have committed the changes, then the result will be as follows:

The ID of the last inserted row was: 0