How to recover the `mysqli->insert_id`from within this function, in addition to the query return?

Asked

Viewed 461 times

0

public function executar($sql){

    $con = new conexao();
    $con->abrir();
    $re = $con->mysqli->query($sql);

    // Preciso retornar esta informação tambem:
    $last_id = $con->mysqli->insert_id;

    $con->fechar();
    return $re;
}

function inserir($tabela,$dados){

    $arrCampo = array_keys($dados);
    $arrValores = array_values($dados);
    $numCampos = count($arrCampo);
    $numValores = count($arrValores);
    if($numCampos == $numValores){
        $SQL = "INSERT INTO " .$tabela." (";
        foreach($arrCampo as $campo){
            $SQL .= "$campo, ";
        }
        $SQL = substr_replace($SQL, ")", -2, 1);
        $SQL .= "VALUES (";
        foreach($arrValores as $valores){
            $SQL .= "'".$valores."', ";
        }
        $SQL = substr_replace($SQL, ")", -2, 1);
    }else{
        echo "Erro ao verificar campos";
    }
    $this->executar($SQL);

}

I am using the following codes, and need to return the ID of the last insertion in the database.

$dados_cliente = array(
    'cliente_nome'=>$cliente_nome,
    'cliente_email'=>$cliente_email,
    'cliente_celular'=>$cliente_celular,
    'cliente_tipo'=>$cliente_tipo,
    'cliente_documento'=>$cliente_documento
);

$comando->inserir('clientes',$dados_cliente);

Which is sent this way. Only after executing the command $comando->inserir('clientes',$dados_cliente); I can’t get the value of the last bank insert.

php mysql oop mysqli

  • Select Max (column)+1 from table

  • 1

    @The problem with this solution is that the ID will not always be MAX+1. If records from the end of the IDS list are deleted, the autonumbering will not reuse the numbers, but the MAX yes, which may be unwanted.

  • 1

    @Bacco Yes. If using auto increment. Generally do not use this function. More if it uses after the Insert only MAX achieves the correct values.

2 answers

1


Whereas you have the structure presented within a class:

public function executar($sql)
{

    $con = new conexao();
    $con->abrir();

    $re = $con->mysqli->query($sql);

    // Preciso retornar esta informação tambem:
    $last_id = $con->mysqli->insert_id;

    $con->fechar();

    return $re;

}

public function inserir($tabela,$dados)
{

    $arrCampo   = array_keys($dados);
    $arrValores = array_values($dados);
    $numCampos  = count($arrCampo);
    $numValores = count($arrValores);

    if($numCampos == $numValores) {

        $SQL = "INSERT INTO " .$tabela." (";
        foreach($arrCampo as $campo){
            $SQL .= "$campo, ";
        }

        $SQL = substr_replace($SQL, ")", -2, 1);

        $SQL .= "VALUES (";
        foreach($arrValores as $valores){
            $SQL .= "'".$valores."', ";
        }

        $SQL = substr_replace($SQL, ")", -2, 1);

    }else{

        echo "Erro ao verificar campos";

    }

    $this->executar($SQL);

}

I considered that the method inserir also belongs to the same class, since there is the use of $this in your body.

You can set a class property called last_id and update its value within the method executar:

public function executar($sql)
{

    $con = new conexao();
    $con->abrir();

    $re = $con->mysqli->query($sql);

    // A propriedade é atualizada aqui:
    $this->last_id = $con->mysqli->insert_id;

    $con->fechar();

    return $re;

}

This way, it will be accessible in every class. Therefore, in the method inserir you can return the value of this property.

public function inserir($tabela,$dados)
{

    $arrCampo   = array_keys($dados);
    $arrValores = array_values($dados);
    $numCampos  = count($arrCampo);
    $numValores = count($arrValores);

    if($numCampos == $numValores) {

        $SQL = "INSERT INTO " .$tabela." (";
        foreach($arrCampo as $campo){
            $SQL .= "$campo, ";
        }

        $SQL = substr_replace($SQL, ")", -2, 1);

        $SQL .= "VALUES (";
        foreach($arrValores as $valores){
            $SQL .= "'".$valores."', ";
        }

        $SQL = substr_replace($SQL, ")", -2, 1);

    }else{

        echo "Erro ao verificar campos";

    }

    $this->executar($SQL);

    return $this->last_id;

}

Thus, by invoking the method. you will have access to the added record id.

$dados_cliente = array(
    'cliente_nome'=>$cliente_nome,
    'cliente_email'=>$cliente_email,
    'cliente_celular'=>$cliente_celular,
    'cliente_tipo'=>$cliente_tipo,
    'cliente_documento'=>$cliente_documento
);

$last_id = $comando->inserir('clientes',$dados_cliente);

echo "ID do registro adicionado " . $last_id;

According to official property documentation insert_id:

Heed! If the last query was not an INSERT or UPDATE or the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.

  • 1

    +1, taking advantage of the class is cleared the code

  • Clean and simple, that’s just what I needed, I’m transitioning from mysql for mysqli and still have some differences that I’m having difficulties.

  • Please click accept the answer to mark the topic as completed.

  • 2019 and I here again with that question. Is there any way to do without using $this?

  • @Helisonsantos Many ways, but probably this is subject for another question. Or get into the chat to discuss better.

  • 1

    The best way is without OOP, so it is without $this too. Only defends OOP in PHP who does not understand at least one of two things (usually both).

Show 1 more comment

0

Change the return of your method to return an array.

public function executar($sql){

    $con = new conexao();
    $con->abrir();
    $re = $con->mysqli->query($sql);

    // Preciso retornar esta informação tambem:
    $last_id = $con->mysqli->insert_id;

    $con->fechar();
    return array($re, $last_id);
}

Then you can retrieve the data to a list, or treat the array the way you prefer.

list ($operationResult, $lastInsertedValue) = $comando->inserir('clientes',$dados_cliente);

  • Just watch out for the method inserir is not, as presented, returning the result of the method executar. In doing so would imply changing the method inserir putting return $this->executar($SQL);. Another is that if the method executar is invoked by other methods, such as atualizar, remover, would imply making this change in all of them.

  • It would be better in this case to use a parameter by reference, to execute($sql, &$last_id) so that you do not touch what already exists, just use the parameter in the new functions only when you need the ID. But it is valid as a very alternative solution. For the specific case, with class, the Woss solution is cleaner, but it is good to show more of a way to do.

Browser other questions tagged php mysql oop mysqli

You are not signed in. Login or sign up in order to post.