Codeigniter and Oracle get the last_id of insert query

Question

I have model like this:

 function data_input($data, $file_upload) {
    $this->db->set('ID_KEY', "LASTID_SEQ.NEXTVAL", FALSE); //false escape
    $this->db->insert('FIRST_TABLE', $data);
    $this->db->set('ID_KEY', "LASTID_SEQ.NEXTVAL -1", FALSE); //false escape
    $this->db->insert('SECOND_TABLE', $file_upload);
    return true;
}

And I want to send ID_KEY's value to controller and using it to update database based on it's ID_KEY.

My problem is, I can generate value of ID_KEY which is same in FIRST_TABLE and SECOND_TABLE, but I cant send the value to the controller.

or, Can I use another method to get the value of "insert_id()" of insert in oracle active record. (or using $this->db->query?)

Answer 1

In Codeigniter; Invoking $this->db->insert_id() returns the ID of last inserted data. If you plan to return last insert id of both queries I would do it like this:

function data_input($data, $file_upload) {
$insert_id_collection = array();

// first table query
$this->db->set('ID_KEY', "LASTID_SEQ.NEXTVAL", FALSE); //false escape
$this->db->insert('FIRST_TABLE', $data);
$insert_id_collection['first_table_name'] = $this->db->insert_id();

// second table query
$this->db->set('ID_KEY', "LASTID_SEQ.NEXTVAL -1", FALSE); //false escape
$this->db->insert('SECOND_TABLE', $file_upload);
$insert_id_collection['second_table_name'] = $this->db->insert_id();

// this will contain to last insert ID;
return $insert_id_collection;

}

The reason why I put and index of the array to the table name for you to identify which last insert ID is that;

Hopes it helps you; Happy coding