Pdo Last Insert Id Always the Right One

PDO Last Insert ID always the right one?

No, this situation is impossible.
Method $db->lastInsertId() returns last inserted id for this DB conection. In other page will be another connection and another last inserted id.

PDO lastInsertId() always return 0

Other than a bug in php/PDO or your framework, there are two possibilities. Either lastInsertId() is called on a different MySQL connection than the insert, or you are generating the id in your application/framework and inserting it, rather than letting auto_increment generate it for you. Which column in the table is the primary key/auto_increment? Is that column included in $attributes in your create() function?

You can test PDO to make sure that part is working correctly with this code (in a new file):

// Replace the database connection information, username and password with your own.
$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'user', 'password');

$conn->exec('CREATE TABLE testIncrement ' .
            '(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50))');
$sth = $conn->prepare('INSERT INTO testIncrement (name) VALUES (:name)');
$sth->execute([':name' => 'foo']);
var_dump($conn->lastInsertId());
$conn->exec('DROP TABLE testIncrement');

When I ran this script, the output was

string(1) "1"

PDO get the last ID inserted

That's because that's an SQL function, not PHP. You can use PDO::lastInsertId().

Like:

$stmt = $db->prepare("...");
$stmt->execute();
$id = $db->lastInsertId();

If you want to do it with SQL instead of the PDO API, you would do it like a normal select query:

$stmt = $db->query("SELECT LAST_INSERT_ID()");
$lastId = $stmt->fetchColumn();

PDO + PHP lastInsertId() issue

To counteract this you would use a transaction.

This would essentially isolate your insert from others, so as long as your Insert/lastInsertId() call is within the same transaction, it will work just fine.

Any chance PDO will return a wrong last inserted ID with a static/singleton shared database object?

If you indeed have your code checkered in a single script like in your example - getting insert id for query A after running query B - there surely would be inconsistency.

Just get your insert id the very next row after insert, and you will have not a single problem with it.

HTTP and singleton has nothing to do here.

Here is a rule of thumb for such phobias: consider if your case is unique or not. For the inconsistent autoincrement consider these premises:

• such inconsistency surely would be a disaster
• singleton is a popular pattern
• HTTP is quite a popular protocol too
• you are not a sole programmer who are using these technologies

and you can make a conclusion: as long as you cannot find any evidence on such a disaster - most likely it never existed.

Sincerely yours, col. Common Sense.

PDO - lastInsertId() for insert query with multiple rows

It's not possible. If you need generated ids for both rows - you need to perform 2 separated INSERT

Important If you insert multiple rows using a single INSERT statement,
LAST_INSERT_ID() returns the value generated for the first inserted
row only. The reason for this is to make it possible to reproduce
easily the same INSERT statement against some other server.

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

MySQL and PDO: Could PDO::lastInsertId theoretically fail?

It will always be safe provided that the PDO implementation is not doing something really bone-headed. The following is from the MySQL information on last_insert_id:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

Retrieving last inserted iD then uploading to MySQL. #PDO #PHP

It can be used right after you successfully execute an INSERT statement. However, you have to always check whether the execution was successful (or are there affected rows) because there can be last insert ID from previously executed statement.

$sth = $this->db->prepare("INSERT INTO posts ( message, uid_fk, ip, created) VALUES ( :update, :id, :ip, :time)");
$sth->execute(array(':update' => $update, ':id' => $uiD, ':ip' => $_SERVER['REMOTE_ADDR'], ':time' => time()));

$this->db->lastInsertId(); //Upon success, is available

For example:
You have a function that creates a new post. This function on success can return the last insert ID value, so you can display that value to user. Like:

if (isset($_GET['id'])){
  $id = $manager->createPost($_GET['id']) ;
  if (is_numeric($id)){
    echo "Your article has been created. ID: {$id}" ; //Or link to it.
  }
}

Update:

Here is my suggestion to your problem: lastInsertId always retuns 0:

Your table, where you insert values, must have PRIMARY KEY AUTO_INCREMENT field, preferrably with type int.

Last insert id value store to same table another specific column

In MYSQL, you have alternative possibility to find it, when you think last_insert_id() is not working. You may require to have SELECT privilege on INFORMATION_SCHEMA and its tables.

If you have that privileges, try the following query.

$query = "insert into users( name, email, pasword, status, identity )"
         . " values( '$name', '$email', '$password', '1',"
         . " ( SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES"
         . "   WHERE TABLE_NAME='users' and TABLE_SCHEMA=DATABASE() )"
         . " )";

---

And, lastly, suggesting to stop using deprecated API.

---

Related Topics