Rescuing the last insert in the database with PDO lastInsertId

7

I'm trying to redeem the last id on the bank with lastInsertId() of the PDO, but nothing appears.

OBS: I'm using 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>";
}

?> 
    
asked by anonymous 02.09.2014 / 07:01

2 answers

3

A practical way to retrive the inserted id with postgres is to use the RETURNING simply enter the field name sequence (which is the equivalent of the auto-increment of mysql) or its 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);
    
02.09.2014 / 13:49
3

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();

}
    
02.09.2014 / 07:13