0

I want to update multiple rows in one query using wpdb->update. I get no error in the debug.log file but only the last row is updated.

$my_update = $wpdb->update( $wpdb->prefix . 'my_settings',
            array( 'value' => $tmp_mail_smtp,
                   'value' => $tmp_mail_smtp_host,
                   'value' => $tmp_mail_smtp_auth ),
            array( 'name' => 'mail-smtp',
                   'name' => 'mail-smtp-host',
                   'name' => 'mail-smtp-auth' ) );

The table structure is (eg):

id - name - value
1 - mail-smtp - yes
2 - mail-smtp-host - smtp.domain.tld
3 - mail-smtp-auth - yes

I want to update the value at name. Am i doing something wrong?

Update:

With $wpdb->last_query; i get UPDATE settings SET value = 0 WHERE name = mail-smtp-auth It seems like he not even recognize the other. $wpdb->last_result and $wpdb->last_error are both empty.

2 Answers 2

1

I googled a lot the last days and a few times i´ve seen postings that say that the $wpdb->update function doens´t support multiple updates and that this is not well documented. -> Sorry i don´t have links anymore...

I think that this is true because i couldn´t get it to work and i found not a single example witch works this way. So i use this way now to update multiple rows and still be able to handle errors.

The function:

function update_queries( $queries ) {
    global $wpdb;

    // set array
    $error = array();

    // run update commands
    foreach( $queries as $query ) {
        $query = str_replace( '[wp-prefix]', $wpdb->prefix, $query );
        $last_error = $wpdb->last_error;
        $wpdb->query( $query );

        // fill array when we have an error
        if( (empty( $wpdb->result ) || !$wpdb->result ) && !empty( $wpdb->last_error ) && $last_error != $wpdb->last_error ) {
                $error[]= $wpdb->last_error." ($query)";
        }
    }

    // when we have an error
    if( $error ) {
        return $error;

    // when everything is fine
    }else{
        return false;
    }
}

If we want to update a few things:

// update database
$queries = array();
$queries[] = "UPDATE `[wp-prefix]table` SET `value` = '$value'  WHERE `name` = 'name';";
$queries[] = "UPDATE `[wp-prefix]table` SET `value` = '$value2'  WHERE `name` = 'name2';";
$error = update_queries( $queries );

// if we have an error
if( !empty( $error ) ) {
    .....

// when everything is fine
}else{
    .....
}
0

Your syntax looks okay. My guess is your first two variables are empty.

Try hardcoding in values to see if those save.

$tmp_mail_smtp      = 'Test Mail SMTP';
$tmp_mail_smtp_host = 'Test Mail SMTP Host';
$tmp_mail_smtp_auth = 'Test Mail SMTP Auth';

$my_update = $wpdb->update( $wpdb->prefix . 'my_settings',
    array( 'value' => $tmp_mail_smtp,
           'value' => $tmp_mail_smtp_host,
           'value' => $tmp_mail_smtp_auth,
    ),
    array( 'name' => 'mail-smtp',
           'name' => 'mail-smtp-host',
           'name' => 'mail-smtp-auth',
    )
);

Update #1

Since the above doesn't work for you, remove the $wpdb->update code and try updating each option separately and see if that works.

if ( $settings_message == '0' ) {
  update_option( 'role', $tmp_role );
  update_option( 'mail-smtp', $tmp_mail_smtp );
  update_option( 'mail-smtp-host', $tmp_mail_smtp_host );
  update_option( 'mail-smtp-auth', $tmp_mail_smtp_auth );
}

P.S. Since this is a wp-admin page, I'd read more about the Settings API which provides a more structured way of adding/managing option pages.

6
  • Even if i do this only the last one is updated. If i delete the last one only the second (now the last again) is updated. Oct 25, 2016 at 10:05
  • What add_action are you using to trigger this or where are you calling this? Could you update your code above to include more?
    – cowgill
    Oct 25, 2016 at 12:41
  • You can find the whole code here: paste.ofcode.org/345EKB5MKsGffd6DUgLEdh3 It´s too large to paste it here. Oct 25, 2016 at 16:53
  • With $wpdb->last_query; i get UPDATE settings SET value = 0 WHERE name = mail-smtp-auth It seems like he not even recognize the other. $wpdb->last_result and $wpdb->last_error are both empty. Oct 25, 2016 at 17:43
  • @wassereimer Thanks for the code link. Didn't realize it was code in wp-admin. I've updated my answer above so try that.
    – cowgill
    Oct 26, 2016 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.