How to retrieve all last inserted rows IDs in mysql-php?

Question

Is there any efficient way to retrieve all last inserted rows auto incremented IDs from mysql database using php? Suppose I don't know how many rows are there to be inserted in a batch but I need all of their IDs after insertion.

After Googling I didn't find any efficient way to do this, so I'm not sure is that possible. I can follow this one but have to hit database multiple times to retrieve IDs.

$data = array (
array(1,2),
array(3,4),
array(5,6)
);

$ids = array();

foreach ($data as $item) {
   $sql = 'insert into table (a,b) values ('.$item[0].','.$item[1].')';
   mysql_query ($sql);
   $ids[] = mysql_insert_id();
}

Thank You

before your batch get the max id. after your batch get max id. now you know the range. — ITroubs, Apr 10 '13 at 07:15
@ITroubs' solution is interesting, but I would imagine it's *less* efficient as it requires two additional queries — Explosion Pills, Apr 10 '13 at 07:17
This is not related to your question but I have seen a point that you wrote `$ids = array();` but you used `$id[] = mysql_insert_id();` when collecting. One character of the variable is missing 's' Should not it be `$ids[] = mysql_insert_id()` ? — zkanoca, Apr 10 '13 at 07:22
Just try to print $id you will get all last inserted array of id — Suresh Kamrushi, Apr 10 '13 at 07:23
@ExplosionPills depends on how many rows are being inserted - though irrespective the cost of a max-primary-key query is virtually nothing. — AD7six, Apr 10 '13 at 07:26
@AD7six ExplosionPills is right. If the db is hosted on a server with hundreds of pages getting the AUTO_INCREMENT value form the Information_Schema might be a bit of a bottleneck. — ITroubs, Apr 10 '13 at 07:28
On a sidenote: the `mysql_*` functions are in the process of getting deprecated and will be removed from future PHP versions. You should not use them to write new code. Use `mysqli_*` functions or PDO objects instead. — Gerald Schneider, Apr 10 '13 at 07:29
@iTroubs At most, it's going to cost the same as inserting a row into the db - i.e. it's not likely to be significant — AD7six, Apr 10 '13 at 07:30
@GeraldSchneider: Yes. I'm aware of that and using mysqli_* in my actual code already. thanks :) — leevi, Apr 10 '13 at 07:33

score 1 · Accepted Answer · 2013-04-10T07:57:13.030

1

your code is near complete solution.

$data = array (
array(1,2),
array(3,4),
array(5,6)
);

$ids = array();

foreach ($data as $item) {
   $sql = "INSERT INTO `table` (`a`, `b`) VALUES ('{$item[0]}' ,'{$item[1]}');";
   mysql_query ($sql) or mysql_error();
   $ids[] = mysql_insert_id();
}

var_dump($ids);

If the question is about performance issue, then another solution could be

foreach ($data as $item) {
   $sql = "INSERT INTO `table`.`table` (`a`, `b`) VALUES ('{$item[0]}' ,'{$item[1]}');";
   mysql_query ($sql) or mysql_error();
}

$last_id = mysql_insert_id();
$total = count($data);
for($i = 0; $i <= $total; $i++){
    $ids[] = $total-$i;
}

This will prevent multiple sql query.

edited Apr 10 '13 at 07:57

answered Apr 10 '13 at 07:20

you are pointing at $ids variable. But eventhough if your print $id as per leevi code it should all the ids. – Suresh Kamrushi Apr 10 '13 at 07:22
@silentboy: nothing wrong actually as you say, but I'm just curious will that be efficient... – leevi Apr 10 '13 at 07:23
@SureshKamrushi look OP's code. what `$ids = array()` do there. he put `$ids` all in `$id` array. that is the mistake – Apr 10 '13 at 07:26
@leevi Do u get any error. i do not get a problem with my code. – Apr 10 '13 at 07:28
@silentboy: he initialize and use different variable, but eventhough $id must contain all ids. PHP does not required variable initialization. – Suresh Kamrushi Apr 10 '13 at 07:28
@silentboy: that was a typo. I've edited that. – leevi Apr 10 '13 at 07:28
@leevi Edited my answer. works in locally. test about yours – Apr 10 '13 at 07:41

score 1 · Answer 2 · answered Apr 10 '13 at 07:33

Mysql only returns the last inserted id, unfortunatelly there's no way to retrieve all of them (Directly). You will have to use work arounds, for example:

Calculate the inserted rows, and the last inserted ID, and calculate the rest backward.
Generate custom ID's instead of letting mysql create them incrementally, and if the insert query success, store the id's into an array (Normally if one of them fails, the whole query would fail anyway).

The second method is recommended, because with n1, if you are doing slighly complex operations, for example using INSERT blah blah ON DUPLICATE KEY the modifies would count as 2 inserts (don't ask me why) instead of one, and you have to do some calculations to see how many were actually inserted and how many modified.

By the way, stop using mysql_ extension, it's deprecated, use mysqli or pdo instead

Thanks for your response. I'm thinking of an efficient solution in terms of auto increment actually. FYI: I'm aware of the mysql_ extensions and using mysqli_ already in my actual code. Thanks :) — leevi, Apr 10 '13 at 07:38
One idea would be to add a datetime or timestamp to the inserts, get the last inserted id's datetime, and query all the id's with that datetime. — aleation, Apr 10 '13 at 07:40

score 0 · Answer 3 · answered Apr 10 '13 at 07:17

0

If your auto_incremented field is "normal", so it does not behave in some special way but increases the last id by one, you can find the maximal value of it before insertion and then find all the records that are higher of it.

Example:

$sql = "SELECT id FROM table ORDER BY id DESC LIMIT 1";
    // get maximal value before insert

$sql = "SELECT id FROM table WHERE id > :last_id"; // get all new

But this solution is not much professional.

answered Apr 10 '13 at 07:17

Voitcus

4,463
4
24
40

2

Will be inaccurate if anything else makes a request that inserts into the db - which would be the case if the same page/request was loaded by two users - if that's not a factor, may work. However, in that case why not simply run the first query - twice. `range($firstMax, $lastMax)` - would give all ids. And will work even if the range is a huge number. – AD7six Apr 10 '13 at 07:23
1

i would run the first query after the first insert. if you do that befor the first insert you run into huge problems if someone deleted some of the last rows and the autoincrement != max(id)!! – ITroubs Apr 10 '13 at 07:27
@AD7six Yes, you are right. I haven't thought about the possibility of inserting rows from another source. – Voitcus Apr 10 '13 at 07:27

score 0 · Answer 4 · answered Apr 10 '13 at 07:39

When i need to track batches of records entered, I have an extra field in the table, usually labeled as stamp or something. When the records are inserted, the stamp field is then populated with the current timestamp as given by the time() function. Then every batch can be tracked, no matter how old they are.Also, saving the timestamp is an easy way to get the exact time when they were added.

score -1 · Answer 5 · answered Apr 10 '13 at 07:37

-1

Getting the id of last insert id

    <?php

$conn = mysql_connect('localhost', 'user', 'password');

mysql_select_db('test');

$data = array (
array(1,2),
array(3,4),
array(5,6)
);

$ids = array();

foreach ($data as $item) {

   $sql = 'insert into testtable (a,b) values ('.$item[0].','.$item[1].')';   
   mysql_query ($sql, $conn);
   $id[] = mysql_insert_id($conn);
}

answered Apr 10 '13 at 07:37

Jayendra Kainthola

167
2
11

1

-1 This looks like a copy of the code in the question - with a typo in it – AD7six Apr 10 '13 at 07:37
@AD7six:I have used the same code but modify mysql_insert_id($conn) instead of mysql_insert_id. As I am new to stackoverflow, so I thought, it would be nice to share the complete example. If you know, how to share this type of example, please let me know. – Jayendra Kainthola Apr 10 '13 at 07:44
*[If the link identifier is not specified, the last link opened by mysql_connect() is assumed](http://php.net/manual/en/function.mysql-insert-id.php)* - i.e. adding `$conn` to the code doesn't change how it works. Changing `$ids` to `$id` just means a new variable has all the ids instead of the one the OP is expecting. – AD7six Apr 10 '13 at 07:54
OK. But it only work for me when I added $conn into mysql_insert_db. Thanks for providing link, I will go through to this. – Jayendra Kainthola Apr 10 '13 at 07:58

How to retrieve all last inserted rows IDs in mysql-php?

5 Answers5

Linked