1

I need to update all meta keys in the postmeta table that have a value other than 0, to the value of 0.

I tried this.. no love (though I cannot figure out why....):

$status = $wpdb->query("UPDATE {$wpdb->prefix}postmeta SET meta_value=0 WHERE meta_key = _wti_like_count AND meta_value <> '0'");

I'm on this concept, but how to say "!0" NOT 0 in the key list?

$wpdb->update('wp_postmeta', array('_wti_like_count' => '0'), array('_wti_like_count' => '0'));

Thanks a ton!

UPDATE: I fixed my $wpdb->query as follows and it works:

$status = $wpdb->query("UPDATE {$wpdb->prefix}postmeta SET meta_value='0' WHERE meta_key = '_wti_like_count' AND meta_value <> '0'");

However, I would still like to know if there is a way to pass a key pair array to the WHERE parameter of $wpdb->update and request a != value.

1
  • I don't have an answer yet... ? I have a workaround that doesn't answer the question being asked. – GrimPanda May 17 '13 at 2:36
0

The $wpdb class properties

You can use all the default WordPress tables like this:

  • $GLOBALS['wpdb']->postmeta

so no need to use prefix, etc.

meta_query

As you can see from the Custom field parameter documentation, there's nothing like the <> (or in other words: "not equal to") operator. The equivalent operator in WP should be != (human words: "not is") or NOT LIKE.

So simply do something along the following lines:

$query = new WP_Query( array(
    'meta_query' => array(
        array(
            'key'     => '_wti_like_count',
            'value'   => '0',
            'compare' => 'NOT LIKE',
            # 'type'    => 'numeric'
        )
    )
) );

This would query all your posts with this meta value. What makes me wonder is why you got a string as an integer. I wouldn't do that, especially not if I use a counter. See what I commented out: The type, which would allow you - later on - much more specific queries.

Update the meta entries

Then simply loop through them and update the values.

if ( $query->have_posts() )
{
    while ( $query->have_posts() )
    {
        $query->the_post();
        # Do debugging, logging or sleep() here to avoid timeouts
        update_post_meta( '_wti_like_count', '0', true );

        # I still believe it should be an INTeger
        update_post_meta( '_wti_like_count', 0, true );
    }
}
1
  • 1
    This was very interesting! About the str vs int portion, the reason I typed it as a string was because in the %postmeta table, the meta_value field is of the data type: LONGTEXT. So I typed the data to that. I figured I could just cast the data type either from the SQL or PHP into the needed type. – GrimPanda May 17 '13 at 13:36
0

It does not look like it is possible to use != in the where. Here is the relevant code from the update() method:

$where_formats = $where_format = (array) $where_format;
foreach ( (array) array_keys( $where ) as $field ) {
    if ( !empty( $where_format ) )
        $form = ( $form = array_shift( $where_formats ) ) ? $form : $where_format[0];
    elseif ( isset( $this->field_types[$field] ) )
        $form = $this->field_types[$field];
    else
        $form = '%s';
    $wheres[] = "`$field` = {$form}";
}

The $wheres[] line is telling. It shows only = is ever used. That array is used later:

$sql = "UPDATE `$table` SET " . implode( ', ', $bits ) . ' WHERE ' . implode( ' AND ', $wheres );

In your case, the query could have worked without checking for the value not being 0. The result would have been the same (though more rows would have been affected), but it looks like this method is limited to only = in the WHERE clauses.

1
  • Thank you Charles. I kind of figured this was the case considering the semantics of the setup, but I was hoping someone with a bit more knowledge of the $wpdb object could help me out. Thanks for filling me in! I guess I'll stick to hand writing the query's, it's what I'm used to anyways :) – GrimPanda May 17 '13 at 4:33
-1
add_filter( 'query', 'query_report', 10000 );
function query_report($sql){ 
    var_dump($sql); 
    return $sql;
}
1
  • 2
    Please add an explanation to your answer: why could that solve the problem? – Sven Oct 10 '14 at 13:00

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.