Mysql query and order meta value

Question

I made a mysql query to WordPress like this:

$querystr = "
    SELECT $wpdb->posts.*
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
    AND $wpdb->postmeta.meta_key = 'where'
    AND $wpdb->postmeta.meta_value = 'london'
    LIMIT 0,50  ";

$pageposts = $wpdb->get_results($querystr, OBJECT);

In this way i can grab all posts with meta_key = "where" and meta_value = london

The problem is that I've also got another meta_key, "date" and a meta_value that contains the real date like "05-06-2013", "06-06-2013", etc.

How can I query all posts with meta "where" = "london" and order by date using the data contain in meta "date" = "xx-xx-xxxx" ?

Answer 1

While the WPDB class, or its global $wpdb instance are your friend when it comes to querying custom tables or WP core data that does not have a higher-level API, it is overkill to use it to query posts.

Instead, read up on WP_Query.

In your case, you'd use it like so:

$args = array(
    'posts_per_page' => 50,    /* set limit to 50 */
    'post_type' => array( 'post', 'page' ), /* fetch both posts and pages */
    'meta_key' => 'date',    /* garb only posts with meta_key "date" set */
    'orderby' => 'meta_value_num',    /* sort by numeric value of above key */
    'order' => 'ASC', /* sot order */
    'meta_query' => array(    /* grab only posts/pages w/ "london" as value for "where" */
        array(
            'key' => 'where',
            'value' => 'london',
            'compare' => '='
        )
    )
);

$pageposts = new WP_Query( $args );

while ( $pageposts->have_posts() ) :
    $pageposts->the_post();
    /* output something */
endwhile;

wp_reset_postdata();

The above sorting by date will work flawlessly with either a timestamp or a YYYY-MM-DD string format.

With the way you currently have it, you will need an additional iteration...