CODEWITHSUNDEEP
phpmysqlpdo
Jonathan H
Jonathan H

Reputation: 7962

PHP PDO::lastInsertId() returns 0

Thanks in advance for reading this, I couldn't find an answer that solved my problem... I don't understand what I'm doing differently than the tutorials/suggestions I found:

SQL Table

CREATE TABLE IF NOT EXISTS `LastInsertID` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(150) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

PHP File

<?php

// Connect to database
$user = "foo";
$pswd = "bar";

$db = new PDO( 'mysql:host=localhost;dbname=test', $user, $pswd );
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Prepare request
$rq = $db->prepare('INSERT INTO `LastInsertID` VALUES(NULL,:name,:email)');

// Begin and commit request
$db->beginTransaction();
$values = array('name'=>'Foo','email'=>'[email protected]');
$rq->execute($values);
$db->commit();

// Echo last ID
echo $db->lastInsertId();

?>

This returns 0 when it should return 6. Where is the problem?

Upvotes: 1

Views: 4539

Answers (2)

Mike
Mike

Reputation: 923

You must use $db->lastInsertId() before you commit if you are in a transaction. Even if you rollback a transaction, the id is "used" or skipped, which is why you should not depend on IDs to be sequential.

Upvotes: 6

Yogus
Yogus

Reputation: 2272

Use this 

INSERT INTO `LastInsertID` (name, email) VALUES(:name,:email)

instead

INSERT INTO `LastInsertID` VALUES(NULL,:name,:email)

I have removed the NULL.

Upvotes: 0

Related Questions