Ready to get started?Download WordPress


Posts just won't order by custom field (11 posts)

  1. hermitjim
    Posted 2 years ago #

    I've hunted around for an answer to this for two days now, and just can't seem to get this code to work. I'm trying to order results in ascending order by a custom field called 'price'. I understand there's an issue ordering by numbers rather than strings, but I have tried every combination of meta_value, meta_value_num, with and without quotes, and am getting absolutely nothing. The thing I really don't understand is I only get results of any description when I set the 'greater than >' to be 'less than <' zero, which is palpable nonsense. The results I get then bring back an unordered list of the posts I'm after.

    I can't give a link to the site because it's running off WAMP, but hopefully some merciful soul will see something in the code:

    global $wpdb;
    	$querystr = "
    	SELECT *
    	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta , $wpdb->postmeta wpostmeta2, $wpdb->postmeta wpostmeta3
    	WHERE wposts.ID = wpostmeta.post_id AND wposts.ID = wpostmeta2.post_id AND wposts.ID = wpostmeta3.post_id
    	AND wpostmeta.meta_key = 'closed'
    	AND wpostmeta.meta_value = '0'
    	AND wpostmeta2.meta_key = 'pay_online'
    	AND wpostmeta2.meta_value = '1'
    	AND wpostmeta3.meta_key = 'price'
    	AND wpostmeta3.meta_value_num > '0'
    	AND wposts.post_status = 'publish'
    	AND wposts.post_type = 'auction'
    	ORDER BY wpostmeta3.meta_value_num ASC";
    	$pageposts = $wpdb->get_results($querystr, OBJECT);
    	if ($pageposts): global $post;
                 	foreach ($pageposts as $post):
    	else : ?> <div class="padd100"><p class="center"><?php _e("Be the first to list here"); ?>.</div>

    [No bumping. If it's that urgent, consider hiring someone or or asking on the #wordpress IRC Channel.]

  2. vtxyzzy
    Posted 2 years ago #

    How are your price values formatted? If they have a leading currency sign, then they will not sort as numbers. In order to sort correctly, you should enter the price with no currency symbol or commas. Do all your formatting just before displaying the value.

  3. hermitjim
    Posted 2 years ago #

    Thanks, vtxyzzy, good thought but not applicable here as the price is just a number. Wondering whether I should explicitly cast it as a floating point before it hits the database? Grasping at straws though...

    Thanks again for your input

  4. vtxyzzy
    Posted 2 years ago #

    I suspect that MySQL doesn't know what to do with meta_value_num:

    AND wpostmeta3.meta_value_num > '0'


    ORDER BY wpostmeta3.meta_value_num ASC";

    Try using this:

    AND (wpostmeta3.meta_value + 0) > '0'
    ORDER BY (wpostmeta3.meta_value + 0) ASC";
  5. hermitjim
    Posted 2 years ago #

    Doesn't return any results at all. Just had a look in the database at the 'price' field in wp_postmeta; price meta_value type is 'long text' - should it be numeric? Or is that just a red herring?

  6. vtxyzzy
    Posted 2 years ago #

    Adding a zero to the value should force it to cast as numeric.

    Perhaps I made a mistake in my coding. Can you try issuing the query in phpMyAdmin or a similar tool?

    Or try taking out the clauses that depend on wpostmeta3.meta_value to see if you get any results.

  7. hermitjim
    Posted 2 years ago #

    OK, so if I remove all refs to wpostmeta3.meta_value including orderby, I get an ascending alpha list on post title

  8. hermitjim
    Posted 2 years ago #

    Not sure how to frame that as an SQL query in PHPMyAdmin - I can see the SQL query box in PHPMyAdmin, but not quite clear how to reconstruct the query from the code (sorry)

  9. vtxyzzy
    Posted 2 years ago #

    Just replace all $wpdb-> by the table prefix used in your database (usually just wp_).

    If you add the order by clause back in, do you get any results? In what order?

  10. vtxyzzy
    Posted 2 years ago #

    I blew it. The 'AND' clause with the + 0 should be like this:

    AND (wpostmeta3.meta_value + 0) > 0

    No quotes around the zero.

  11. hermitjim
    Posted 2 years ago #

    Still not playing :( Haven't had a chance to SQL it yet (real life intervened) will try later today.

    Many thanks

Topic Closed

This topic has been closed to new replies.

About this Topic