CodeIgniter Forums
Problem with Last inserted record id - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Problem with Last inserted record id (/showthread.php?tid=12685)

Problem with Last inserted record id - El Forum - 10-27-2008

[eluser]Joseph1982[/eluser]
Hello,

I have tested the '$this->db->insert_id();' function but it not returns the correct record id. I have tested the process with multiple insertion in a single query. The code is below:

Code:
class Mytest extends Controller
{

    function __construct()
    {
        parent::Controller();
    
        $this->load->database();
    }

    function index()
    {
        $query    =    "INSERT INTO `test` ( `id` , `name` ) VALUES ('', 'Reneesh'), ('', 'Shaiju')";

        $this->db->query($query);


        echo $this->db->insert_id();

    }
}

Please let me know your suggestions..

Thank You.

Problem with Last inserted record id - El Forum - 10-28-2008

[eluser]Jilani Jidni[/eluser]
its happened because mysql last generated id is maintained in the server on a per-connection basis.


[quote author="Aniesh" date="1225184685"]Hello,

I have tested the '$this->db->insert_id();' function but it not returns the correct record id. I have tested the process with multiple insertion in a single query. The code is below:

Code:
class Mytest extends Controller
{

    function __construct()
    {
        parent::Controller();
    
        $this->load->database();
    }

    function index()
    {
        $query    =    "INSERT INTO `test` ( `id` , `name` ) VALUES ('', 'Reneesh'), ('', 'Shaiju')";

        $this->db->query($query);


        echo $this->db->insert_id();

    }
}

Please let me know your suggestions..

Thank You.[/quote]

Problem with Last inserted record id - El Forum - 10-28-2008

[eluser]Colin Williams[/eluser]
So, does it return the id of the first of multiple inserts or the last? Once you know, you could iterate the difference to get all insert ids

Problem with Last inserted record id - El Forum - 10-28-2008

[eluser]Jilani Jidni[/eluser]
yes thats a good idea. it return the first of multiple inserts.

[quote author="Colin Williams" date="1225192780"]So, does it return the id of the first of multiple inserts or the last? Once you know, you could iterate the difference to get all insert ids[/quote]

Problem with Last inserted record id - El Forum - 10-28-2008

[eluser]Joseph1982[/eluser]
[quote author="Colin Williams" date="1225192780"]So, does it return the id of the first of multiple inserts or the last? Once you know, you could iterate the difference to get all insert ids[/quote]

It will return the first id.

By using the $db->affected_rows(), we can find the total number of insertion and the last inserted is :

Code:
/** MySql function only returns the ID of the first record inserted, not the last one*/
        // Get the last record id inserted
        $last_insert_id        =    $this->db->insert_id();
        $affected_rows        =    $this->db->affected_rows();
        $affected_rows        =    $affected_rows -1;
        $last_seen_feed_item_id    =    $last_insert_id + $affected_rows;


This works in my case.

Thank you.