XiLab XiLab - 3 months ago 21
MySQL Question

Wordpress - use $wpdb->update and limit 1

It's probably a stupid question but I'm trying to use

$wpdb->update
instead of $wpdb->query but I'm not sure how to use limit 1. So instead of

$wpdb->query("update {$wpdb->prefix}vp_pms_group_users set read = '1', seen = '1', time_seen = '{$date_time_seen}' where message_id = '{$last_message_id}' and group_id = '{$group_id}' and to_username = '{$session_uid}' and read = '0' limit 1");


I've tried with

$wpdb->update($wpdb->prefix . "vp_pms_group_users", array(
'read' => '1',
'seen' => '1',
'time_seen' => $date_time_seen,
),
array(
'message_id' => $last_message_id,
'group_id' => $group_id,
'to_username' => $session_uid,
'read' => '0',
),
LIMIT 1 //????
);


Should I use limit after the array or inside it?
Thanks.

Answer

Here's an answer, it's not pretty, but based on looking through the method chain within $wpdb it's possibly the only way to achieve what you're after, and still use $wpdb->update().

Step 1. Make your update with a unique parameter which you'll later replace, note that this must be the last parameter passed to the WHERE clause, otherwise replacing it with LIMIT 1 will cause a syntax error in your SQL statement.

Something like:

$wpdb->update(
    $wpdb->prefix . "vp_pms_group_users", 
    array(...),
    array(
        ...
        'MyReplacementLimit' => 1
    )
);

This should give you an SQL statement like so:

UPDATE vp_pms_group_users SET ... WHERE ... AND MyReplacementLimit = 1;

Step 2: Now you can use the query filter to replace that fake clause with a limit...

add_filter('query', function ($query) {
    return str_replace('AND MyReplacementLimit = 1', 'LIMIT 1', $query);
});

This is untested and is based purely on reading through the code available in $wpdb. It may need a few tweaks to get working correctly.

A simpler solution: You could always just use SQL directly, so long as you're using $wpdb->prepare() it's probably easier to read, and more understandable than the above approach.