Problem with Last inserted record id

[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.

[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]

[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

[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]

[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.