PHP - getting automatically incremented values ​​from advanced inserts

Question

I am currently using $mysqli->insert_id to retrieve automatically assigned primary keys from a MySQL table when inserting new rows. This works great on individual inserts. This is a simple case.

 $result = $mysqli->query($query); $NewPrimaryKey = $mysqli->insert_id; 

However, I found out (from this site actually) that several attachments do better with advanced insertion:

 insert into table (name1,name2,name3) values ('blah1','blah2',blah3'), ('blah1','blah2',blah3'), ('blah1','blah2',blah3')) 

This also works great - except when I want to know which keys were automatically allocated.

The PHP manual says:

When you run extended inserts in a table with the AUTO_INCREMENT field, the value of mysqli_insert_id () will be equal to the value of the first row inserted.

My testing confirms this.

Note. I do not need only the first or last.

I don't want to go back to thousands of single inserts just so that I can automatically get the auto_allocated key.

Does anyone know a way to make extended inserts and return all the keys that were allocated (presumably in an array)?

I had to just read and save the last existing key before doing an extended write, and then calculate the expected values ​​of the selected keys, but it looks a bit orderly.

Answer 1

I don’t think you can get all the newly inserted row identifiers, but if insert_id returns the first identifier, you can simply assume that the inserted identifiers are the range of this identifier to the number of inserts (which should be if something is wrong, but then the whole insert must fail, since it is atomic). You can also select all auto_increment identifiers that are greater than or equal to the new insert_id .

Answer 2

You can calculate the keys, right?

 $keys=array(); for($id=mysqli_insert_id();$id<=$id+$totalRecords;$id++) $keys[]=$id; 

Or use ORM as a doctrine