Is there any risk in using mysqli_insert_id?

Question

Is there any risk in using mysqli_insert_id?

Navigation

#1 by (5 votes)

4

Let's suppose a website with a relatively high flow rate. A user X inserts a record into the database at the same time as another user Y inserts another record.

Considering that the procedure of this insertion of user data X and user Y is:

mysqli_query(conexao(),"insert into tabela (coluna) values ($valor)");
//INTERVALO ENTRE AS FUNÇÕES
$id_inserido = mysqli_insert_id(conexao());

What are the possibilities between the range of mysqli_query and mysqli_insert_id is there an insertion of another user Y and mysqli_insert_id of user X actually get id of user Y?

php mysql mysqli

asked by anonymous 09.07.2016 / 18:25

1 answer

Hangman Game in Python Replace point by comma in Javascript

score 5 · Accepted Answer

There is no risk of "mixing" ID, by the following

As it is returned from a auto increment column, it does not repeat.
As the function works with the current connection, and each access to a PHP script creates its individual connection (persistent connections should not be used in scripts), connections made by another script will not change this value that you do not mix tasks in parallel on the same page with threads or something like this, which is no longer normal in PHP).

But it's important to watch for a few things.

If you enter multiple lines , contrary to what you can imagine, the conexao() returned is the first of them. This has been resolved so that in replication environments it is possible to leave the same sequence on different machines with the use of the insert id .
Because id acts on the last query performed, make sure to call mysqli_insert_id() immediately after the query that generated the value so as not to get confused.
The MySQL SQL function value mysqli_insert_id() always contains the most recently generated LAST_INSERT_ID() value, and is not restarted between queries.

In addition, these last two notes deserve a special explanation: If you have more than one query on the same page, be careful to check if the one that needs to return the AUTO_INCREMENT has failed or not and even changed some fact record, to avoid by mistake catching the id of a query .

An example of extra caution in more critical operations:

if( mysqli_affected_rows( $con ) > 0 ) { // -1 é erro, 0 é sem inserção
   $last_id = mysqli_insert_id( $con );
} else {
   $last_id = 0;
}

Or leaner:

$last_id = mysqli_affected_rows($con) > 0 ? mysqli_insert_id($con) : 0;

In this way, if there was an error in id , or simply if there was no insert , there is no danger of getting insert from a previous operation. But you do not always have to do this, this only makes sense if in the same script you have more than one query after the other.

Additional considerations:

mysqli_query(conexao(),"insert into tabela (coluna) values ($valor)");

This id in your code should probably be a variable. If you have a separate function for this, you risk having various headaches if the code is not a simple getter .

It would probably be better this way:

$con = conexao();
mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido = mysqli_insert_id($con);

Another thing: If you need multiple conexao() s in the same script, better get it soon then to avoid confusion. Not because of the danger mentioned in the question, but because of the danger of catching id s in the wrong order in an extended code:

$con = conexao();

mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido1 = mysqli_insert_id($con);
// intervalo
mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido2 = mysqli_insert_id($con);
// intervalo
mysqli_query($con,"insert into tabela (coluna) values ($valor)");
$id_inserido3 = mysqli_insert_id($con);
// intervalo
//
// Agora usa $id_inserido1, $id_inserido2 e $id_inserido3 como quiser

Manual:

link