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 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.

share|improve this question
1  
whats you custom field name? –  Bainternet Feb 25 '11 at 11:16
add comment

1 Answer

up vote 0 down vote accepted

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);
share|improve this answer
    
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 '11 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 '11 at 10:29
    
@Kreker prego :) –  konus Mar 5 '11 at 3:56
add comment

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.