How to get ID of last inserted or updated row when using $wpdb->query

Question

I am inserting data in a Wordpress plugin table using a custom query using the $wpdb->query() method. I use a custom query because I need to run ON DUPLICATE KEY UPDATE. Is there a way to retrieve the ID of the latest inserted or updated row when using this method?

When doing a native $wpdb->insert() or $wpdb->replace() the ID is stored in the $wpdb->insert_id variable, but this doesn't seem to work for queries executed using $wpdb->query().

The only alternatives which come to mind are to use $wpdb->replace() or to perform a SELECT query to get the ID of the last row in the table, however the former solution implies running a DELETE before the INSERT when a duplicate key is found, while the latter is prone to race conditions.

Is there some other possible solution to this problem?

Answer 1

You can still retrieve the ID of the affected row using MySQL's LAST_INSERT_ID() function within your query.

I don't know what your query looks like, but here's an exmaple:

global $wpdb;

// Your data and query
$table_name = $wpdb->prefix . 'your_table';
$data = [
    'column1' => 'value1',
    'column2' => 'value2',
];
$unique_column = 'column1';
$id_column = 'id';

// Construct the query
$query = $wpdb->prepare(
    "INSERT INTO $table_name (column1, column2)
    VALUES (%s, %s)
    ON DUPLICATE KEY UPDATE column2 = VALUES(column2), $id_column = LAST_INSERT_ID($id_column)",
    $data['column1'],
    $data['column2']
);

// Execute the query
$wpdb->query($query);

// Get the ID of the last inserted or updated row
$last_id = $wpdb->get_var("SELECT LAST_INSERT_ID()");

Since LAST_INSERT_ID() is tied to the current connection/session, it is safe from race conditions, and use it with confidence.