how to get last insert id after insert query in codeigniter active record

Question

I have an insert query (active record style) used to insert the form fields into a MySQL table. I want to get the last auto-incremented id for the insert operation as the return value of my query but I have some problems with it.

Inside the controller:

function add_post(){
    $post_data = array(
        'id'            => '',
        'user_id'   =>  '11330',
        'content'   =>  $this->input->post('poster_textarea'),
        'date_time' => date("Y-m-d H:i:s"),
        'status'        =>  '1'
    );
    return $this->blog_model->add_post($post_data);
}

And inside model:

function add_post($post_data){
    $this->db->trans_start();
    $this->db->insert('posts',$post_data);
    $this->db->trans_complete();
    return $this->db->insert_id();
}

I get nothing as the return of the add_post in model

Millepore

For those who are wondering, db->insert_id() returns false after a db->trans_complete(). Make sure you get your insert_id()'s before you complete the transaction. – Ducal

Possible duplicate of CodeIgniter activerecord, retrieve last insert id? – Gervais

Anyone please mark it as duplicate. – Cindycine

Answer 1

Try this

function add_post($post_data){
   $this->db->insert('posts', $post_data);
   $insert_id = $this->db->insert_id();

   return  $insert_id;
}

In case of multiple inserts you could use

$this->db->trans_start();
$this->db->trans_complete();

Romalda

Unneeded use of transactions. @Crowlix's answer is more concise. – Mankind

@Abraham what about concurrent inserts? – Sclerosed

@ShekharJoshi afaik the insert_id() functions returns the id of the last insert performed by the db object you're using. This should handle concurrent inserts, shouldn't it? Please correct me if I'm wrong. – Mankind

How does codeigniter know which rows were added by a particular object? – Sclerosed

Why do we need to pass an id value to $insert_id variable? does CI3 can return any value if I return a function Ext: return $this->db->insert_id()? – Teleprinter

what to do if my first column of table is not id and pk? – Luxembourg

@ShekharJoshi It's not about objects, CI's insert_id() returns the last inserted id as per MySQL's last_insert_id(), which keeps the last inserted id in a per-connection basis. Because of this, transactions are not needed for last inserted id's. – Arguable

Answer 2

A transaction isn't needed here, this should suffice:

function add_post($post_data) {
    $this->db->insert('posts',$post_data);
    return $this->db->insert_id();
}

Debt

what about concurrent inserts? – Mariande

@mander I believe insert_id() returns the id of the last insert performed by the db object it is called on. Even in the presence of concurrent inserts, wouldn't this mean it always returns the id corresponding to the insert this particular db object made? – Mankind

Answer 3

$id = $this->db->insert_id();

Sulfonal

Answer 4

From the documentation:

$this->db->insert_id()

The insert ID number when performing database inserts.

Therefore, you could use something like this:

$lastid = $this->db->insert_id();

Sapanwood

Please don't give only a link but try to summarise the solution here – Bra

Answer 5

Using the mysqli PHP driver, you can't get the insert_id after you commit.

The real solution is this:

function add_post($post_data){
  $this->db->trans_begin();
  $this->db->insert('posts',$post_data);

  $item_id = $this->db->insert_id();

  if( $this->db->trans_status() === FALSE )
  {
    $this->db->trans_rollback();
    return( 0 );
  }
  else
  {
    $this->db->trans_commit();
    return( $item_id );
  }
}

Source for code structure: https://codeigniter.com/user_guide/database/transactions.html#running-transactions-manually

Apocynthion

Answer 6

It is worth saying that the other answers relate to Codeigniter version 3. The answer in Version 4 (found https://codeigniter.com/user_guide/database/helpers.html) is to use $this->db->insertID()

Shoestring

Answer 7

because you have initiated the Transaction over the data insertion so, The first check the transaction completed or not. once you start the transaction, it should be committed or rollback according to the status of the transaction;

function add_post($post_data){
  $this->db->trans_begin() 
  $this->db->insert('posts',$post_data);
  $this->db->trans_complete();
  if ($this->db->trans_status() === FALSE){
    $this->db->trans_rollback();
    return 0;
  }else{
    $this->db->trans_commit();
    return $this->db->insert_id();
  }
}``

in the above, we have committed the data on the successful transaction even you get the timestamp

Kanal

Answer 8

Just to complete this topic: If you set up your table with primary key and auto increment you can omit the process of manually incrementing the id.

Check out this example

if (!$CI->db->table_exists(db_prefix() . 'my_table_name')) {
    $CI->db->query('CREATE TABLE `' . db_prefix() . "my_table_name` (
  `serviceid` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `hash` varchar(32) NOT NULL,
  `url` varchar(120) NOT NULL,
  `datecreated` datetime NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=" . $CI->db->char_set . ';');

Now you can insert rows

$this->db->insert(db_prefix(). 'my_table_name', [
            'name'         => $data['name'],
            'hash'            => app_generate_hash(),
            'url'     => $data['url'],
            'datecreated'     => date('Y-m-d H:i:s'),
            'active'          => $data['active']
        ]);

Snifter

Answer 9

**Inside Model**
function add_info($data){
   $this->db->insert('tbl_user_info',$data);
   $last_id = $this->db->insert_id();
   return  $last_id;
}

**Inside Controller**
public function save_user_record() {
  $insertId =  $this->welcome_model->save_user_info($data);
  echo $insertId->id;
}

Eruptive

Answer 10

You must use $lastId = $this->db->insert_id();

Stereochromy

buddy you can use above for get last insert id – Stereochromy

duplicate answer – Pullet