Error in getting the last inserted ID of a query using batch insert in CodeIgniter

Question

How can I get the last inserted ID of a query using the batch insert in CodeIgniter. I used the code $this->db->insert_id() but it returns the ID of my first inserted array. I can't get the last insert.

Here's what I did:

for ($x = 0; $x < sizeof($filtername); $x++) {
    $orders[] = array(
        'poid'              => null,
        'order_id'          => $poid,
        'item_desc'         => $filtername[$x],
        'item_qty'          => $filterquantity[$x],
        'item_price'        => $filterprice[$x],
        'total'             => $filtertotal[$x],
        'cash_on_delivery'  => $val_delivery,
        'is_check'          => $val_check,
        'bank_transfer'     => $val_transfer,
        'transaction_date'  => $dateorder
    );
}

$this->db->insert_batch('po_order', $orders);
echo $this->db->insert_id(); //will return the first insert array

I can't spot where's my error. My last option is to get it using a query. I also did mysql_insert_id() but always returns to 0.

http://stackoverflow.com/questions/1440360/lastinsertid-from-a-bulk-insert — Mahavir Munot, Aug 22 '13 at 05:53
http://stackoverflow.com/questions/15919289/how-to-retrieve-all-last-inserted-rows-ids-in-mysql-php — Mahavir Munot, Aug 22 '13 at 05:55
Adding the number of records u have inserted to the first id returned will help unless you are not doing smthg strange (like you have not deleted previous records in your database). Because insert_batch will not give you last insert id. — plain jane, Aug 22 '13 at 06:10
Hope this helps..... http://stackoverflow.com/questions/1285231/retrieving-the-last-inserted-ids-for-multiple-rows — plain jane, Aug 22 '13 at 06:11

score 5 · Answer 1 · answered Dec 17 '13 at 08:09

5

I think the best way would be to use the batch insert instead of individual inserts in a loop for performance , but to get the last insert id, ADD the First Insert ID & the Affected Rows.

$this->db->insert_batch('po_order', $orders);
$total_affected_rows = $this->db->affected_rows();
$first_insert_id = $this->db->insert_id();

$last_id = ($first_insert_id + $total_affected_rows - 1);

answered Dec 17 '13 at 08:09

Sohail

2,058
7
33
57

1

This technique is very easy and faster; – Kabir Hossain Mar 03 '16 at 10:03
2019 and still usefull. Thanks bro! – MONSTEEEER Oct 30 '19 at 08:18

score 3 · Accepted Answer · answered Aug 22 '13 at 07:11

You will need to do something like this,

$insertIds  = array();
for ($x = 0; $x < sizeof($filtername); $x++) {
    $orders = array(
        'poid'              => null,
        'order_id'          => $poid,
        'item_desc'         => $filtername[$x],
        'item_qty'          => $filterquantity[$x],
        'item_price'        => $filterprice[$x],
        'total'             => $filtertotal[$x],
        'cash_on_delivery'  => $val_delivery,
        'is_check'          => $val_check,
        'bank_transfer'     => $val_transfer,
        'transaction_date'  => $dateorder
    );
    $this->db->insert('po_order', $orders);
    $insertIds[$x]  = $this->db->insert_id(); //will return the first insert array
}
print_r($insertIds); //print all insert ids

Error in getting the last inserted ID of a query using batch insert in CodeIgniter

2 Answers2

Linked