Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm trying to query a custom post type "jogos" where the meta value "data_de_lancamento" (launch date) is <= than today's. That field uses the format dd/mm/yy.

The returned posts from that query must then be ordered Descending by the custom field "views".

My current code is:

date_default_timezone_set('Europe/Lisbon');
$data = date("d/m/y");
global $wpdb;

$sql = "SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2
WHERE wposts.ID = wpostmeta.post_id
AND wposts.ID = wpostmeta2.post_id
AND wpostmeta.meta_key = 'views'
AND wpostmeta2.meta_key = 'data_de_lancamento'
AND wpostmeta2.meta_value < $data
AND wposts.post_type = 'jogos'
AND wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value ASC";

$resultado = $wpdb->get_results($sql, OBJECT);
share|improve this question

1 Answer 1

You need to use the meta_query parameter of the WP_Class when running a query. Something like this should point you in the right direction.

$data = date("Y-m-d");
$args = array(
    'post_type' => 'jogos',
    'meta_query' => array(
        array(
            'key' => 'data_de_lancamento',
            'value' => $data,
            'compare' => '<='
        )
    ),
    'meta_key' => 'views',
    'orderby' => 'meta_value',
    'order' => 'DESC'
 );
$query = new WP_Query( $args );

The catch with this is that it's nearly impossible to compare dates in the form of 'dd/mm/yy'. As such, this code will not do the trick. You need to first change your date format to 'yyyy-mm-dd'. This is a much more useful way of performing the date comparison. Also, you may need to do some testing with the operator to make sure it's functioning right.

Note that I specify using meta_query which is new to WP 3.1. meta_key and meta_value are deprecated as of WP 3.1; however, to order by meta_value you actually still have to specify meta_key as the value you want it to order by. Confusing and not well documented.

References:

http://codex.wordpress.org/Function_Reference/WP_Query#Custom_Field_Parameters

http://codex.wordpress.org/Function_Reference/WP_Query#Order_.26_Orderby_Parameters

share|improve this answer
    
Thank you so much! Is there any way i can change the date format for the query, and then put it back as it was? So the date displayed is the format i want? –  Tiago May 16 '11 at 0:12
    
Update: I just tried your code, edited manually the date formated on the admin panel, but still, the query returns nothing.. :/ –  Tiago May 16 '11 at 0:29
    
To answer the first question, I would recommend storing the date in the database as yyyy-mm-dd as this is easiest for sorting tasks. Then, when you read out the data, run it through the php date function in order to format it however you want, e.g., date('m/d/y', strtotime($date)); As for your second comment, without knowing more about the situation I cannot help. I would recommend trying to change the operator, or further playing with the query until you get something to work. I'm happy to keep helping, but I need more to work with. –  tollmanz May 16 '11 at 1:20
    
I've tried storing the date with the format yyyy-mm-dd, still no results in the query.. The date function is also using that format. No results so far... What else would you need to help me with this? –  Tiago May 16 '11 at 13:40
    
Any help is still appreciated :) –  Tiago May 21 '11 at 23:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.