How to retrieve ID of the last record recorded with MySQLi

Question

How to retrieve ID of the last record recorded with MySQLi

Navigation

#1 by (5 votes)
#2 by (2 votes)
#3 by (1 votes)

6

I am not able to retrieve the ID of the last record inserted in my DB, it is coming as Zero, the inclusion is working and at the moment of making an update the script fails.

I have tried with mysql_insert_id() and mysqli_insert_id()

What I did was this:


$sql = "INSERT INTO WFDocContratacao ( Unidade, Cargo, NumeroVagas, MotivoContratacao, Departamento, TipoVaga, HorarioTrabalho, Jornada, DataAdmissao, ContratoExperiencia, SalarioContratual, Codigo1, SalarioPosExperiencia, Codigo2, Atividade, Aproveitamento, NomeIndicado, Escolaridade, CNH, ConhecimentoTI, EstadoCivil, Idade, Sexo, Experiencia,          TempoExperiencia, Caracteristicas, OutrosRequisitos) VALUES   (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

// Preparar os dados: s = string / i = inteiro / d = decimal / b = blob
if($stmt = $conn->prepare($sql) ){

    $stmt->bind_param(
        "ssssssssssdsdssssssssssssss",
        $_POST["UnidadeRequisitante"],
        $_POST["Cargo"],        
        $_POST["NumeroVagas"],
        $_POST["Motivo"],
        $_POST["Departamento"],
        $_POST["TipoVaga"],
        $_POST["HorarioTrabalho"],
        $_POST["Jornada"],
        // Definir como STRING na lista acima
        $Data,
        $_POST["ContratoExperiencia"],      
        $fSalarioContrato,
        $_POST["Codigo1"],
        $fSalarioPosExperiencia,
        $_POST["Codigo2"],      
        $_POST["Atividade"],
        $_POST["AproveitamentoInterno"],
        $_POST["NomeFuncionarioIndicado"],
        $_POST["Escolaridade"],
        $_POST["CNH"],
        $_POST["Informatica"],
        $_POST["EstadoCivil"],
        $_POST["Idade"],
        $_POST["Sexo"],
        $_POST["Experiencia"],
        $_POST["TempoExperiencia"],
        $_POST["Caracteristicas"],  
        $_POST["OutrosRequisitos"]  
    );  

    $IdDoctoCont =  mysql_insert_id();

   //executando a query
    if($stmt->execute()){   
        $aretorno["msg"] = "Registro inserido com sucesso.";
        // $stmt->close();
    }else{
        $aretorno["msg"] = "Ocorreu um erro na inclusão dos dados: " . $stmt->error . ". Verifique.";
        $aretorno["status"] = "ERRO";
    }   

    $sqlUp = "UPDATE WFTarefa SET IdDoctoCont = ? WHERE IdTarefa = ?";

    // Preparar os dados: s = string / i = inteiro / d = decimal / b = blob
    if($stmt = $conn->prepare($sqlUp) ){

        $stmt->bind_param(
            "ii",
            $IdTarefa,
            $IdDoctoCont
        );
    }           

}else{
    $aretorno["msg"] = "Ocorreu um erro na preparação dos dados: "  . $stmt->error . ". Verifique.";
    $aretorno["status"] = "ERRO";
}

//close the database
$conn->close();

php mysqli

asked by anonymous 13.05.2015 / 15:36

3 answers

2

You can also test this: lastInsertId () link

p>

13.05.2015 / 15:51

1

The mysqli_insert_id command can help you with this. link

13.05.2015 / 15:45

How and when to use exceptions in PHP? Resize image

score 5 · Accepted Answer

In your code, a i was missing, the insert_id call should be made after execute(). also ensure that the update will only be performed if the insert actually succeeds otherwise displays the error message, use a two-phase transaction this can be done directly by the bank or the connection driver.

With mysqli there are two ways to do it, the first one available since php5 is to turn off auto commit with the method / function auto_commit () before executing the first sql command on success call commit () to permanently save the result, the rollback () happens when the statement returns error can also be called arbitrarily.

The other way is to call begin_transaction () instead of auto_commit() but only is available from version 5.5

Avoid mixing the procedural and OO styles of mysqli.

Change:

$IdDoctoCont =  mysql_insert_id();
if($stmt->execute()){

To:

$conn->autocommit(false);

if($stmt->execute()){ //Caso o insert tenha sucesso, pega o id inserido e faz o update
    $aretorno["msg"] = "Registro inserido com sucesso.";
    $IdDoctoCont =  $conn->insert_id;

    $sqlUp = "UPDATE WFTarefa SET IdDoctoCont = ? WHERE IdTarefa = ?";
    $stmt = $conn->prepare($sqlUp);
    $stmt->bind_param("ii", $IdTarefa, $IdDoctoCont);

    if($stmt->execute()){
       $conn->commit(); //salva todas as operações realizados no banco.
    }

}else{
    $aretorno["msg"] = "Ocorreu um erro na inclusão dos dados:". $stmt->error ." Verifique";
    $aretorno["status"] = "ERRO";
}