How to get last insert id after insert query inside of a transaction in CodeIgniter

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.

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
– pbarney, Commented Feb 18, 2016 at 16:50
Non-transactional, but related: CodeIgniter activerecord, retrieve last insert id? — Shaiful Islam
– Shaiful Islam, Commented May 13, 2017 at 13:21

Vad.Gut · Accepted Answer · 2015-06-25 08:35:26Z

321

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

edited Jun 25, 2015 at 8:35

Vad.Gut

5311 gold badge5 silver badges20 bronze badges

answered May 8, 2013 at 12:15

Sudz

4,3082 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Abraham Philip Over a year ago

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

Shekhar Joshi Over a year ago

@Abraham what about concurrent inserts?

Abraham Philip Over a year ago

@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.

Shekhar Joshi Over a year ago

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

Sebastianb Over a year ago

@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.

|

pbarney · Accepted Answer · 2016-02-17 15:32:24Z

70

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

edited Feb 17, 2016 at 15:32

pbarney

2,9434 gold badges41 silver badges55 bronze badges

answered May 8, 2013 at 12:55

Crowlix

1,2699 silver badges19 bronze badges

2 Comments

Pimin Konstantin Kefaloukos Over a year ago

what about concurrent inserts?

Abraham Philip Over a year ago

@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?

Simon Carlson · Accepted Answer · 2013-05-08 12:22:12Z

29

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

answered May 8, 2013 at 12:22

Simon Carlson

1,9895 gold badges24 silver badges36 bronze badges

Comments

cgaldiolo · Accepted Answer · 2021-07-15 10:14:26Z

11

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

edited Jul 15, 2021 at 10:14

cgaldiolo

3,8971 gold badge22 silver badges19 bronze badges

answered Apr 26, 2015 at 10:59

Md.Jewel Mia

3,4573 gold badges22 silver badges24 bronze badges

Comments

Tristan CHARBONNIER · Accepted Answer · 2019-10-06 15:02:28Z

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

Antony · Accepted Answer · 2021-07-06 14:45:04Z

2

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

answered Jul 6, 2021 at 14:45

Antony

4,4221 gold badge34 silver badges43 bronze badges

Comments

Kishor Pant · Accepted Answer · 2019-04-22 03:21:42Z

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

Fabus · Accepted Answer · 2019-06-17 14:12:22Z

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']
        ]);

Collectives™ on Stack Overflow

How to get last insert id after insert query inside of a transaction in CodeIgniter

8 Answers 8

7 Comments

2 Comments

Comments

$this->db->insert_id()

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

7 Comments

2 Comments

Comments

$this->db->insert_id()

Comments

Comments

Comments

Comments

Comments

Linked

Related