EllisLab text mark
Advanced Search
     
How to get id after insert data
Posted: 19 July 2009 05:39 AM
Avatar
Joined: 2009-06-17
22 posts

Dear CodeIgniter Members,

I want to get the id a the field that I just inserted to the database at the recent.

How could I write the code in php?

Have any one know?

I’m waiting to get the answer from you soon.

Best Regards,

 Signature 

PEN Vannak,
  Email:vannakpen@gmail.com
  URL:http://vannak.comli.com

 
Posted: 19 July 2009 06:19 AM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2008-01-03
728 posts

Have a look at the guide: http://ellislab.com/codeigniter/user-guide/database/helpers.html

Try this code:

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

Blog - Twitter

DBlog

MeNeedz: Auth - Cloud - Password - Search - Shoutbox - Akismet -
Twitter - Visitor tracking

 
Posted: 19 July 2009 07:11 AM   [ # 2 ]   [ Rating: 0 ]
Joined: 2009-06-18
279 posts

why don’t you get it before inserting data, abd if query was successful just make $id++ . good luck

 
Posted: 19 July 2009 07:56 AM   [ # 3 ]   [ Rating: 0 ]
Joined: 2009-05-02
553 posts
Mareshal - 19 July 2009 11:11 AM

why don’t you get it before inserting data, abd if query was successful just make $id++ . good luck

Because you can’t since ID’s can get messed up.

 
Posted: 19 July 2009 09:39 AM   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2008-04-25
4956 posts

To elaborate further on Yorick’s comment, this would cause a race condition where simultaneous requests would both calculate the same ID, and cause an error when inserted. Because of this, it’s best to use an auto incremented field, and grab the ID once the row has been inserted.

 Signature 

http://www.phptherightway.com

Remember the 8 Ps: Perfect Planning and Prior Preparation Prevents Piss-Poor Performance.

 
Posted: 19 July 2009 01:05 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2008-08-19
340 posts

Just adding my 2 cents.  I once had an application (not code igniter, this is like 5 years ago) give me a terrible problem where I had 2 people register at the exact same time.  I was returning mysql_insert_id() but it returned the same id to both of them.  Since I was doing stuff with the id after the user had registered, it really screwed things up, which I had to go in and fix manually. 

I still use $this->db->insert_id() as I think that occurrence was a 1-in-a-million fluke, but the solution I had to use, and still sometimes use today even though it’s a little more taxing, is to run a select right after the insert to grab the proper id.  Something like this:

//users_model.php
function insert_user($username$password$email){
   $data 
= array(
      
'username' => $username,
      
'password' => $password,
      
'email' => $email
   
);
   
$this->db->insert('users'$data);

   foreach(
$data as $key => $val){
      $this
->db->where($key$val);
   
}
   $this
->db->order_by('date_joined''desc'); //date_joined is a TIMESTAMP, default CURRENT_TIMESTAMP
   
$query $this->db->get('users'1);
   return 
$query->row()->id;

I don’t know if that will ever help anyone, just sharing one of my more memorable experiences, and a possible solution in case it ever happens to anyone.

Ever since that day, a pipe dream of mine has always been that it would be nice if running an INSERT query would return the primary key for that new record.  Then you could just do $id = mysql_query(“INSERT INTO…”); return $id;  Or if it was an optional paramater, like $id = mysql_query(“INSERT INTO…”, $link, TRUE);  ... anyways, I’m rambling.  Pipe dream.

 
Posted: 19 July 2009 02:32 PM   [ # 6 ]   [ Rating: 0 ]
Avatar
Joined: 2008-01-03
728 posts

Kinda off topic but you can slightly improve your code by removing the foreach loop, because $this->db->where() works with an associative array aswell. Simply passing $data first parameter should be fine.

 Signature 

Blog - Twitter

DBlog

MeNeedz: Auth - Cloud - Password - Search - Shoutbox - Akismet -
Twitter - Visitor tracking

 
Posted: 19 July 2009 02:42 PM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2008-08-19
340 posts

Oh, yeah you’re right.  Brain fart.  Thanks.

 
Posted: 11 September 2010 05:21 PM   [ # 8 ]   [ Rating: 0 ]
Avatar
Joined: 2010-02-09
226 posts
darkhouse - 19 July 2009 05:05 PM

Just adding my 2 cents.  I once had an application (not code igniter, this is like 5 years ago) give me a terrible problem where I had 2 people register at the exact same time.  I was returning mysql_insert_id() but it returned the same id to both of them.

This is the problem I’m working through right now, and is the reason I need to know more about how $this->db->insert_id() works.

I am surprised this problem hasn’t yet been resolved by the database vendors. Surely a simple solution would be to serialize each transaction, to identify them for the purposes of post processing?

 
Posted: 11 September 2010 05:52 PM   [ # 9 ]   [ Rating: 0 ]
Avatar
Joined: 2010-08-24
22 posts

Well, a better aproach would be using synchronized methods and concurrency, but i’m not sure PHO (and codeigniter) support for this.

 Signature 

PDEV

 
Posted: 11 September 2010 06:26 PM   [ # 10 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

It shouldn’t happen that two processes that insert a record in the same table get the same id.

The database engine makes sure all inserts with auto_increment columns are atomic, you can’t have two records with the same id (you probably have that field as primary index, so the second insert would abort with a duplicate key error anyway).

The insert_id field is a property of the connection, even if you work with pooled connections only one process is ever using a connection at any given time.

Offcourse, when you think you can outsmart the database engine and try to use your own logic to determine the insert id (like suggested above), you will have to use multiple queries, which severly increases that chance that another process inserts the record you’re process thinks it’s inserting. To do this safe, you would need a write lock on the table during your transaction, something to avoid at all costs if you value the performance of your application.

 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!

 
Posted: 11 September 2010 06:29 PM   [ # 11 ]   [ Rating: 0 ]
Avatar
Joined: 2010-08-24
22 posts

How a write lock would fix this issue? Suppose:

->write user (by user #1)
->write user (by user #2)
->insert_id request (by user #1)

 Signature 

PDEV

 
Posted: 12 September 2010 05:09 AM   [ # 12 ]   [ Rating: 0 ]
Avatar
Joined: 2010-02-09
226 posts
juanvillegas - 11 September 2010 09:52 PM

Well, a better aproach would be using synchronized methods and concurrency, but i’m not sure PHO (and codeigniter) support for this.

CroNIX reminded me of the resource ID, which is essentially what I was suggesting.

I’m not quite sure how I managed to forget about that, but there you go!

 
Posted: 12 September 2010 07:20 AM   [ # 13 ]   [ Rating: 0 ]
Avatar
Joined: 2008-11-04
4489 posts

@juanvillages,

You haven’t read my post.

The insert_id is a connection property. When user #1 inserts a record, the insert_id property is populated by the driver for user #1’s connection, and the table for which the record was inserted. When user #2 inserts a record in the same table, the same happens, but for that connection. These are two different fields, and populated directly after the insert. In other words, it is not a single value kept per table.

When you then request the insert id, the value stored in the connection is returned. This is still the value stored after user #1’s insert, and is unaffected by other inserts. It will only be overwritten when user #1 inserts another record.

You don’t need a lock for this. You need a lock if you manually try to do this, using ‘SELECT MAX(record_id) FROM table’, adding one to the result, and then insert the record. Because between the select and the insert someone else could have inserted a record.

 Signature 

Me: WanWizard.eu | My company: Exite | Datamapper: DataMapper ORM <= LOOKING FOR A NEW MAINTAINER!