Retrieve ID of newly inserted record

[hgs]

Hi

Here we talk about tables with an id field of type auto increment.

Inserting a record into such a table assigns a new value to the id field, but how do you know about that value ?

Here is just another method to get that value.

The usage is pretty simple:

$newid=insert("mytable","myid","somefiled");



<?php
function insert($table,$idfield,$uuidfield)
/****************************************/
{
//////////////////////////////////////////////////////////////////////
//
// This function is for use with mysql.
// It is assumed that the table has a column if type 'auto increment'
// and a character field at least 36 charaters long
//
//////////////////////////////////////////////////////////////////////

$query="select max($idfield) from $table";
$result = mysql_query($query);
if (!$result) {
die("$query<br>" . mysql_error());
exit;
}
$row = mysql_fetch_row($result);
$idmax=$row[0]; // used later to speed up location of new record
//
// create a UUID
//
$myid=sprintf( '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ),
mt_rand( 0, 0x0fff ) | 0x4000,
mt_rand( 0, 0x3fff ) | 0x8000,
mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ), mt_rand( 0, 0xffff ) );
//
// insert new record with this UUID
//
$query="INSERT INTO `$table` ( `$idfield` , `$uuidfield` ) VALUES (NULL , '$myid');";
$result = mysql_query($query);
if (!$result) {
die("$query<br>'". mysql_error());
exit;
}
//
// locate new record now using the UUID and the last auto increment value
//
$newid=$myid;
$query="Select $idfield from $table where $uuidfield='$myid' and $idfield>='$idmax'";
$result = mysql_query($query);
if (!$result) {
die("$query<br>'". mysql_error());
exit;
}
$num_rows = mysql_num_rows($result);
if($num_rows > 0) {
//
// clear the field that temporarily holds the UUID
//
$row = mysql_fetch_row($result);
$newid=$row[0];
$query="Update $table set $uuidfield='' where $idfield='$newid'";
$result = mysql_query($query);
if (!$result) {
die("$query<br>'". mysql_error());
exit;
}
} else {
//
// TROUBLE !!!
//
die('Could not find inserted record');
exit;
}
return $newid;
}
?>


Have fun !

Regards
Heinz

[kbluhm]

Why not just use mysql_insert_id() (http://www.php.net/manual/en/function.mysql-insert-id.php), $mysqli->insert_id() (http://www.php.net/manual/en/mysqli.insert-id.php), etc? Or even query MySQL's LAST_INSERT_ID() (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id) directly.

[hgs]

Why not just use mysql_insert_id() (http://www.php.net/manual/en/function.mysql-insert-id.php), $mysqli->insert_id() (http://www.php.net/manual/en/mysqli.insert-id.php), etc? Or even query MySQL's LAST_INSERT_ID() (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id) directly.

Good question :)

Call me paranoid .....

I am not using the functions you mention because I don't know what happens to
the 'insert_'id' in case multiple users do inserts on the same table at the same time.
These inserts, as well as the calls above, might be protected by transactions , but who knows.

The method I use worked for me the last years.

Regards
Heinz

[kbluhm]

I'm sorry, but I see you making 4 queries, along with an expensive sprintf() call, to get the latest ID value... where mysql_insert_id() will only use a single query to get it. Isn't your solution more susceptible to returning an incorrect value? It is at the very least a heavier burden on the server... and if you're worried about ID's getting crossed due to new INSERTs, you're already under a heavy load as MySQL is really damn fast. The last thing I would think you'd want to do is be sending more queries than necessary.

[hgs]

I'm sorry, but I see you making 4 queries, along with an expensive sprintf()
call, to get the latest ID value... where mysql_insert_id() will only use a single query to get it.
Isn't your solution more susceptible to returning an incorrect value?
It is at the very least a heavier burden on the server... and if you're
worried about ID's getting crossed due to new INSERTs,
you're already under a heavy load as MySQL is really damn fast.
The last thing I would think you'd want to do is be sending more queries than necessary.

Without a doubt 100% full ACK!

However, in the past there where reasons for me to do it that way
and it never failed.

[oesxyl]

Good question :)

Call me paranoid .....

I am not using the functions you mention because I don't know what happens to
the 'insert_'id' in case multiple users do inserts on the same table at the same time.
These inserts, as well as the calls above, might be protected by transactions , but who knows.

The method I use worked for me the last years.

Regards
Heinz
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

best regards

[hgs]

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

best regards

Ok ok I surrender :)

I will give it a try.

Regards

[hgs]

According to the common wisdom

Here is what I am using and testing now and it works :eek:




function insert($table)
/****************/
{

//////////////////////////////////////////////////////////////////////
//
// This function is for use with mysql.
//
//////////////////////////////////////////////////////////////////////

$query="Lock tables $table WRITE";
$result = mysql_query($query);
if (!$result) {
die("$query<br>" . mysql_error());
exit;
}
$query="INSERT INTO `$table` () VALUES ()";
$result = mysql_query($query);
if (!$result) {
die("$query<br>'". mysql_error());
exit;
}
$id= mysql_insert_id();
$result = mysql_query("unlock tables");
if (!$result) {
die("$query<br>" . mysql_error());
exit;
}

return $id;

}

[abduraooft]

I'd recommend to check the article at http://www.faqts.com/knowledge_base/view.phtml/aid/1405/fid/272
One notable exception is getting the value of the id you just inserted
in an auto_increment field. You might think that, between the time you
insert the row into the table and the time you ask for the last
auto_increment ID, some other thread might have also inserted a row, and
thus you would get their ID back instead of the one you inserted. But
this is not the case - MySQL was designed to make this operation easy to
work with. So the last_insert_id() is stored *per connection*, and you
do not need to worry about another thread affecting that value.