& 取得上一步 INSERT 操作产生的 ID
PHP mysql_insert_id 取得上一步 INSERT 操作产生的 ID
mysql_insert_id
mysql_insert_id &
取得上一步 INSERT 操作产生的 ID
int mysql_insert_id
([ resource $link_identifier
如果上一查询没有产生
AUTO_INCREMENT 的值，则
mysql_insert_id()
返回 0。如果需要保存该值以后使用，要确保在产生了值的查询之后立即调用
mysql_insert_id()。
MySQL 中的 SQL 函数
LAST_INSERT_ID()
总是保存着最新产生的
AUTO_INCREMENT 值，并且不会在查询语句之间被重置。
mysql_insert_id() 将
MySQL 内部的 C API 函数
mysql_insert_id()
的返回值转换成
long（PHP 中命名为 ）。如果
AUTO_INCREMENT 的列的类型是 BIGINT，则
mysql_insert_id()
返回的值将不正确。可以在 SQL 查询中用
MySQL 内部的 SQL 函数
LAST_INSERT_ID() 来替代。
Example #1 mysql_insert_id() 例子
&?php&&&&mysql_connect("localhost",&"mysql_user",&"mysql_password")&or&&&&&&&&die("Could&not&connect:&"&.&mysql_error());&&&&mysql_select_db("mydb");&&&&mysql_query("INSERT&INTO&mytable&(product)&values&('kossu')");&&&&printf&("Last&inserted&record&has&id&%d
",&mysql_insert_id());?&
PHP mysql_insert_id note #1
if trying to insert a generated random id use mysql's UUID() to generate a unique key for you, there should be a lot less problems with it.
$query = sprintf( "INSERT INTO `users`"
&&& ."(`id`& & ,`username`)"
&&& ."VALUES"
&&& ."(UUID(),'%s'& & & & && )",
&&& mysql_real_escape_string($user)
if( !mysql_query($query) )
$id = mysql_insert_id();
PHP mysql_insert_id note #2
There is a lot of incorrect info here on "don't use AI" and "using max is equivalent".& Then you have people improperly advising you to use psuedo-random numbers
If you're really worried about the AI field not returning because of the inherent race conditions, just do a select statement based on the vars you just input.& If your vars are not unique, DON'T use these psuedo-random numbers.& When you have enough iterations, the probability that the one of these randoms becomes a duplicate gets pretty high.
Instead, just use the unix timestamp.& However, don't use UNIX_TIMESTAMP() in your query, because if you do, when you run your select statement right after, there's a possibility that you'll end up with a different timestamp.
Since date() continues to count up during the execution of the script, simply store date(U) into a variable or definition.& Then insert and select based on that.& Assuming you're not using mysql procedures:
define(UNIX_TIMESTAMP, date('U'));
$db-&query("insert into table values('', 'a', 'b', 'c', 'd', '".UNIX_TIMESTAMP."'");
$res = $db-&query("select id from table where a_col = 'a' and b_col = 'b' and c_col = 'c' and d_col = 'd' and temp_id = 'UNIX_TIMESTAMP'");
PHP mysql_insert_id note #3
If mysql_insert_id() returns 0 or null, check that you have also included the link identifier. It is optional but if you have more than one database connection then this is necessary.
ex: mysql_insert_id($conn)
PHP mysql_insert_id note #4
I thought this would be relevant to all the people using mysqli and looking for the ID after INSERT command :
function insert_join($catid, $disc_id) {
$conn = db_connect();
&& $demande = "insert into categories_disc values ('', '".$catid."', '".$disc_id."')";
&& $resultat = $conn-&query($demande);
&& if (!$resultat) {
&& & return false;
&& } else {
&&& return $conn-&insert_id; }
Then, on the other side, let us call this function as follows :
$cat_id = insert_join($catid, $disc_id);
if($cat_id !== false) {
&& & && echo "&p&Category stuff was added to the database as follows : &br&";
&& & && echo "&hr&ID de la category : ".$cat_id."&/p&&hr&";
PHP mysql_insert_id note #5
Apparently the value returned by mysql_insert_id() may be correct for BIGINT auto_increment keys below the value of INT, but it may wrap to negative when the BIGINT auto_increment passes the largest signed value of INT.& A timebomb for very large tables...
PHP 5.2.10, MySQL 5.0.81, assume the connection and selection...
$sql = "CREATE TEMPORARY TABLE noise ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=MEMORY";
if (!$res = mysql_query($sql)) die(mysql_error());
$sql = "ALTER TABLE noise AUTO_INCREMENT = ";
if (!$res = mysql_query($sql)) die(mysql_error());
$kount = 0;
while ($kount & 3)
&& $sql = "INSERT INTO noise () VALUES ()";
&& if (!$res = mysql_query($sql)) die(mysql_error());
&& $nid = mysql_insert_id($dbcx);
&& var_dump($nid);
&& $kount++;
PHP mysql_insert_id note #6
Here's an elegant way to INSERT using UPDATE syntax.
function insert_update($table,$fields,$id=NULL)
&&& if($id===NULL)
&& & && $sql="INSERT INTO $table (id) VALUES(NULL);UPDATE $table SET $fields WHERE id = LAST_INSERT_ID()";
&&& }else{
&& & && $sql="UPDATE $table SET $fields WHERE id = $id";
&&& return $sql;
$table="`members`";
$fields="`username` = 'Ultimater',`userlevel` = 'member'";
if(!$profile_exists)
$sql=insert_update($table,$fields);
&&& mysql_query($sql);
$sql=insert_update($table,$fields,5);
&&& mysql_query($sql);
PHP mysql_insert_id note #7
If i can't get a good increment.
I use this function
function get_new_id($table){
&$select = 'select max(`id`) +1as `id` from `'.$table.'`& where `id` != &some big id&';
&$query = mysql_query($select);
&$obj = mysql_fetch_object($query);
&return $obj-&id;
PHP mysql_insert_id note #8
I solved the problem by inserting a "unique" code, like md5 or session_id.
First you insert the date in de database and in the next step you look for de used id.
Just a part of the php script.
$code&& = md5(uniqid());& $name&& = $POST_[name];
$email& = $POST_[email];
$mysql_id = mysql_connect("$dbhost","$dbuser","$dbpass");
$sql = "INSERT into database (code,name,email) VALUES('$code','$name','$email')";
$res = mysql_db_query("$db","$sql",$mysql_id);
&& & include("error.php");
$mysql_id = mysql_connect("$dbhost","$dbuser","$dbpass");
$sql = "select * from database where code='$code' ";& $res = mysql_db_query("$db","$sql",$mysql_id);
&& & include("error.php");
$row = mysql_fetch_row($res);
$id = $row[0]; ?&
PHP mysql_insert_id note #9
Be careful, because this operates on the last performed query, it includes UPDATEs and SELECTs as 'queries'. For example, this is what I set up.
INSERT post into database
UPDATE child forums with insert ID (insert ID is correct)
Insert ID = 0 because of last query
Send the user to their post - but fail because the insert ID is zero.
So store it in a variable like $insert_id instead of querying it every time.
PHP mysql_insert_id note #10
Other methods seem to have problems with missing records in auto increment sometimes you will have records 1 2 5 6 most functions would return the value of 5 for next auto increment when indeed it would be 7. This is the only way I found to make this work so I can use my customer number and the record number to provide a truly unique customer number that is also useful.
$next_increment = 0;
$qShowStatus = "SHOW TABLE STATUS LIKE 'your_table'";
$qShowStatusResult = mysql_query($qShowStatus) or die(mysql_error());
$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];
echo $next_
then you can do something like this
echo& $next_increment ."-". rand();
My first post: I hope this is useful to someone
PHP mysql_insert_id note #11
I have come up with an unorthodox solution for this problem. Im sure its not the fastest but its surely the safest.
In sql I have made a column to be "unique" and instead of fetching the last Id generated by auto_increment I make a random number and I store that number in the before created column. Example:
id ____ rand_nr ____ name
1& ____ 345432 ____ Richard
2& ____ 598675 ____ Joe
3& ____ 926374 ____ Patrick
This way I know exactly what number coresponds to the row inserted.
PHP mysql_insert_id note #12
I may have solved the problems that a number of you have brought to everyone's attention. Here is a simple function that will always return the correct ID of the afffecting row (i.e., the row that will soon be inserted into)--no matter how many consecutive users are online and updating/inserting into the same table that you are also inserting/updating.
Retrieving the ID of the row soon-to-be-affected was imperative to my current project, and the various COUNT() solutions just did not cut it.
Use this before any insert/update statement to retrieve the ID of the row to be inserted into:
function get_current_insert_id($table)
&&& $q = "SELECT LAST_INSERT_ID() FROM $table";
&&& return mysql_num_rows(mysql_query($q)) + 1;
It would be great to read any discussions if anyone considers this function problematic.
PHP mysql_insert_id note #13
"Why on earth are you all arguing about the best way to get the next auto_increment value? The whole point is that it increments automatically. The name should be a give away."
Because you need the same last id value in another table. So you need a way to identify it, in order to use it in the next query. This way its easier than using a new query to get the id like ORDER BY id DESC LIMIT 1
PHP mysql_insert_id note #14
I don't get all the fuss around this.
"The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients."
I can't really see what's inaccurate about that.
"In the case of a multiple-row INSERT statement, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT if no such value is generated, it returns the last last explicit value inserted into the AUTO_INCREMENT column."
I must be missing something here but why would you insert multiple rows and then only handle the last one with some favoured behaviour? You could just as well insert them one at a time and then handle each row separately with the latest id.
I can't see what's wrong with that.
However I can see what's wrong with simply using max(my_table.id_column) because of the concurrent access issues this would imply.
PHP mysql_insert_id note #15
There's nothing inherently wrong with using auto-increment fields. There's also nothing wrong with the main competetive idea, which is for the database to supply a primitive sequence of non-repeating identifiers, typically integers. This is rather like which side of the road you drive on.
The bigger problem is when people don't understand what they are doing with database access. It's like driving a car without really knowing the rules of the road. Such people wind up making bad decisions without realizing it, and then, eventually, something breaks.
Databases are complex beasts, and worth taking the time to really understand. Learn about the implications and limitations of different approaches to solving problems. Then, you will be prepared to pick a solution based on what has to work.
PHP mysql_insert_id note #16
Why on earth are you all arguing about the best way to get the next auto_increment value? The whole point is that it increments automatically. The name should be a give away.
Don't calculate the next id, just leave the field blank and let the database issue the id itself.
PHP mysql_insert_id note #17
To get the most accurate NEXT auto_increment id use this function
function mysql_next_id($table) {
&&& $result = mysql_query('SHOW TABLE STATUS LIKE "'.$table.'"');
&&& $rows = mysql_fetch_assoc($result);
&&& return $rows['auto_increment'];
Any other methods of predicting the next autogenerated id will fail in certain cases.
Most of the programmers try to get the MAX(id)+1 (or sort the table backwards and get the first id) but this will cease to work when you delete the last record.
However in a race condition even this function may fail if other users insert records in the time between calling this function and using the return value. You may use LOCK TABLES to prevent such a case.
Anyway, the best advice I can give you is to RECONSIDER YOUR LOGIC TO AVOID USING AUTO_INCREMENT.
PHP mysql_insert_id note #18
Using 'SELECT MAX(id)+1...' will not return the next auto_increment id. This function is totaly unreliable by two reasons.
1. In race conditions there is no guarantee that other user will not insert new record while your function have done its work. This will render your "generated" last_id obsolete. It is rare case but it happens.
2. Most of all, if the last record(s) in the table is deleted the max id will no longer match the auto_increment value, because auto_increment never repeats numbers, it increases whenever an insert statement is completed and does not decrease if you erase the last record!!!
e.g. If you have this table with the last record deleted:
3. truck - [erased]
auto_increment is 4
but MAX(id) is 2!!!
PHP mysql_insert_id note #19
Just wanted to re-iterate previous comment on receiving NULL or 0 return statement from calling mysql_insert_id() after insert statement.
If you have multiple mysql connections (i.e. mysql_connect() or mysqli_connect()) on the page you will need to specify the the connection you are using when calling this function!
mysql_insert_id($MY_CONN);
PHP mysql_insert_id note #20
It seems that, in case of a very first INSERT, mysql_insert_id() returns 0. My guess is that MySQL does not trigger auto increment if the table is empty. Once an entry is present, it works as expected.
PHP mysql_insert_id note #21
If mysql_insert_id() returns 0 or null, check your auto increment field is not being set by your sql query, also if you have multiple db connections like I did, the solution is to create a seperate db connection for this query.
PHP mysql_insert_id note #22
Forget about using MAX to get the last inserted id. Race conditions like other users inserting between your SELECT MAX(.. and your INSERT may render your id unusable.
The WAY to get the id is by using mysql_insert_id() or the mysql SQL function LAST_INSERT_ID().
Take care, if using mysql_insert_id() you should provide the resource returned by the mysql_connect, not the resultset returned by mysql_query.
PHP mysql_insert_id note #23
and the primary reason to use neither of those last two solutions is both could result in race conditions when there is more than a single user with access to the database. when you use MAX or ORDER BY DESC LIMIT 1 you will retrieve the maximum value from the table, at that moment. That doesn't mean another user doesn't do an insertion in the primary and secondary table BEFORE you do your insertion in the secondary table. You have thus inserted a row with a correct id into the second table. Always use last_insert_id()
PHP mysql_insert_id note #24
function previous_id($table, $id_column) {
& if ($table && $id_column) {
&&& $result = mysql_query("SELECT MAX(".$id_column.") AS maxid FROM ".$table);
&&& $stuff = mysql_fetch_assoc($result);
&&& return $stuff['maxid'];
& } else {
&&& return false;
Use MySQL's built-in function MAX(). It's faster because any min/max algorithm is faster than sorting entire table.
PHP mysql_insert_id note #25
I realise this isn't the best way of doing it, but if use auto_increment in your tables, which just increases by one every time you add a row, you can get the last used id simply by quering the database, selecting the id column and ordering it descendingly and pulling out the first row returned. The added bonus is you can do it anytime, instead of just after an INSERT query.
function previous_id($table, $id_column) {
& if ($table && $id_column) {
&&& $result = mysql_query("SELECT " . $id_column " FROM " . $table . "
&&& ORDER BY " . $id_column . " DESC
&&& LIMIT 1");
&&& $stuff = mysql_fetch_assoc($result);
&&& return $stuff[$id_column];
& } else {
&&& return false;
Just change $table to the table name, and $id_column to the column which auto increments.
PHP mysql_insert_id note #26
Please be warned that this function, like almost every other MySQL function, requires that the same database resource link that was used to insert the data into the database be used to get the last auto increment value.
If you close the database connection after inserting data, then open one up again and then attempt to get the last auto increment value, you will get an incorrect value since all resources related to the last insert query were cleaned up after the connection was closed.
PHP mysql_insert_id note #27
There is a problem with the function below submitted by rudolflai
In that mysql_autoid function, what happens when between the time you've retrieved the id and the time you do something with it (like INSERT a new record) another new record has been inserted into the database in the interim?
Better make sure you LOCK the tables you're working on before you use the function and don't UNLOCK until after you've done something with the id returned.
PHP mysql_insert_id note #28
It's not true that mysql_insert_id() only returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
We can use LAST_INSERT_ID() statement that will return value for mysql_insert_id().
$sql = "UPDATE `mytable` SET `mytable_id`= LAST_INSERT_ID(`mytable_id` + 1) WHERE ...;
@mysql_query($sql);
$last_id = mysql_insert_id();
LAST_INSERT_ID() statement will affect mysql_insert_id().
PHP mysql_insert_id note #29
When used in transactions, mysql_insert_id() MUST be called before committing. Otherwise, it will return unpredictable results.
PHP mysql_insert_id note #30
One of the problem of the LAST_INSERT_ID function is that it may return an old Auto_Increment number if the INSERT did not have the time to run properly while the mysql_insert_id always returns the good value. I tried to insert a row imidiatly followed by a select LAST_INSERT_ID and it kept on returning me the LAST_INSERT_ID before my insert statement.
PHP mysql_insert_id note #31
Below is a snippet for retriving the next insert id from a mysql table. It functions like the auto_increment option in phpMyAdmin. Hope you find it helpful. =]
function mysql_autoid($id,$table){
&&& $query = 'SELECT MAX('.$id.') AS last_id FROM '.$table;
&&& $result = mysql_query($query);
&&& $result = mysql_fetch_array($result);
&&& return $result[last_id]+1;
PHP mysql_insert_id note #32
Have had many people coming to me with issues regarding last_insert_id and mysql_insert_id() etc
This is a very simple solution that caters for most. As you can see it gets the last auto inc from the field, in this case, ID.
mysql_query("LOCK TABLES art WRITE");
mysql_query("INSERT INTO table (....) VALUES (....)");
$result = mysql_query("SELECT MAX(ID) AS LAST_ID FROM table");
$result = mysql_fetch_array($result);
mysql_query("UNLOCK TABLES");
echo "Last ID#" . $result[LAST_ID];
PHP mysql_insert_id note #33
Be careful when using "insert ignore". If the unique index already exists, the record will not be added, but it WILL give you the id of the next auto_increment value even though it didn't create it.
$sql = "insert ignore into sometable set num=10";
mysql_query($sql) or die();
echo mysql_affected_rows()."&br&";
echo mysql_insert_id()."&br&&br&";
$sql = "insert ignore into sometable set num=10";
mysql_query($sql) or die();
echo mysql_affected_rows()."&br&";
echo mysql_insert_id()."&br&&br&";
would give:
PHP mysql_insert_id note #34
If you insert a data row by using the ON DUPLICATE KEY UPDATE clause in an INSERT-statement, the mysql_insert_id() function will return not the same results as if you directly use LAST_INSERT_ID() in MySQL.
See the following example:
&& // insert a datarow, primary key is auto_increment
&& // value is a unique key
&& $query = "INSERT INTO test (value) VALUES ('test')";
&& mysql_query( $query );
&& echo 'LAST_INSERT_ID: ',
&& & & && mysql_query( "SELECT LAST_INSERT_ID()" ),
&& & & && '&br&mysql_insert_id: ',
&& & & && mysql_insert_id();
This will print:
LAST_INSERT_ID: 1
mysql_insert_id: 1
In this case the function returns the same as the MySQL-Statement.
But see the insert on an existing key:
&& $query = "INSERT INTO test (value)
&& & & & & & & && VALUES ('test')
&& & & & & & & && ON DUPLICATE KEY UPDATE value = 'test2'";
&& mysql_query( $query );
&& echo 'LAST_INSERT_ID: ',
&& & & && mysql_query( "SELECT LAST_INSERT_ID()" ),
&& & & && '&br&mysql_insert_id: ',
&& & & && mysql_insert_id();
This will print:
LAST_INSERT_ID: 2
mysql_insert_id: 1
By using the ON DUPLICATE KEY UPDATE clause, only the old datarow will be modified, if the INSERT statement causes a duplicate entry, but the LAST_INSERT_ID() function returns the next auto_increment value for the primary key, which is by the way not set as the next auto_increment value in the database.
The mysql_insert_id() function returns the primary key of the old (and changed) data row. For me this is the right operation method, because the LAST_INSERT_ID() function returns a value which is not referenced to a data row at all.
Greets from Munich.
heiligkind
PHP mysql_insert_id note #35
As mentioned by frumler at the-beach dot no_spam dot net
the LAST_INSERT_ID works like a charm when inserting values into tables.
I'm not sure why anyone would need mysql_insert_id() when LAST_INSERT_ID is readily available.
Say you have a table called "transaction" and a table called "accounts".& Obviously each account must be created using a transaction, so every time a record is created in the accounts table, a record must be created in the transaction table containing the same account_id(auto_increment) that was just created by mysql.
Here's a simple way to do this:
$account_query="INSERT INTO accounts (account_id,type) VALUES (NULL,'saving')";
$transaction_query="INSERT INTO transactions(transaction_id,account_id) VALUES (NULL,LAST_INSERT_ID)";
$a_query=mysql_query($account_query);
$t_query=mysql_query($transaction_query);
PHP mysql_insert_id note #36
Take care of setting an empty value for the AUTO_INCREMENT Field else you never get a value except zero returned from mysq_insert_id() ....
Ciao Ephraim
PHP mysql_insert_id note #37
My apologies for the error below (that was modified out of a class) - as you cannot define a constant as an array.
replace the line:
define("ID",...);
$mysql_id = mysql_query("...");
$mysql_id is now an array in which the first element $mysql_id[0] holds the last inserted id.
Apologies if anyone struggled over that one... esp. the noobs.
PHP mysql_insert_id note #38
An effective way to retrieve the last insert id without fail or having two queries inserted at the same time causing an unexpected number being returned.
mysql_query("LOCK TABLES apc_forms WRITE");
mysql_query("SET AUTOCOMMIT = 0");
mysql_query("INSERT INTO apc_forms (form_title, form_event_id, form_expirey) VALUES ('title',1,'')");
define('ID',mysql_query("SELECT LAST_INSERT_ID()"));
mysql_query("COMMIT");
mysql_query("UNLOCK TABLES");
PHP mysql_insert_id note #39
Just a quick note. mysql_insert_id() does work with REPLACE.
PHP mysql_insert_id note #40
Beware, mysql_insert_id() only returns the value of the last syntaxically correct insert statement.
If your code has a problem and the insert is not understood by the server then the value of the last working insert command is returned.
Put something else in place such as "select count( id ) from table" before and after the mysql_insert_id() call to ensure that a row was inserted.
PHP mysql_insert_id note #41
A bit more on return values:
mysql_insert_id() returns 0 if you haven't actually manipulated anything yet.
Also, it returns 0 even if the DB connection is lost[0] between inserting and calling mysql_insert_id() - so you can always count on getting an integer.
[0] By 'lost' I mean e.g. a DB crash. Actually closing the open link and then trying to communicate with the DB will of course result in an error.
PHP mysql_insert_id note #42
It's possible to do the same with M$ Server.
function odbc_insert_id()
& $query = "SELECT @@IDENTITY AS ID;";
& $result = odbc_exec($this-&m_rConnectionID, $query);
& $row = odbc_fetch_object($result);
& return $row-&ID;
PHP mysql_insert_id note #43
In reply to: sly at noiretblanc dot org:
Make sure that auto_increment has an capital A as the first letter, otherwise it WON'T work! So you have to spell it as Auto_increment... And then it works fine.
PHP mysql_insert_id note #44
I believe the "resource link" being referred to is not what is returned from mysql_query() but the $link returned from mysql_connect(). mysql_insert_id() will just use the most recent connection if there is no explicit $link being used.
So the above example in the manual page itself should behave the same with mysql_insert_id($link) at the end instead of the mysql_insert_id() they used. If you had multiple connections, the $link might come in handy.
Also in reading the mysql manual itself, there is some enlightening information on the fact that this does appear to be totally safe to use because it is on a per-connection basis.
Here's the relevant quote from the manual on LAST_INSERT_ID() which is located here:
"The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions."
Sounds safe to me. I couldn't imagine this would be done any other way *but* on a per-connection basis, otherwise chaos would ensue. The only way to test it would be to perform a multi-thread type test. Perhaps someone is up for it and wants to post their results somewhere? :)
PHP mysql_insert_id note #45
If you use this function after doing an INSERT ... SELECT to insert multiple rows at once, you get the autonumber ID of the *first* row added by the INSERT.
e.g. if there are 4 records in table 'init' that have column 'type' = 2
I want to add these 4 records to table 'game'
Table game has an autonumber column 'game_id' that is currently at 32.
If I do this query:
INSERT INTO game (type, players, rounds)
SELECT type, players, rounds FROM init
WHERE type = 2
Then mysql_insert_id() will return 33, not 36.
PHP mysql_insert_id note #46
In response to treylane at example dot com.
It is very very very important that you put in an "or die" or some other form of error handling.
Some scripts can fail invisibly and insert invalid data throughout your whole database because of mysql_insert_id
inserting the last successful insertid rather than reporting that the last attempt failed.
example of an or die statement.
$result = mysql_query($sql)
or die("Invalid query: " . mysql_error());
$EventID = mysql_insert_id();
PHP mysql_insert_id note #47
any zerofills on your id get chopped off on this function because it returns an int.
PHP mysql_insert_id note #48
To get the NEXT insert id use the mysql query SHOW TABLE STATUS LIKE 'tablename' and get the field auto_increment...
PHP mysql_insert_id note #49
This might be obvious, but it tripped me up - be careful when using last_insert_id() with persistent connections - running last_insert_id() after a failed update/insert/etc will return the last insert id of the last successful update/insert made by that CONNECTION rather than 0 for the number of rows updated by the previous non-working query, and who knows what the last query run on that connection was.
PHP mysql_insert_id note #50
Just a reminder, mysql_insert_id() should be called after 'mysql_affected_rows()', but BEFORE 'mysql_query("COMMIT")'.
PHP mysql_insert_id note #51
If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:
INSERT INTO foo (auto,text)
&&& VALUES(NULL,'text');& & & & & & & # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
&&& VALUES(LAST_INSERT_ID(),'text');& # use ID in second table
...found here:
It works even without inserting the NULL va)
The following is great for monitoring:
&&& $new_id = mysql_insert_id();
&&& print "New id: $new_id
Hope it helps you all, cheers.
PHP mysql_insert_id note #52
You can't do an INSERT DELAYED and expect to get anything but zero, for it runs in a separate thread, and mysql_insert_id() is tied to the current thread.
PHPMySQL - 函数}