SELECT max(meta_value) FROM wp_postmeta WHERE meta_key=’price’… stops working when value is over 999

Overview: I am not trying to return a post. I simply want the single highest value for a particular meta_value across all posts… just the value itself.

Details: I have added a custom meta_key “price” to all my posts. The value is always an integer (no decimals or non-numeric characters). I am trying to do a query that returns the highest / largest / maximum meta_value associated with this particular meta_key.

Buggy Code

function max_meta_value(){
    global $wpdb;
    $query = "SELECT max(meta_value) FROM wp_postmeta WHERE meta_key='price'";
    $the_max = $wpdb->get_var($query);
    return $the_max;
}

Buggy Results: At first I thought the above code worked, because it does if all the meta_values are less than 999. I soon discovered that if the meta_value is greater than 999 then it is ignored. So really the code above is giving me the max(meta_value) for meta_values less than 1000.

Plea to Community: Obviously I do not know why it fails, but I have a feeling that it has something to do with how WP stores the value – perhaps its datatype related? Or maybe I should not be using $wpdb->get_var(). Any guidance will be greatly appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The meta_value is not of an integer type for max to return proper values. You can use mysql cast method to convert into integers as follows:

SELECT max(cast(meta_value as unsigned)) FROM wp_postmeta WHERE meta_key='price'

Method 2

I modified the original function and KDM’s solution to have a more universal function. It goes like this:

function end_meta_value( $end = "max", $meta )
{
    global $wpdb;
    $query = $wpdb->prepare( 
        "SELECT %s( cast( meta_value as UNSIGNED ) ) FROM {$wpdb->postmeta} WHERE meta_key='%s'",
        $end,
        $meta
    );
    return $wpdb->get_var( $query );
}

This way you can get both minimum and maximum values of any custom meta_value.
I also changed wp_postmeta to $wpdb->postmeta to fit any prefix you use.

Note: If you want query for a digit, replace %s in the $wpdb->prepare() statement with %d.

Method 3

I modified szajmon solution to work with wp_cache and fix the sql syntax error I get.

wpdb->prepare wraps the $end variable with quotes and that trows an error (at least in my case)

function get_min_max_meta_value( $type = 'max', $key ){

    global $wpdb;
    $cash_key = md5($key . $type);
    $results = wp_cache_get($key);

    if($results === false){

        $sql = "SELECT " . $type . "( cast( meta_value as UNSIGNED ) ) FROM {$wpdb->postmeta} WHERE meta_key='%s'";
        $query = $wpdb->prepare( $sql, $key);

        return $wpdb->get_var( $query );

    }

    return $results;
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0