In codeigniter, Is it possible to get last Inserted IDs from bach inserts ?

Question

Is there any way to get last inserted IDs from batch inserts in codeigniter ?

This might seem silly but I wonder if there is any help available.

No. You'd need to run the inserts in a loop to get each of the IDs. — gen_Eric, Apr 10 '14 at 14:51
http://stackoverflow.com/questions/18372537/error-in-getting-the-last-inserted-id-of-a-query-using-batch-insert-in-codeignit — Ivanka Todorova, Apr 10 '14 at 14:56
Actually I do not want to insert data in loop as of avoiding load on server. I'm running a cronjob which executes after every minute with thousands of feeds. — Zeshan, Apr 10 '14 at 15:05
Nope, as @rocket suggested you need to loop and insert the data. — Nil'z, Apr 10 '14 at 15:05
@ZeshanKhattak: You can run the insert loop inside of a transaction. Then commit it when the loop is done. That should lessen the strain on the server. — gen_Eric, Apr 10 '14 at 15:10
Hm, how about getting the last ID (assuming auto-increment), and just add the number of batch items to it? Still better than using a loop. — Shomz, Apr 10 '14 at 15:13
@Shomz: At my work, the MySQL server is set to increment AUTO_INCREMENT fields by 2 instead of 1 (we were toying with having 2 MySQL servers). Chances are, the OP's server doesn't do this, but it is something that can be configured. I don't know how to set or view the status of this option, though. — gen_Eric, Apr 10 '14 at 15:15
@RocketHazmat I see, that makes it a bit harder, but you know, as long as it's a constant (2, 3, whatever...), it's still doable, and I think much more effective/less strenuous than looping, especially with a lot of iterations. — Shomz, Apr 10 '14 at 15:18
@Shomz: There might be a way to ask the DB for the value of this option. I'm not sure, and chances are the OP's database has it set to 1, so your suggestion will work fine. I was just pointing out a "gotcha" with it :-) — gen_Eric, Apr 10 '14 at 15:20
@RocketHazmat Sure, I agree with everything you said... And even if you can't get that info from the DB, you can still keep the value in the config somewhere. — Shomz, Apr 10 '14 at 15:27

score 1 · Accepted Answer · answered Apr 10 '14 at 15:14

You're gonna need to run the inserts in a loop, that's the only way to get each of the IDs. To lessen the strain on the server, you can do this inside of a transaction.

$ids = array();

$this->db->trans_start();

foreach($data as $val){
    $this->db->insert('yourTable', array(
        'field1' => $val['foo'],
        'field2' => $val['bar']
    ));

    $ids[] = $this->db->insert_id();
}

$this->db->trans_complete();

Shomz · Answer 2 · 2014-04-10T15:25:24.737

Since $this->db->insert_id(); should return the ID of the first row from the batch, you can just add the number of affected rows to it. But of course, assuming that's possible because you're using auto-increment.

As Rocket Hazmat pointed out, there could be variations in auto-increment values. However, that's not a problem if we know the increment value. So, something like this should work:

$increment = 2;                        // auto-increment value (probably 1 though)
$id = $this->db->insert_id();          // id of the first inserted row from the batch 
$total = $this->db->affected_rows();   // number of inserted rows
$ids = array($id);                     // add the first on to our output array of ids
for ($i = 1; $i < $total; $i += $increment) {
    $ids[] = $id + $i;                 // add each of them
}

In codeigniter, Is it possible to get last Inserted IDs from bach inserts ?

2 Answers2