168
votes

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

10
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.pbarney
Anyone please mark it as duplicate.kishor10d

10 Answers

299
votes

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();
68
votes

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();
}
29
votes
$id = $this->db->insert_id();
10
votes

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();
3
votes

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

0
votes

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

0
votes

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']
        ]);
0
votes
**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;
}
0
votes

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()

-1
votes

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