Get last insert id after a prepared insert with PDO

Question

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;
}

Also call like - `return $db->lastInsertId($stmt); ` – Rohit Suthar Aug 18 '15 at 13:56 — Rohit Suthar, Aug 18 '15 at 13:56

score 60 · Accepted Answer · edited May 02 '18 at 14:22

60

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

edited May 02 '18 at 14:22

Servuc

328
1
4
16

answered Feb 20 '11 at 15:36

Alix Axel

151,645
95
393
500

7

I tried with the sequence name as an argument for lastInsertId() and it works. Thanks. – Psyche Feb 20 '11 at 15:49
6

What is the sequence name? The name of the column which contains the autoincremented value? – brianmearns Aug 08 '13 at 18:53
@sh1ftst0rm sequences are used in Postgres rather then MySQL. MySQL will usually return without a parameter being passed (granted you have a primary key). – arleslie Jun 22 '14 at 05:07
`\d tablename` showed me `id | integer | not null default nextval('addr_source_id_seq'::regclass)`, I put `addr_source_id_seq` and voila! So in my case it wasn't `table_column_seq` line. – simno Sep 08 '16 at 18:04

score 11 · Answer 2 · answered Feb 20 '11 at 15:37

11

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.

answered Feb 20 '11 at 15:37

Frank Heikens

117,544
24
142
135

score 4 · Answer 3 · answered Jul 26 '15 at 14:35

Previous answers are not very clear (PDO doc too)

In postgreSQL, sequences are created when you are using the SERIAL data type.

CREATE TABLE ingredients (
  id         SERIAL PRIMARY KEY,
  name       varchar(255) NOT NULL,
);

So the sequence name will be ingredients_id_seq

$db->lastInsertId('ingredients_id_seq');

Get last insert id after a prepared insert with PDO

3 Answers3

Linked

Related