1

I'm developing a plugin made on the original calendar. My wordpress is based on real events and I want to keep trace of theme on the calendar.

So I added a custom field in the post which is the end of the events (or the last day for subriscribe) with the value of a date of course (like 25/02/2010).

Now I want to display on the calendar all the events that ends on the days instead of the posts create in each days.
How to get the meta data value of the post in this query?

 // Get days with posts
$dayswithposts = $wpdb->get_results("SELECT DISTINCT DAYOFMONTH(post_date)
    FROM $wpdb->posts WHERE MONTH(post_date) = '$thismonth'
    AND YEAR(post_date) = '$thisyear'
    AND post_type = 'post' AND post_status = 'publish'
    AND post_date < '" . current_time('mysql') . '\'', ARRAY_N);

It have to select all the post with my custom meta value.

Hope I'm not doing this for nothing, I googled and no plugin with this function came out.

1
  • 1
    whats you custom field name?
    – Bainternet
    Feb 25, 2011 at 11:16

1 Answer 1

1

I think you can do a left join :

left join 
    $wpdb->postmeta my_field_meta on (p.ID = my_field_meta.post_id and 
                                      my_field_meta.meta_key = 'subscribe') 

Where "subscribe" it's the name of your custom field. So your code could be:

 $dayswithposts = $wpdb->get_results("SELECT DISTINCT DAYOFMONTH(p.post_date)
    FROM $wpdb->posts as p
    LEFT JOIN 
        $wpdb->postmeta my_field_meta on (p.ID = my_field_meta.post_id and 
                                          my_field_meta.meta_key = 'subscribe')     
    WHERE MONTH(p.post_date) = '$thismonth'
    AND YEAR(p.post_date) = '$thisyear'
    AND p.post_type = 'post' AND p.post_status = 'publish'
    AND p.post_date < '" . current_time('mysql') . '\'', ARRAY_N);
3
  • yeah this is kind of what I mean. I forgot one important thing. The MONTH(p.post_date) doesn't has to be the month of the posts but the month of the my custom meta field (called "scadenza" for who's asking). But I don't how to make the condition. It will be like WHERE MONTH(my_field_meta)...how to do that? and I think a INNER JOIN is better so I have only the posts with the meta field = "scadenza"
    – Kreker
    Mar 4, 2011 at 8:03
  • ok this is the solution i'm using SELECT DISTINCT SUBSTRING(m.meta_value,1,2) FROM $wpdb->posts as p INNER JOIN $wpdb->postmeta m on (p.ID = m.post_id and m.meta_key = 'Scadenza') WHERE SUBSTRING(m.meta_value,4,2) = '$thismonth' AND SUBSTRING(m.meta_value,7,4) = '$thisyear' AND p.post_type = 'post' AND post_status = 'publish' AND p.post_date < '" . current_time('mysql') . '\'' Thanks for help
    – Kreker
    Mar 4, 2011 at 10:29
  • @Kreker prego :)
    – konus
    Mar 5, 2011 at 3:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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