Rescuing the last insertion in the bank with lastInsertId PDO

Question

I’m trying to rescue the last id in the bank with lastInsertId() of the PDO, but nothing appears.

OBS: I’m using the Postgre.

<?php
try {

    $dbh = new PDO('pgsql:host=localhost;port=5432;dbname=bancocjweb1', 'postgres', '12345');

    $stmt = $dbh->prepare("INSERT INTO pessoas (sexo, nascimentodata, email, nome, cpf) VALUES(?,?,?,?,?)");

    try {
        $dbh->beginTransaction();
        $stmt->execute(array(
            'm',
            '1987-01-01',
            '[email protected]',
            'teste',
            '05255178910'
        ));

        $dbh->commit();
        echo $dbh->lastInsertId();

    }
    catch (PDOExecption $e) {
        $dbh->rollback();
        print "Error!: " . $e->getMessage() . "</br>";
    }
}
catch (PDOExecption $e) {
    print "Error!: " . $e->getMessage() . "</br>";
}

?> 

Answer 1

A practical way to resume the id inserted with postgres is to use the clause RETURNING just enter the name of the field quence(which is the equivalent of mysql’s auto-increment) or her name, as commented by Craig Ringer.

$stmt = $dbh->prepare("INSERT INTO pessoas (sexo, nascimentodata, email, nome, cpf)
                       VALUES(?,?,?,?,?) RETURNING id ");

$stmt->execute(array('m', '1987-01-01', '[email protected]', 'teste', '05255178910'));
$dbh->commit();
$ultimo_id = $stmt->fetch(PDO::FETCH_ASSOC);

Answer 2

Without replicating your code... Run lastInsertId before commit. It’s a reported case and you can read here in PHP.

try {
    $dbh->beginTransaction();
    $stmt->execute(array( ... ));

    echo $dbh->lastInsertId();
    $dbh->commit();

}