194

I have a query, and I want to get the last ID inserted. The field ID is the primary key and auto incrementing.

I know that I have to use this statement:

LAST_INSERT_ID()

That statement works with a query like this:

$query = "INSERT INTO `cell-place` (ID) VALUES (LAST_INSERT_ID())";

But if I want to get the ID using this statement:

$ID = LAST_INSERT_ID();

I get this error:

Fatal error: Call to undefined function LAST_INSERT_ID()

What am I doing wrong?

William Kinaan
  • 28,059
  • 20
  • 85
  • 118

3 Answers3

445

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();
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Corbin
  • 33,060
  • 6
  • 68
  • 78
  • 1
    yes you right , i found it and it works ,thank you , i will accept answer – William Kinaan May 21 '12 at 07:21
  • For those interested: `lastInsertId` is [`camelCase`](https://en.wikipedia.org/wiki/CamelCase) and `LAST_INSERT_ID` is [`snake_case`](https://en.wikipedia.org/wiki/Snake_case). Unfortunately, PHP doesn't stick to either naming convention. – rybo111 Jun 16 '15 at 10:28
  • 7
    @rybo111, first it's `Screaming snake case`. second, it's MySQL naming convention and is not PHP – azerafati Jul 23 '15 at 11:56
  • @Bludream Yes, `SCREAMING_SNAKE_CASE` is mentioned on that Wiki link. `lastInsertId` is a PHP Data Object function. PHP uses both `camelCase` (`lastInsertId`) and `snake_case` (`str_replace`). – rybo111 Jul 23 '15 at 12:08
  • 3
    @rybo111, hmm `SELECT LAST_INSERT_ID()` is a MySQL function – azerafati Jul 23 '15 at 12:48
  • @Bludream never said it wasn't ;) – rybo111 Jul 23 '15 at 12:49
  • 48
    So perhaps this is a dumb follow up question but I just want to be certain. When I rely on lastInsertId() does it return the last inserted ID that happened within my currently executing code right then? Or does it return the last inserted ID from across my database? For example, if I have a heavily trafficked website and I insert something during my login script and use lastInsertId() to get the inserted iD, but a lot of people are logging in at the same time, am I safe on relying on lastInsertId() to get the last inserted ID from that specific instance of code executing? Or do I run a risk – Art Geigel Nov 18 '15 at 19:02
  • 56
    @ArtGeigel It will be the last inserted ID of the connection underlying the PDO instance. In other words, it's safe in the scenario you described since concurrent queries would take place in separate connections. – Corbin Nov 19 '15 at 06:15
  • When working with PDO, does executing an insert, followed by a SELECT query that has nothing to do with the insert, then finally a ```pdo::lastInsertId()``` affect the last insert id value returned? I noticed that I get 0 back when doing that extra SELECT query before calling ```lastInsertId()```. Running ```pdo::query('SELECT LAST_INSERT_ID()')``` though will still give me the expected last inserted id value. – georaldc Jun 16 '16 at 20:28
26

lastInsertId() only work after the INSERT query.

Correct:

$stmt = $this->conn->prepare("INSERT INTO users(userName,userEmail,userPass) 
                              VALUES(?,?,?);");
$sonuc = $stmt->execute([$username,$email,$pass]);
$LAST_ID = $this->conn->lastInsertId();

Incorrect:

$stmt = $this->conn->prepare("SELECT * FROM users");
$sonuc = $stmt->execute();
$LAST_ID = $this->conn->lastInsertId(); //always return string(1)=0
Michiel
  • 1,713
  • 3
  • 16
  • 34
Ayhan Kesicioglu
  • 460
  • 5
  • 10
-5

You can get the id of the last transaction by running lastInsertId() method on the connection object($conn).

Like this $lid = $conn->lastInsertId();

Please check out the docs https://www.php.net/manual/en/language.oop5.basic.php

Kanton Samad
  • 23