CodeIgniter Forums
Active record last insert ID - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Active record last insert ID (/thread-10487.html)



Active record last insert ID - El Forum - 08-01-2008

[eluser]Unknown[/eluser]
I have a table with an auto_incrementing column that's set as the primary key. Is it possible to get the number from this column after doing an insert with active record without having to run another query? I couldn't find anything in the docs or the forums.

Thanks!
Dan


Active record last insert ID - El Forum - 08-01-2008

[eluser]Adam Owen[/eluser]
It sure is.

Code:
$this->db->insert_id()

If you're redirecting after an insert then you can store it in flashdata.

Code:
$this->session->set_flashdata('id', $this->db->insert_id());
redirect('home/index');

This and some other helpful commands are in this page of the user guide - http://ellislab.com/codeigniter/user-guide/database/helpers.html


Active record last insert ID - El Forum - 08-01-2008

[eluser]Unknown[/eluser]
Thanks so much! I completely missed that on the helpers page and it's right at the top. Just started using CI and my head is swimming =)

Thanks,
Dan


Active record last insert ID - El Forum - 08-03-2008

[eluser]meovino[/eluser]
Just wondering... how safe is this in a multi-user environment? Is there a chance that a second insert can occur before insert_id() can be called?


Active record last insert ID - El Forum - 08-03-2008

[eluser]Unknown[/eluser]
@meovino

$this->db->insert_id() is a simple wrapper to the PHP MySQL classes mysql_insert_id() function. The only difference is that is suppresses any errors that may come from the call by adding the @ in front of it, and it passes the connection id (which is optional). Here is the code I am referring to from the mysql_driver.php CI file:

Code:
/**
* Insert ID
*
* @access public
* @return integer
*/
function insert_id()
{
  return @mysql_insert_id($this->conn_id);
}

However I think its important to note the difference between the PHP function that this wraps and the direct MySQL function of retrieving the last auto-incremented value for the session. An important difference to note is the following:

In the instance when multiple rows are created with a single insert statement LAST_INSERT_ID() (the direct MySQL function) will return the first inserted rows ID, whereas MYSQL_INSERT_ID() (the PHP function the CI wraps up with $this->db->insert_id()) returns the ID from the last row inserted.

See the following documentation references:
http://dev.mysql.com/doc/refman/5.0/en/mysql-insert-id.html
Quote:In the case of a multiple-row INSERT statement, mysql_insert_id() returns the first automatically generated AUTO_INCREMENT value; if no such value is generated, it returns the last explicit value inserted into the AUTO_INCREMENT column.

http://us.php.net/mysql_insert_id
Quote:Note: The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries.

Thanks,
Rob