$wpdb->update() always need a second try

Question

$wpdb->update() always need a second try

kolja

2022年2月16日 13:43

I will use a custom endpoint of REST APi to update fields in a custom table. Therefore, i wrote this function:

function update_table_single($request = null){
    global $wpdb;
    $data_key = trim($request['data_key']);
    $data_value = trim($request['data_value']);
    $where_key = trim($request['where_key']);
    $where_value = trim($request['where_value']);
    $table = trim($request['table']); 

    $data = [ $data_key = $data_value ];  
    $where = [ $where_key = $where_value ]; 
    $wpdbupdate = $wpdb-update($wpdb-prefix.$table, $data, $where);
 
    // $wpdbupdate =  $wpdb-query(
    //     $wpdb-prepare( UPDATE $wpdb-prefix.$table SET $data_key = %s WHERE $where_key = %d, $data_value, $where_value )
    // );

    $wpdb-show_errors = TRUE;
    $wpdb-suppress_errors = FALSE;
    if($wpdbupdate === 0){
        $response['error'] = $wpdb-last_error;
        $response['query'] = $wpdb-last_query;
        $response['status'] = 200;
    }else{
        $response['error'] = $wpdb-last_error;
        $response['query'] = $wpdb-last_query;
        $response['status'] = 400;
    }

    return new WP_REST_Response($response, 123);
}

But the strange thing is, that the first try to update the field with a new value always returns false (status:400).

The second try (just send POST request again) will update the field as expected.

In both cases, the queries are exactly the same:

UPDATE izq5I_my_customer SET user_color = 'red' WHERE user_id = 2169

I also tried the prepare() function, but this gives the same results.

What else can i try to find the reason for this strange behavior?

Topic wp-update-post wpdb plugin-development Wordpress

Category Web

Oleksii · Accepted Answer · 2022年2月16日 13:43

Short answer:
You have a logical error in your code, this condition is wrong:

 if($wpdbupdate === 0)

Replace it with:

if($wpdbupdate >= 0)

and it will work as expected.

Long explanation:
$wpdb->update method returns the number of rows updated, which is 1 in your case, so your code continues to the next block and status 400 is assigned.

See WP documentation on $wpdb->update: https://developer.wordpress.org/reference/classes/wpdb/update/

Return #
(int|false) The number of rows updated, or false on error.

Your code only returns status 200 when nothing is updated and $wpdb->update returns zero, and the strange behaviour goes by this scenario:

table row updated, the update returns 1, which triggers status 400
nothing is updated on second attempt, the update returns 0, which triggers status 200

To fix this, change your if condition to

if($wpdbupdate >= 0)

to return status 200 when a row is updated or ignored (because the update contains value equal to the existing one), or

if($wpdbupdate > 0)

to return status 200 only if a value is changed

$wpdb->update() always need a second try

About