WordPress.org

Ready to get started?Download WordPress

Forums

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

  1. hermitjim
    Member
    Posted 1 year 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):
    			setup_postdata($post);
    			widget_market();
    		endforeach;
    	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
    Member
    Posted 1 year 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
    Member
    Posted 1 year 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
    Member
    Posted 1 year ago #

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

    AND wpostmeta3.meta_value_num > '0'

    and

    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
    Member
    Posted 1 year 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
    Member
    Posted 1 year 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
    Member
    Posted 1 year 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
    Member
    Posted 1 year 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
    Member
    Posted 1 year 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
    Member
    Posted 1 year 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
    Member
    Posted 1 year 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