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

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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

Leave a Comment