CodeIgniter Forums
How to get id after insert data - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: How to get id after insert data (/thread-20709.html)

Pages: 1 2


How to get id after insert data - El Forum - 07-19-2009

[eluser]PEN Vannak[/eluser]
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,


How to get id after insert data - El Forum - 07-19-2009

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

Try this code:
Code:
$id = $this->db->insert_id();



How to get id after insert data - El Forum - 07-19-2009

[eluser]Mareshal[/eluser]
why don't you get it before inserting data, abd if query was successful just make $id++ . good luck


How to get id after insert data - El Forum - 07-19-2009

[eluser]Yorick Peterse[/eluser]
[quote author="Mareshal" date="1248016298"]why don't you get it before inserting data, abd if query was successful just make $id++ . good luck[/quote]

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


How to get id after insert data - El Forum - 07-19-2009

[eluser]TheFuzzy0ne[/eluser]
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.


How to get id after insert data - El Forum - 07-19-2009

[eluser]darkhouse[/eluser]
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:

Code:
//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.


How to get id after insert data - El Forum - 07-19-2009

[eluser]davidbehler[/eluser]
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.


How to get id after insert data - El Forum - 07-19-2009

[eluser]darkhouse[/eluser]
Oh, yeah you're right. Brain fart. Thanks.


How to get id after insert data - El Forum - 09-11-2010

[eluser]Wayne Smallman[/eluser]
[quote author="darkhouse" date="1248037559"]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.[/quote]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?


How to get id after insert data - El Forum - 09-11-2010

[eluser]juanvillegas[/eluser]
Well, a better aproach would be using synchronized methods and concurrency, but i'm not sure PHO (and codeigniter) support for this.