Last Insert ID is not returning

Question

I have following code for inserting data into database using PDO.

It inserts data into database but not return last inserted in ID.

here userid is primary key

  try {
        $dbh = new PDO('mysql:host=localhost;dbname=crud_demo', "username", "password");

        $sqlQuery = "INSERT INTO users(userid,first_name,last_name,email,password)
            VALUES(:userid,:first_name,:last_name,:email,:password)";

        $statement = $dbh->prepare($sqlQuery);
        $bind = array(
            ":userid" => "bhavik",
            ":first_name" => "Bhavik",
            ":last_name" => "Patel",
            ":email" => "bhavitk@live.in",
            ":password" => "1234567"
        );
        $statement->execute($bind);
        echo $dbh->lastInsertId();
    } catch (PDOException $e) {
        echo $e->getMessage();
    }

$dbh->lastInsertId(); always return 0 whatever i insert value for userid

possible duplicate of [Unable get Last Insert Id](http://stackoverflow.com/questions/11156133/unable-get-last-insert-id) — nickb, Jun 22 '12 at 13:09
You are inserting your own value for your ID, so why do you need to get it back? You already know what it is, right? — Wiseguy, Jun 22 '12 at 13:11
@nickb it was also my question but there some problem so i asked again — Chetan Patel, Jun 22 '12 at 13:14
if your primary key is varchar, see [this](http://stackoverflow.com/questions/8923366/getting-last-record-from-mysql). I also had same issue. — Fahim Parkar, Jun 22 '12 at 13:38

score 10 · Accepted Answer · answered Jun 22 '12 at 13:11

10

lastInsertId() only returns IDs automatically generated in an AUTO_INCREMENT column. Your PRIMARY KEY is apparently a string (or at least you're inserting a string in it). Since you're inserting a known value, you don't actually need to find out the inserted ID — you've specified it yourself.

answered Jun 22 '12 at 13:11

lanzz

42,060
10
89
98

1

+1. `lastInsertId()` accept a `$name` parameter. Is it possible to specify `userid` if it's an integer type ? (Never tried, just thought about it) – zessx Jun 22 '12 at 13:15
1

`lastInsertId($name)` is used for databases like Postgresql. There is no `AUTO_INCREMENT` column modifier there, but you have sequences that implement this functionality, and as a side effect you can have multiple incremental values in the table, and you need to provide the name of the sequence whose last value you want. – lanzz Jun 22 '12 at 13:17

score 5 · Answer 2 · answered Jun 22 '12 at 13:11

5

The userid doesn't have an auto_increment attribute in your database, so lastInsertId() will never return anything useful.

You can only lastInsertId() if your field is:

the primary key
not null
an integer type (int, bigint, etc.)
auto_increment

answered Jun 22 '12 at 13:11

Ja͢ck

170,779
38
263
309

score 0 · Answer 3 · answered Jun 22 '12 at 13:07

0

first you need to check insert query.

if its run proper then use mysql function like mysql_insert_id().

if this function is run property then may be possible to mistake in your $dbh->lastInsertId()

this function.

answered Jun 22 '12 at 13:07

user1107107

44
2

Asker is using the PDO_MySQL extension, not ext/mysql. ext/mysql is [not recommended](http://us3.php.net/manual/en/mysqlinfo.api.choosing.php) for future development. And, [`lastInsertId()`](http://us.php.net/manual/en/pdo.lastinsertid.php) is part of the PDO library, not a user-defined method. – Wiseguy Jun 22 '12 at 13:23

Last Insert ID is not returning

3 Answers3

Linked