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

Question

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.

I don't have an answer yet... ? I have a workaround that doesn't answer the question being asked. — GrimPanda
– GrimPanda, Commented May 17, 2013 at 2:36

kaiser · Accepted Answer · 2013-05-17 10:55:19Z

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

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
– GrimPanda, Commented May 17, 2013 at 13:36

Charles Clarkson · Accepted Answer · 2013-05-17 02:51:33Z

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.

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
– GrimPanda, Commented May 17, 2013 at 4:33

Mayeenul Islam · Accepted Answer · 2014-10-10 13:43:08Z

-1

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

edited Oct 10, 2014 at 13:43

Mayeenul Islam

13k21 gold badges86 silver badges171 bronze badges

answered Oct 10, 2014 at 12:09

sumit

1

2

Please add an explanation to your answer: why could that solve the problem?

Sven
– Sven

2014-10-10 13:00:06 +00:00
Commented Oct 10, 2014 at 13:00

Add a comment |

Stack Exchange Network

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

3 Answers 3

The `$wpdb` class properties

`meta_query`

Update the meta entries

Your Answer

Hot Network Questions

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

3 Answers 3

The $wpdb class properties

meta_query

Update the meta entries

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions

The `$wpdb` class properties

`meta_query`