How to get last inserted row ID from wordpress dat

Question

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.

Answer 1

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

Answer 2

Something like this should do it too :

$last = $wpdb->get_row("SHOW TABLE STATUS LIKE 'table_name'");
$lastid = $last->Auto_increment;

Answer 3

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.

Answer 4

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