Get last insert id after a prepared insert with PDO
Solution 1
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
Solution 2
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.
Solution 3
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');
Psyche
Updated on May 03, 2020Comments
-
Psyche almost 4 years
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; }
-
Psyche about 13 yearsI tried with the sequence name as an argument for lastInsertId() and it works. Thanks.
-
brianmearns over 10 yearsWhat is the sequence name? The name of the column which contains the autoincremented value?
-
arleslie almost 10 years@sh1ftst0rm sequences are used in Postgres rather then MySQL. MySQL will usually return without a parameter being passed (granted you have a primary key).
-
simno over 7 years
\d tablename
showed meid | integer | not null default nextval('addr_source_id_seq'::regclass)
, I putaddr_source_id_seq
and voila! So in my case it wasn'ttable_column_seq
line.