How to rescue ID from last record recorded with Mysqli

Question

How to rescue ID from last record recorded with Mysqli

Asked 9 years, 6 months ago

Viewed 3,860 times

6

I am unable to rescue the ID from the last record inserted in my BD, it is coming as Zero, the inclusion is working and at the time of doing an update the script fails.

I’ve 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();

3 answers

7

Your code lacked a i,the call of insert_id must be made after the execute(). also ensure that the update will only be carried out if the Insert actually occurs successfully otherwise displays the error message, use a two-step transaction this can be done directly by the database or the connection driver.

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

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

Avoid mixing 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";
}