search

forrest79 / phpgsql

Simple and fast PHP database library for PostgreSQL with auto converting DB types to PHP and fluent interface for SQL query writing.
Other
11 stars 3 forks source link

method to get last Inserted ID from INSERT query? #26

Closed RonEskinder closed 3 years ago

RonEskinder commented 3 years ago

I have an insert and need to know the last inserted id for that query. Is there a method not documented that i can use for this?

$prepareStatement = $connection->prepareStatement('INSERT INTO "public"."tc_mail"("userid", "mailAddress", "mailSubject", "mailType", "body", "extras") VALUES (?, ?, ?, ?, ?, ?);');
$result = $prepareStatement->executeArgs([$userid, $mailAddress, $subject, $alert, $body, $deviceid]);

$result->getAffectedRows();

This will only show affected rows, but no info about the id

forrest79 commented 3 years ago

Hi, there is no function to determine the new ID. You must use PostgreSQL functionality with a RETURNING (https://www.postgresql.org/docs/12/dml-returning.html), for example:

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

On this query, you can call fetchSingle() method and you will get a new ID as integer.