SELECT MAX (id) vs LAST_INSERT_ID()

[alomar]

hello. everybody
imagine a table called tbl_categorie: just an autoincrementa id and a text field.
I just make a simple insert query in it:

 
$sql1="INSERT INTO tbl_categorie (categoria) VALUES ('$cat')";
[code=php]  

Now I'm wondering: how can I know the value of the latest  id???
Or better: is there any difference between the values retruned by next three different "options"???


first option:

[code=php] $sql2 = "SELECT MAX(idcategoria) AS idcategoria FROM tbl_categorie";

second

 
$idcategoria=mysql_insert_id();

third

 $sql="select LAST_INSERT_ID() from tbl_prodotti";

THANX

AKA alomar

[Lars_Berg]

Use either of the two last alternatives. They return the same value.

The first alternative should never be used for the purpose of returning the newly generated auto_increment value.

[alomar]

Originally posted by Lars Berg
Use either of the two last alternatives. They return the same value.

The first alternative should never be used for the purpose of returning the newly generated auto_increment value.

thank you but...can u explain me why?

alomar

[Lars_Berg]

If you use

insert into t

select max(id) from t

there can be other processes that inserts records into t after your insert but before your select. Those inserts generates new auto increment value in the table and this is the value that is returned by your select query.

Using last_insert_id will avoid this problem as mysql remembers the last created auto_increment value on a per connection basis.

Another problem is that mysql may (depending on table type) reuse auto increment values if records have been deleted.

[alomar]

Another problem is that mysql may (depending on table type) reuse auto increment values if records have been deleted. [/B]

are u sure about that?
Meanwhile, thank u very much!

alomar