I try to get min product price like this:

$pts = $wpdb->get_results("SELECT post_title, ID, MIN( CAST(wm.meta_value AS UNSIGNED)) AS price FROM phiz_posts
        INNER JOIN phiz_postmeta wm ON (wm.post_id = phiz_posts.ID)
        INNER JOIN phiz_term_relationships wtr ON (phiz_posts.ID = wtr.object_id)
        INNER JOIN phiz_term_taxonomy wtt ON (wtr.term_taxonomy_id = wtt.term_taxonomy_id)
        INNER JOIN phiz_terms wt ON (wt.term_id = wtt.term_id) AND ((wtt.taxonomy = 'product_visibility' AND  wt.slug = 'featured'))
        INNER JOIN phiz_term_relationships rs ON rs.object_id = phiz_posts.ID    
        WHERE post_type = 'product' 
        AND post_title = '$title'
        AND post_status = 'publish' 
        AND rs.term_taxonomy_id = $product_cat_id
        AND (wm.meta_key = '_regular_price'
              OR wm.meta_key = '_sale_price'
              OR wm.meta_key = '_price')
        ORDER BY price");

        foreach($pts as $row)
        {
            $price = $row->price;
        }

This work fine, I'v the right product but without decimal... Ex: Here result is 9 but the real min price is 9,60

Thanks

I try to get min product price like this:

$pts = $wpdb->get_results("SELECT post_title, ID, MIN( CAST(wm.meta_value AS UNSIGNED)) AS price FROM phiz_posts
        INNER JOIN phiz_postmeta wm ON (wm.post_id = phiz_posts.ID)
        INNER JOIN phiz_term_relationships wtr ON (phiz_posts.ID = wtr.object_id)
        INNER JOIN phiz_term_taxonomy wtt ON (wtr.term_taxonomy_id = wtt.term_taxonomy_id)
        INNER JOIN phiz_terms wt ON (wt.term_id = wtt.term_id) AND ((wtt.taxonomy = 'product_visibility' AND  wt.slug = 'featured'))
        INNER JOIN phiz_term_relationships rs ON rs.object_id = phiz_posts.ID    
        WHERE post_type = 'product' 
        AND post_title = '$title'
        AND post_status = 'publish' 
        AND rs.term_taxonomy_id = $product_cat_id
        AND (wm.meta_key = '_regular_price'
              OR wm.meta_key = '_sale_price'
              OR wm.meta_key = '_price')
        ORDER BY price");

        foreach($pts as $row)
        {
            $price = $row->price;
        }

This work fine, I'v the right product but without decimal... Ex: Here result is 9 but the real min price is 9,60

Thanks

• woocommerce-offtopic
• mysql
• wpdb

1 Answer 1

The answer really lays in the first line SELECT post_title, ID, MIN( CAST(wm.meta_value AS UNSIGNED)) AS price - here you cast to UNSIGNED which is integer, and this cuts of your decimal part.

So the solution is just replace UNSIGNED with DECIMAL(10,2) or FLOAT

And result code will look this way with FLOAT

    $pts = $wpdb->get_results("SELECT post_title, ID, MIN( CAST(wm.meta_value AS FLOAT)) AS price FROM phiz_posts
            INNER JOIN phiz_postmeta wm ON (wm.post_id = phiz_posts.ID)
            INNER JOIN phiz_term_relationships wtr ON (phiz_posts.ID = wtr.object_id)
            INNER JOIN phiz_term_taxonomy wtt ON (wtr.term_taxonomy_id = wtt.term_taxonomy_id)
            INNER JOIN phiz_terms wt ON (wt.term_id = wtt.term_id) AND ((wtt.taxonomy = 'product_visibility' AND  wt.slug = 'featured'))
            INNER JOIN phiz_term_relationships rs ON rs.object_id = phiz_posts.ID    
            WHERE post_type = 'product' 
            AND post_title = '$title'
            AND post_status = 'publish' 
            AND rs.term_taxonomy_id = $product_cat_id
            AND (wm.meta_key = '_regular_price'
                  OR wm.meta_key = '_sale_price'
                  OR wm.meta_key = '_price')
            ORDER BY price");

    foreach($pts as $row)
    {
        $price = $row->price;
    }

OR

    $pts = $wpdb->get_results("SELECT post_title, ID, MIN( CAST(wm.meta_value AS DECIMAL(10,2))) AS price FROM phiz_posts
            INNER JOIN phiz_postmeta wm ON (wm.post_id = phiz_posts.ID)
            INNER JOIN phiz_term_relationships wtr ON (phiz_posts.ID = wtr.object_id)
            INNER JOIN phiz_term_taxonomy wtt ON (wtr.term_taxonomy_id = wtt.term_taxonomy_id)
            INNER JOIN phiz_terms wt ON (wt.term_id = wtt.term_id) AND ((wtt.taxonomy = 'product_visibility' AND  wt.slug = 'featured'))
            INNER JOIN phiz_term_relationships rs ON rs.object_id = phiz_posts.ID    
            WHERE post_type = 'product' 
            AND post_title = '$title'
            AND post_status = 'publish' 
            AND rs.term_taxonomy_id = $product_cat_id
            AND (wm.meta_key = '_regular_price'
                  OR wm.meta_key = '_sale_price'
                  OR wm.meta_key = '_price')
            ORDER BY price");

    foreach($pts as $row)
    {
        $price = $row->price;
    }

With Decimal, where total number length is 10 digits and it can have 2 digits after decimal point, sure it's better to adjust these Decimal parameters which better fit your ranges of numbers. Decimal wins over Floats in precision but you need to take care of possible number of digits.