mysql_insert_id()
Moderator: General Moderators
mysql_insert_id()
Does this grab the last insert id that was generated by the script per run?
Lets say two people ran the script at the exact same time, which ID would it grab?
Lets say two people ran the script at the exact same time, which ID would it grab?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
it grabs the id from the script that inserted it... there's no way for it to cross users.
I have wondered how this works though...if anyone knows I'd love to find out.
in the old days of my cold fusion coding I used a function that selected max(id) where ipaddress = remote_addr.
this of course sucked because I had to have an ipaddress field on all of my tables.
I have wondered how this works though...if anyone knows I'd love to find out.
in the old days of my cold fusion coding I used a function that selected max(id) where ipaddress = remote_addr.
this of course sucked because I had to have an ipaddress field on all of my tables.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
It's explained quite clear in http://www.php.net/mysql_insert_id. (If i get it right, it's possible that two users use the same database link if the link was made via mysql_pconnect and thus they could see the same last insert id).
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
I don't think it is possible for two requests to share the same connection, even with pconnect. I don't see that mentioned in the docs and it has never happened in my experience.
The function takes a link parameter (or assumes/creates one) so after reading the docs it is definitely the last insert id on that connection which will be unique to that request.
The function takes a link parameter (or assumes/creates one) so after reading the docs it is definitely the last insert id on that connection which will be unique to that request.
(#10850)
I just noticed that is documentedarborint wrote:I don't think it is possible for two requests to share the same connection, even with pconnect. I don't see that mentioned in the docs and it has never happened in my experience.
Warning
There are a couple of additional caveats to keep in mind when using persistent connections. One is that when using table locking on a persistent connection, if the script for whatever reason cannot release the lock, then subsequent scripts using the same connection will block indefinitely and may require that you either restart the httpd server or the database server. Another is that when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does.
I simply eddited my.ini (my.cnf on *nix) so that it allows only 1 connection:
max_connections=1
And then i wrote to simple php scripts:
first browse to this one to insert a row...
next browse to this one and notice that the last_insert_id still exists (since it's the same 'link/connection'...)
max_connections=1
And then i wrote to simple php scripts:
first browse to this one to insert a row...
Code: Select all
<?php
mysql_pconnect('localhost', 'user', 'password');
mysql_select_db('test');
mysql_query("INSERT INTO persons (name) VALUES ('john')");
echo "last insert id:" . mysql_insert_id();
?>Code: Select all
<?php
mysql_pconnect('localhost', 'user', 'password');
mysql_select_db('test');
echo "last insert id:" . mysql_insert_id();
?>- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
I think there is some confusion here, and I think my responses have not been very clear as well.
The behavior of last_insert_id should not matter whether you use pconnect or connect. It will report the last id inserted in that table whichever is used. So running Tim's script with connect will produce the exact same results -- which are the correct results -- which are to give the last id inserted into the table.
The most important thing is to answer this question:
The behavior of last_insert_id should not matter whether you use pconnect or connect. It will report the last id inserted in that table whichever is used. So running Tim's script with connect will produce the exact same results -- which are the correct results -- which are to give the last id inserted into the table.
The most important thing is to answer this question:
The answer is that it would depends on which order MySQL processed those two inserts. There is no "exact same time" for MySQL. It will make the inserts atomic and it will make the calls to last_insert_id atomic as well. But each script will get a valid id at the time the call was made. That does not mean that the value returned for the id is the current state of the data table at some later time. So, for example, you would not want to increment the value to use as the next id.scottayy wrote:Lets say two people ran the script at the exact same time, which ID would it grab?
(#10850)
So I could safely replace this:
with this:
on a script that will be used (regular connect) by several people at roughly the same time?
Code: Select all
mysql_query("INSERT INTO `names` (`name`) VALUES('john')") or die(mysql_error());
$result = mysql_query("SELECT `id` FROM `table` WHERE `userid` = '$currentuser' ORDER BY `id` DESC LIMIT 1");
$id = mysql_result($result,0);Code: Select all
mysql_query("INSERT INTO `names` (`name`) VALUES('john')") or die(mysql_error());
$id = mysql_insert_id();Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Yes, and you can then to UPDATEs on that record using that id. But remember that if you do other queries then the value may change, and as Tim showed if you have not done an INSERT then it probably gives the last value for that table.scottayy wrote:on a script that will be used (regular connect) by several people at roughly the same time?
(#10850)
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
I think mysql connection doesn't matter but the column in the table must have auto_increment value for primary key instead for mysql_insert_id to work.timvw wrote:I simply eddited my.ini (my.cnf on *nix) so that it allows only 1 connection:
max_connections=1
And then i wrote to simple php scripts:
first browse to this one to insert a row...
next browse to this one and notice that the last_insert_id still exists (since it's the same 'link/connection'...)Code: Select all
<?php mysql_pconnect('localhost', 'user', 'password'); mysql_select_db('test'); mysql_query("INSERT INTO persons (name) VALUES ('john')"); echo "last insert id:" . mysql_insert_id(); ?>
Code: Select all
<?php mysql_pconnect('localhost', 'user', 'password'); mysql_select_db('test'); echo "last insert id:" . mysql_insert_id(); ?>
Actually, it's really does matter (which was the point of my post). If you increase the max_allowed connections to something more than 1 and use mysql_connect instead of mysql_pconnect it's pretty sure you get two different 'links/connections'. And in that situation the result for mysql_insert_id in the script that didn't insert anything will be 0.dibyendrah wrote: I think mysql connection doesn't matter but the column in the table must have auto_increment value for primary key instead for mysql_insert_id to work.
http://dev.mysql.com/doc/refman/5.0/en/ ... ue-id.html
For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
Thanks timvw for the information. I'll give a try ! I have been using the mysql_insert_id() so long and never found a problem. actually I have a web application in a central server and database on same machine and many other user connects to that machine from different other machines and add data . In that I used mysql_insert_id() function to know what users has added in that day . In that case I haven't found any problem .
And regarding connection, I have been using mysql_connect insetad of mysql_pconnect() function .
Dibyendra
And regarding connection, I have been using mysql_connect insetad of mysql_pconnect() function .
Dibyendra
I've been experimenting to find an example where one script does the following: insert, wait for a long time (while script2 is being run), get last insert id and notice that the insert_id is actually one generated in script2.
Apparently php assigns the 'link/connection' to the first script, and does not release it untill the script has terminated. Which makes it impossible for script2 to connect and insert.. And thus the race-condition does not exist.
(Still don't know what happens when the mysql server goes down and up while a script is running. Is the 'link' reestablished or is a new one created? Anyway, this situation seems quite unlikely..)
Apparently php assigns the 'link/connection' to the first script, and does not release it untill the script has terminated. Which makes it impossible for script2 to connect and insert.. And thus the race-condition does not exist.
(Still don't know what happens when the mysql server goes down and up while a script is running. Is the 'link' reestablished or is a new one created? Anyway, this situation seems quite unlikely..)