Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get last insert id after a prepared insert with PDO

I'm using PHP PDO with PostgreSQL for a new project.

Given the following function, how can I return the id of the row just inserted? It doesn't work the way it looks now.

function adauga_administrator($detalii) {     global $db;     $ultima_logare = date('Y-m-d');      $stmt = $db->prepare("INSERT INTO site_admins (sa_nume, sa_prenume, sa_user_name, sa_password, sa_email, sa_id_rol, sa_status, sa_ultima_logare) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");     $stmt->bindParam(1, $detalii['nume']);     $stmt->bindParam(2, $detalii['prenume']);     $stmt->bindParam(3, $detalii['username']);     $stmt->bindParam(4, md5(md5($detalii['parola'] . SIGURANTA_PAROLE) . SIGURANTA_PAROLE));     $stmt->bindParam(5, $detalii['email']);     $stmt->bindParam(6, $detalii['rol'], PDO::PARAM_INT);     $stmt->bindParam(7, $detalii['status'], PDO::PARAM_INT);     $stmt->bindParam(8, $ultima_logare);         $stmt->execute();       $id = $db->lastInsertId();     return $id; } 
like image 435
Psyche Avatar asked Feb 20 '11 15:02

Psyche


People also ask

How can I get last insert ID in PDO?

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

How do I get the last inserted ID in SQL?

SELECT @@IDENTITY @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope.

How do I get last inserted data?

you can get the id if you call LAST_INSERT_ID() function immediately after insertion and then you can use it. Show activity on this post. For any last inserted record will be get through mysql_insert_id() If your table contain any AUTO_INCREMENT column it will return that Value.

How can I get last inserted record in MySQL using PHP?

If you use php to connect to mysql you can use mysql_insert_id() to point to last inserted id.


2 Answers

From the Manual:

Returns the ID of the last inserted row, or the last value from a sequence object, depending on the underlying driver. For example, PDO_PGSQL() requires you to specify the name of a sequence object for the name parameter.

It should be something like:

return $db->lastInsertId('yourIdColumn'); 

[EDIT] Update link to doc

like image 185
Alix Axel Avatar answered Sep 29 '22 22:09

Alix Axel


From the PHP manual:

For example, PDO_PGSQL() requires you to specify the name of a sequence object for the name parameter.

You could also use RETURNING in the INSERT-statement and fetch the INSERT-result like a SELECT result.

like image 39
Frank Heikens Avatar answered Sep 29 '22 22:09

Frank Heikens