WordPress.org

1. daniel4ing
   Member
   Posted 6 months ago #

   I'm trying to set up a frontpage with multiple loops, the loops will be checking multiple custom fields in a custom post_type.

   The post_type's name is products and the custom fields that have been attached to it are product_featured, product_start_date and product_end_date. The product_start_date and product_end_date date format is d-m-Y, so it is 01-09-2011.

   Now I've tried to use WP_Query in combination with meta_query but noticed that it doesn't seem to work very well with arrays.

   $args=array(
   'post_type' => 'products',
   'meta_query' =>array(
   array( 'key' => 'product_start_date', 'value' => $date, 'type' => 'DATE',  'compare' => '=<'  ),
   array( 'key' => 'product_end_date',  'value' => $date, 'type' => 'DATE',  'compare' => '>='  ),
   array( 'key' => 'product_featured',  'value' => 1  )
   ),
   'post_status' => 'publish'
   );

   I have tried this with and without 'relation' => 'AND'. I've also tried it with both WP_Query and query_posts. Only the first loop will show results.

   Now I'm trying to set it up with custom select queries, getting results in multiple loops and now I've bumped into another problem. I can't seem to figure out how to check the meta value against the current date.

   This is what I'm currently using the custom query:
   

   $querystr = "
   SELECT wposts.*
   FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
   WHERE wposts.ID = wpostmeta.post_id
   AND ( wpostmeta.meta_key = 'product_featured' AND wpostmeta.meta_value = '1' )
   AND wposts.post_type = 'products'
   AND wposts.post_status = 'publish'
   ORDER BY wpostmeta.meta_value DESC
   ";

   I've tried to add
   AND ( wpostmeta.meta_key = 'product_start_date' AND STR_TO_DATE(wpostmeta.meta_value, '%d-%m-%Y') < '".date('d-m-Y')." ) )

   and

   AND ( wpostmeta.meta_key = 'product_start_date' AND STR_TO_DATE(wpostmeta.meta_value, '%d-%m-%Y') > '".date('d-m-Y')." ) )

   ...but no results.

   What am I doing wrong? -_-

2. vtxyzzy
   Member
   Posted 6 months ago #

   Can't say if anything else needs to be changed, but in order to compare dates, they must be in Y-m-d order.

3. daniel4ing
   Member
   Posted 6 months ago #

   The date format I'm using is d-m-Y though, why would I have to check it in the Y-m-d order?

4. vtxyzzy
   Member
   Posted 6 months ago #

   Using d-m-Y order, 21-01-1999 will compare greater than 15-01-2011 because 21 is greater than 15 and the compare is left-to-right.

5. daniel4ing
   Member
   Posted 6 months ago #

   I've adjusted the date format, but still the same result.

6. vtxyzzy
   Member
   Posted 6 months ago #

   In this WHERE condition:

   AND ( wpostmeta.meta_key = 'product_start_date' AND STR_TO_DATE(wpostmeta.meta_value, '%d-%m-%Y') < '".date('d-m-Y')." ) )

   you are converting the meta_value from a string to a date and the date from a date to a string. One is a string and the other a date.

   Try this:

   AND ( wpostmeta.meta_key = 'product_start_date' AND wpostmeta.meta_value < '".date('Y-m-d')."' ) )

   Try printing out the querystr to make sure everything looks the way you expect.

7. daniel4ing
   Member
   Posted 6 months ago #

   Thanks for the reply vtxyzzy

   All 3 meta_key and meta_value queries work separately, just not combined...

8. daniel4ing
   Member
   Posted 6 months ago #

   Got the result, took me long enough...heh

   SELECT DISTINCT wposts.*
   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 = 'product_featured'
   AND wpostmeta.meta_value = '1'
   AND wpostmeta2.meta_key = 'product_start_date'
   AND wpostmeta2.meta_value <  '" . date("Y-m-d") . "'
   AND wpostmeta3.meta_key = 'product_end_date'
   AND wpostmeta3.meta_value > '" . date("Y-m-d") . "'
   AND wposts.post_type = 'products'
   AND wposts.post_status = 'publish'
   GROUP BY ID

   Thanks for the help Mac :)

Reply

You must log in to post.