WordPress.org

Support

Support » Plugins and Hacks » Hacks » need help with a query of a meta key with prices with commas and$

need help with a query of a meta key with prices with commas and$

  • i need help with a query for a meta key ‘price’ the problem is i need it to display 15 posts per page, and the real pain is the prices are entered into the database with $’s and commas, ie. $5,000. we want to be able to perform a query for price ranges and have them display asc ie. an “under 50$” query would display all posts with a meta key price under $50… there are also prices entered as “$TBD” “$ Upon Request”, we need to exclude those from the results.

    thanks in advance

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator bcworkz

    @bcworkz

    Have you tried using CAST() in your query to convert the value to decimal type? Not sure it’ll work correctly, but worth a try. If you’re using WP_Query, it probably doesn’t know how to parse that as an argument, you’ll need to add it to the query using one of it’s filters. Or use $wpdb instead.

    i have a sql query now, that will do basically that, the real problem is it ignores pagination, so i get the sort i want, but it displays every post on one page…

    <?php
    $querystr = "
    	SELECT wposts.*, ABS(
    REPLACE(SUBSTRING(wpostmeta.meta_value, 2)
    , ',', '')) AS sortby
    	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    	WHERE wposts.ID = wpostmeta.post_id
    	AND wpostmeta.meta_key = 'price'
            AND wpostmeta.meta_value != '$TBA'
            AND wpostmeta.meta_value != 'REQUEST $'
            AND ABS(REPLACE(REPLACE(wpostmeta.meta_value, '$', ''), ',', '')) > '50'
            AND ABS(REPLACE(REPLACE(wpostmeta.meta_value, '$', ''), ',', '')) < '150'
    		AND post_status='publish'
            ORDER BY sortby asc
    		";
    
    $pageposts = $wpdb->get_results($querystr, OBJECT);
    
    if ($pageposts) : foreach ($pageposts as $post): setup_postdata($post);
    	$my_meta = get_post_meta(get_the_ID(), '_my_meta', TRUE);
    ?>
        <?php get_template_part( 'grid', get_post_format() ); ?>
    
        <?php endforeach; endif; ?>
    Moderator bcworkz

    @bcworkz

    WP_Query has a bunch of filters that allow you to directly modify it’s current SQL query in order to work around it’s parsing limitations. Now that you have a working SQL, you just need to figure out how to modify the query in WP_Query so that it ends up looking the same as yours. Then you can make use of it’s pagination.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘need help with a query of a meta key with prices with commas and$’ is closed to new replies.
Skip to toolbar