PDO get the last ID inserted

Question

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?

Solution 1

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();

Solution 2

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

Comments

[William Kinaan]

yes you right , i found it and it works ,thank you , i will accept answer

[rybo111]

For those interested: lastInsertId is camelCase and LAST_INSERT_ID is snake_case. Unfortunately, PHP doesn't stick to either naming convention.

[azerafati]

@rybo111, first it's Screaming snake case. second, it's MySQL naming convention and is not PHP

[rybo111]

@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).

[azerafati]

@rybo111, hmm SELECT LAST_INSERT_ID() is a MySQL function

[rybo111]

@Bludream never said it wasn't ;)

[Art Geigel]

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

[Corbin]

@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.

[georaldc]

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.

[Your Common Sense]

Welcome to Stack Overflow. We value useful contributions but please do not duplicate existing answers.