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 lastInsertId method. 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:

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:

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:

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

Hopefully, you found this tutorial to be somewhat useful!

Facebook Comments