0

Trying to set a 'removed' date time in a plugin query but I'm not sure how to use UPDATE SET with $wpdb->prepare.

Here's my query:

$cur_date = date('Y-m-d G:i:s');        
$rows_affected = $wpdb->query(
                $wpdb->prepare("
                   UPDATE $table
                   SET ( removed, post_id, user_id, status )
                   VALUES ( %s, %d, %d, %d )
                   "),
                   array(
                    $cur_date,
                    $postid,
                    $userid,
                    0
                )
            ));

Can I set up UPDATE like this using $wpdb? Very new to self crafted DB queries.

If not, how should/can I accomplish this?

Thanx in advance!

EDIT ---------------------------------------------------

New code:

$table = $wpdb->prefix . 'ds_entry_swoons';
$cur_date = date('Y-m-d G:i:s'); 
$rows_affected = $wpdb->query(
                              $wpdb->prepare("
                                             UPDATE {$table}
                                             SET  removed = %s, post_id = %d, user_id = %d, swoon_status = %d
                                             WHERE post_id = $postid AND user_id = $userid;",
                                             $cur_date, $postid, $userid, 0
                                             )
                              ));
  • Just use wpdb's internal update function. It handles sanitization for you. – Andrew Bartel Jan 10 '14 at 16:14
4

I sort of fixed your query. It needs a table and a WHERE condition to prevent changing all rows. Even a LIMIT 1 at the end won't hurt.

$rows_affected = $wpdb->query(
    $wpdb->prepare(
        "UPDATE {$table} SET removed = %s, post_id = %d, user_id = %d, status = %d;",
        $cur_date = date('Y-m-d H:i:s'), $postid, $userid, 0
    ) // $wpdb->prepare
); // $wpdb->query

Just add the WHERE... in the MySQL query. Proper use of prepare is:

$wpdb->prepare($format, $arg1, $arg2, ...); // just like printf()
  • Actually, LIMIT 1 is very dangerous with UPDATE and DELETE statements. This is particularly true if the database is a Master in MySQL Replication setup because there is no guarantee the order of UPDATE/DELETE on the Slave. Warnings about this will be posted in the Master's MySQL error log. Other than that, your answer is essentially correct once @dkmojo adds the WHERE clause. +1 !!! – RolandoMySQLDBA Oct 19 '11 at 19:28
  • If you target a primary key ID or a unique key combo... why would it be dangerous? – EarnestoDev Oct 19 '11 at 19:32
  • That's would be the only exception. Nonunique keys would be a different story. MySQL may still post warnings in the error log either way. – RolandoMySQLDBA Oct 19 '11 at 19:35
  • 1
    I actually wrote a post about that in the DBA StackExchange : dba.stackexchange.com/questions/1371/…. It has to do with the way MySQL Optimizer performs query transformations under the hood. – RolandoMySQLDBA Oct 19 '11 at 19:37
  • 1
    cool thanx for the insight and link. I've seen them before in plugin code but was unsure of their purpose. Now I know, and knowing is half the battle (GI JOE - had to do it) :) – dkmojo Oct 20 '11 at 18:51
3

There is dedicated $wpdb->update() method that is both convenient helper to perform UPDATE query and performs sanitization, calling $wpdb->prepare() internally.

0

You can insert as many variables as you want (%digit, %string ...)

global $wpdb;
$zz = $wpdb->query( $wpdb->prepare( "UPDATE $wpdb->postmeta 
    SET meta_value = '%s'       WHERE post_id = '%d'    OR WHERE post_id = '%d' ", 
                      $value,                    $id_1,                     $id_2   ) );
var_dump($zz); exit;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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