PDO LastInsertId returning nothing

Question

For some reason I cannot get the LastInsertId from my PDO insert. I am receiving nothing back, if I place it inside the execute I get -1 because the insert query hasn't run. However when just after the execute to grab the last ID inserted nothing is returned.

php 5.1.6
PECL pdo = 0.1.0

I have looked at following questions and many other stack exchange questions.

However the difference is nothing is returned compared to 0. No errors are recorded either. See the code below.

Connection

try {
  $conn = new PDO("pgsql:host=localhost port= dbname=", "", "");
  echo "PDO connection object created";
}
catch(PDOException $e) {
  echo $e->getMessage();
}

insert / return the last id

$stmt = $conn ->prepare("INSERT INTO sheet_tbl (site_id,  username, additionalvolunteers) VALUES(?,?,?)"); 
$stmt->bindParam(1,$site_id);
$stmt->bindParam(2,$username1);
$stmt->bindParam(3,$additionalvolunteers);
$site_id = $_POST['site_id'];   
$username1 = $user->name;
$additionalvolunteers = $_POST['additionalvolunteers'];
$stmt ->execute();
$newsheetID = $conn->lastInsertId('sheet_id');
echo $newsheetID . "last id";

Hi yes, the row inserts currently at row 23 but sheet_id = 115 @YourCommonSense — Tom, Sep 09 '13 at 12:52
Possible duplicate of [Get last insert id after a prepared insert with PDO](http://stackoverflow.com/questions/5057954/get-last-insert-id-after-a-prepared-insert-with-pdo) — Alastair Irvine, Aug 04 '16 at 07:34

score 1 · Answer 1 · edited May 26 '15 at 13:42

1

You should be using

$newsheetID = $conn->lastInsertId('sheet_id_**seq**');

Just add _seq after id.

edited May 26 '15 at 13:42

Deepend

3,673
15
51
93

answered May 26 '15 at 12:57

Ant

21
4

score 0 · Answer 2 · answered Sep 09 '13 at 20:46

0

Assumming sheet_id type is SERIAL you can use LASTVAL()

TRY (Not tested as I dont have postgresql)

$newsheetID = $conn->query("SELECT LASTVAL()");
echo $newsheetID . "last id";

answered Sep 09 '13 at 20:46

david strachan

6,992
2
21
33

score 0 · Answer 3 · answered Feb 17 '17 at 16:53

I use postgresql in C# with the "RETURNING rowkey" and its has always worked for me.
I also use pdo/php in a lot of applications.
However i dont have a postgres database open to the server right now for testing.

This could should work but it is not tested.

$site_id = $_POST['site_id'];   
$username1 = $user->name;
$additionalvolunteers = $_POST['additionalvolunteers'];

$stmt = $conn ->prepare("INSERT INTO sheet_tbl (site_id,  username, additionalvolunteers) VALUES(:site_id, :username1, :additionalvolunteers) RETURNING row_id"); 

$success = $stmt->execute([
    'site_id' => $site_id,
    'username1' => $username1,
    'additionalvolunteers' =>  $additionalvolunteers
]);
// unsure about this
$newsheetID =  $stmt->fetchColumn();
echo $newsheetID . " last id";

PDO LastInsertId returning nothing

3 Answers3