MySQL Bugs: #49942: In PHP mysql_insert_id() returns the wrong ID for multiple inserts in one query

Bug #49942	In PHP mysql_insert_id() returns the wrong ID for multiple inserts in one query
Submitted:	26 Dec 2009 18:24	Modified:	26 Dec 2009 18:44
Reporter:	Michael Ozeryansky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:		OS:	Windows (XP Pro SP3)
Assigned to:		CPU Architecture:	Any
Tags:	id, insert, multiple, mysql_insert_id(), php

Description:
I sent a bug request to PHP about my issue. The response told me this is all part of the API calls, so I am asking my question here. I'm using PHP 5.3.0.

In the the function mysql_insert_id() description: "Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT)." and it will return: "The ID generated for an AUTO_INCREMENT column by the previous query on success" as stated on php.net
Details: http://us.php.net/manual/en/function.mysql-insert-id.php

When I run my query, which is an insert with multiple rows, I get the first ID generated not the last ID generated.

The expected result for 10 inserts with AUTO=1 would be 10
But the actual results for 10 inserts with AUTO=1 is 1

In my script it's actually more useful to do this, but I think for other scripts it might be an issue.

How to repeat:
In PHP:
$query = 'INSERT INTO player VALUES ';
foreach($data['players'] as $player){
	$query .= sprintf("(null, '%s', '%s', '%s', '%s', '%s', %s),", $stuff,
$stuff, $stuff, $stuff, $stuff, $stuff);		
}
$query = substr($query,0,-1);//removes last comma
$result = mysql_query($query, $resource) or die(mysql_error());
$firstPlayerID = mysql_insert_id($resource);

Which is basically:
INSERT INTO player VALUES (foo, bar),(foo2, bar2),(foo3, bar3)...

Suggested fix:
I don't know if this is a feature or a bug.

This is a feature. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/mysql-insert-id.html:

"In the case of a multiple-row INSERT statement, the return value of mysql_insert_id()  depends on the MySQL server version.

In MySQL 5.1.12 and later, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value that was successfully inserted. In MySQL 5.1.11 and earlier, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value, regardless of whether insertion of that value was successful."

Thank you. This is actually better for me, less math.