How to write: $wpdb->update having WHERE NOT value pair in the array

3 Views

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….):

Read More
$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.

Post Views: 3

Related posts

3 comments

  1. 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 );
    }
}

  2. 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.

  3. add_filter( 'query', 'query_report', 10000 );
function query_report($sql){ 
    var_dump($sql); 
    return $sql;
}

Comments are closed.