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

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, Feb 18 '16 at 16:50
Possible duplicate of [CodeIgniter activerecord, retrieve last insert id?](http://stackoverflow.com/questions/1985967/codeigniter-activerecord-retrieve-last-insert-id) — Shaiful Islam, May 13 '17 at 13:21

score 295 · Accepted Answer · edited Jun 25 '15 at 08:35

295

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 '15 at 08:35

Vad.Gut

503
1
5
18

answered May 08 '13 at 12:15

Sudz

3,998
1
15
23

1

Unneeded use of transactions. @Crowlix's answer is more concise. – Abraham Philip Apr 25 '15 at 16:37
1

@Abraham what about concurrent inserts? – Shekhar Joshi Jun 15 '15 at 12:32
3

@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. – Abraham Philip Jul 20 '15 at 20:20
How does codeigniter know which rows were added by a particular object? – Shekhar Joshi Jul 21 '15 at 06:48
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()? – DMS-KH Aug 28 '15 at 09:56
what to do if my first column of table is not id and pk? – Imran Qamer Dec 04 '15 at 09:36
3

@ShekharJoshi It's not about objects, CI's insert_id() returns the last inserted id as per MySQL's [last_insert_id()](https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_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. – Sebastianb Mar 04 '16 at 20:40

score 66 · Answer 2 · edited Feb 17 '16 at 15:32

66

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 '16 at 15:32

pbarney

2,172
4
29
44

answered May 08 '13 at 12:55

Crowlix

1,219
9
19

1

what about concurrent inserts? – Pimin Konstantin Kefaloukos Sep 04 '14 at 15:49
10

@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? – Abraham Philip Jul 20 '15 at 20:22

score 29 · Answer 3 · answered May 08 '13 at 12:22

29

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

answered May 08 '13 at 12:22

Simon Carlson

1,681
5
19
34

score 10 · Answer 4 · edited Jun 20 '20 at 09:12

10

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 Jun 20 '20 at 09:12

Community

1
1

answered Apr 26 '15 at 10:59

Md.Jewel Mia

2,724
3
16
20

3

Please don't give only a link but try to summarise the solution here – abarisone Apr 26 '15 at 11:18

Tristan CHARBONNIER · Answer 5 · 2019-10-06T15:02:28.240

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

score 0 · Answer 6 · answered Apr 22 '19 at 03:21

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

score 0 · Answer 7 · answered Jun 17 '19 at 14:12

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

score 0 · Answer 8 · answered Sep 18 '19 at 17:11

**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;
}

score -1 · Answer 9 · answered Dec 14 '17 at 05:55

-1

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

answered Dec 14 '17 at 05:55

Pawan Kr

11
5

buddy you can use above for get last insert id – Pawan Kr Dec 14 '17 at 08:42
2

duplicate answer – Rohit Dhiman Dec 11 '18 at 11:50

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

9 Answers9

$this->db->insert_id()

Linked

Related