Welcome Guest, Not a member yet? Register   Sign In
Active record last insert ID
#1

[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
#2

[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-gui...lpers.html
#3

[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
#4

[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?
#5

[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/m...rt-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




Theme © iAndrew 2016 - Forum software by © MyBB