Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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
                                             )
                              ));
share|improve this question
    
Just use wpdb's internal update function. It handles sanitization for you. –  Andrew Bartel Jan 10 at 16:14

3 Answers 3

up vote 1 down vote accepted

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()
share|improve this answer
    
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

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

share|improve this answer
global $wpdb;
$bl = $wpdb->query( $wpdb->prepare( "UPDATE $wpdb->postmeta SET meta_value = '111' WHERE post_id = '4'", "blahblah" ) );
var_dump($bl);
share|improve this answer
1  
What exactly does your prepare (or the code at all) do? Where do you use blahblah? You should always write at least a short explanation, which can be one sentence (doesn't have to be only one, though). –  t f Jan 10 at 14:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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