PDO::lastInsertID sometimes doesnt work

Question

Something strange is happening with my pdo connection when I get the lastInsertId(): in 90% times it work well, but in 10% of the cases it returns 0 (I don't know if is really 0 or FALSE). But in 100% of cases my records are successfully inserted.

Here is my code:

function execute_insert($sql, $values) {
    try {
        $dbConnection = new PDO('mysql:dbname=mydb;host=localhost;charset=utf8', 'username', 'mypass');

        $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $dbConnection->beginTransaction();
        $stmt = $dbConnection->prepare($sql);

        $stmt->execute($values);

        $i = $dbConnection->lastInsertId();

        $dbConnection->commit();
        return $i;

    } catch (PDOException $e) {
        echo "erro :S - " . $e->getMessage();
        die();
    }
}


//blablabla

$sql = "INSERT INTO ai_pessoas(nome, tipo, cpf, orgao_emissor_id,
    rg_uf, rg, telefone1, telefone2, celular1, cidade_id, cep, endereco,
    numero,complemento,bairro, email_principal, email_secundario, email_principal_confirmado,
    dt_nascimento, oferta_id) "; 

$sql .= "VALUES(:nome,1,:cpf,:orgaorg,:ufrg,:rg,:telefone,:outroTelefone,:celular,:cidade,:cep,:endereco,:numero,:complemento,:bairro,:email,:emailAlternativo,FALSE,:datanasc,:idOfertaAtual);";

$idPessoa = execute_insert($sql,
        array(
            ':nome' => $nome,
            ':cpf' => $cpf,
            ':orgaorg' => $orgaorg,
            ':ufrg' => $ufrg,
            ':rg' => $rg,
            ':telefone' => $telefone,
            ':outroTelefone' => $outroTelefone,
            ':celular' => $celular,
            ':cidade' => $cidade,
            ':cep' => $cep,
            ':endereco' => $endereco,
            ':numero' => $numero,
            ':complemento' => $complemento,
            ':bairro' => $bairro,
            ':email' => $email,
            ':emailAlternativo' => $emailAlternativo,
            ':datanasc' => $datanasc,
            ':idOfertaAtual' => $idOfertaAtual
));

if ($idPessoa > 0){
   //sometimes enters here, sometimes not
}

My records are aways inserted, but the "if ($idPessoa > 0)" sometimes doest work meaning that $idPessoa is 0 or FALSE.

Any clue?

Thanks a lot!

sectus · Answer 1 · 2013-05-17T00:36:11.337

0

You can try LAST_INSERT_ID() for this purpose.

P.S. Your function connects every time.

P.P.S. Also i see that you do not handle errors and rollback. I think that in your 10% insert are failed. Explore about double calling this function because you have UNIQUE index on some field. And add check result of execute and errorInfo.

edited May 17 '13 at 00:36

answered May 17 '13 at 00:26

sectus

15,605
5
55
97

Hello, thanks for you answer. I'll try this and if it works I'll let you know. – pzz May 20 '13 at 15:37
PS: It's a simple web page, so I use this connection only in 2 places, in distinct occasions, but I'll fix it in future. PPS: I believe that isn't failing, because this script have more lines after the end of `if ($idPessoa > 0){...}` and they are executed. i.e.: the `die()` command never execute... (at least this code never raised any `PDOException`). – pzz May 20 '13 at 15:45

PDO::lastInsertID sometimes doesnt work

1 Answers1