125

My WordPress plugin has a table with a AUTO_INCREMENT primary key field called ID. When a new row is inserted into the table, I'd like to get the ID value of the insertion.

The feature is to using AJAX to post data to server to insert into DB. The new row ID is returned in the AJAX response to update client status. It is possible that multiple clients are posting data to server at the same time. So, I have to make sure that each AJAX request get the EXACT new row ID in response.

In PHP, there is a method called mysql_insert_id for this feature.But, it is valid for race condition only if the argument is link_identifier of last operation. My operation with database is on $wpdb. How to extract the link_identifier from $wpdb to make sure mysql_insert_id work? Is there any other way to get the last-inserted-row id from $wpdb?

Thanks.

Maxime
  • 8,645
  • 5
  • 50
  • 53
Morgan Cheng
  • 73,950
  • 66
  • 171
  • 230
  • The link|resource is stored in `$wpdb->dbh`, but it is defined as `protected $dbh;` ...sou you can't access it directly, therefore, use answer below :) – jave.web Jun 04 '16 at 12:38

7 Answers7

244

Straight after the $wpdb->insert() that does the insert, do this: 

$lastid = $wpdb->insert_id;

More information about how to do things the WordPress way can be found in the WordPress codex. The details above were found here on the wpdb class page

Maxime
  • 8,645
  • 5
  • 50
  • 53
jsnfwlr
  • 3,638
  • 2
  • 23
  • 25
  • 1
    Is it like this: `$lastid = $wpdb->$insert_id` ? – Francisco Corrales Morales Feb 11 '14 at 17:23
  • "This function returns false if the row could not be inserted. Otherwise, it returns the number of affected rows (which will always be 1)." From: http://codex.wordpress.org/Function_Reference/wpdb_Class#INSERT_rows – unbreak Mar 25 '15 at 12:59
  • 1
    @unbreak - wrong function there ... you're reading about wpdb->insert( $table, $data, $format ); – jsnfwlr Apr 29 '15 at 03:34
  • 6
    It is also useful to know that if you use $wpdb->query, it will still assign insert_id. – Dave Scotese Aug 20 '15 at 22:27
  • 2
    was not necessary, but I think it improves a bit, statement highlighted, and removed indentation as it's not required for single line. – kamal pal Jun 02 '16 at 04:37
  • Is this using `LAST_INSERT_ID()` specifically in the sense it functions in the ways described [here](https://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html)? – Jordan Oct 26 '17 at 16:51
  • Thank You It was useful for me – Rutvi Trivedi Feb 06 '20 at 11:20
  • What if client B does $wpdb->insert() in between client's A $wpdb->insert() and $wpdb->$insert_id? In that case client A will get B's id. Can that happen? – blaze9 Jun 29 '20 at 15:30
  • @blaze9 it shouldn't, because the wpdb class should be mitigating that race condition for you, if you absolutely have to get that ID back and are experiencing these race conditions, you could then use the params you used in the insert to select the row back out and get the ID that way – jsnfwlr Jun 30 '20 at 22:28
  • It's probably best to check if `$wpdb->insert()` is not `false` before checking `$wpdb->insert_id`. – Gavin Feb 08 '21 at 09:39
18

This is how I did it, in my code

 ...
 global $wpdb;
 $query =  "INSERT INTO... VALUES(...)" ;
 $wpdb->query(
        $wpdb->prepare($query)
);
return $wpdb->insert_id;
...

More Class Variables

Francisco Corrales Morales
  • 3,676
  • 1
  • 37
  • 64
  • 1
    "This function returns false if the row could not be inserted. Otherwise, it returns the number of affected rows (which will always be 1)." from: http://codex.wordpress.org/Function_Reference/wpdb_Class#INSERT_rows – unbreak Mar 25 '15 at 13:01
  • 1
    It works. The `$wpdb->query` returns the len of affected rows and `$wpdb->insert_id` has the last inserted id. Thanks! – Fabio Montefuscolo Sep 29 '15 at 20:26
  • This is better for me, because I want to capture inserts that will otherwise return an error due to duplicate column values on a unique column. There is no way to `INSERT IGNORE` with `$wpdb->insert` unfortunately. – Solomon Closson Feb 15 '18 at 01:50
  • @unbreak - Looks like it's returning the `insert_id` to me, not the number of affected rows. – Solomon Closson Feb 15 '18 at 01:58
3

I needed to get the last id way after inserting it, so 

$lastid = $wpdb->insert_id;

Was not an option.

Did the follow:

global $wpdb;
$id = $wpdb->get_var( 'SELECT id FROM ' . $wpdb->prefix . 'table' . ' ORDER BY id DESC LIMIT 1');
Marco Floriano
  • 318
  • 2
  • 6
  • 14
2

Get the last inserted row id in WP like this.

global $wpdb;
$order = ['product_name'=>'Computer', 'os_system'=>'Linux'];
$wpdb->insert('wp_product_orders', $order );
$last_inserted_id = $wpdb->insert_id;
infomasud
  • 2,263
  • 1
  • 18
  • 12
0

Something like this should do it too : 

$last = $wpdb->get_row("SHOW TABLE STATUS LIKE 'table_name'");
$lastid = $last->Auto_increment;
kamal pal
  • 4,187
  • 5
  • 25
  • 40
Martin
  • 49
  • 1
  • 11
    Wouldn't this cause a problem if two records were inserted at almost the exact same time by two different processes? Both processes could insert at the same time (or close enough to the same time) so that the auto_increment would return the same number for both processes. – Michael Khalili Sep 08 '13 at 05:54
-1

just like this :

global $wpdb;
$table_name='lorem_ipsum';
$results = $wpdb->get_results("SELECT * FROM $table_name ORDER BY ID DESC LIMIT 1");
print_r($results[0]->id);

simply your selecting all the rows then order them DESC by id , and displaying only the first

Ahmed Mansour
  • 500
  • 4
  • 14
-7

Putting the call to mysql_insert_id() inside a transaction, should do it:

mysql_query('BEGIN');
// Whatever code that does the insert here.
$id = mysql_insert_id();
mysql_query('COMMIT');
// Stuff with $id.
Ollie Saunders
  • 7,787
  • 3
  • 29
  • 37