Custom Query based on Meta_Key

Well, I thought I’d update. I’m making a bit of progress here. I’ve managed to get the page to recognize the posts by the meta_key, and now they display that month’s posts. I’m currently trying to configure it so that it’ll archive the old ones (and provide sidebar links to these archives).

I’ve also managed to apply the monthly name to the post div, so that I can style them by month (so now, instead of <div class="post"> I have <div class="post September_2007"> which is cool – because now I can arrange the stylehseet as needed based on that setting.

The way I got the monthly stuff to appear on one page was to do a comparison of the current month and year to the month and year placed in the meta_key. This works great for the current month and year – but it’s proving to make the archiving a challenge. So #1 and part of #2 are currently worked out.

Now I’m having an issue trying to figure out the best way on something else. Some of the authors just use the same article month after month, without ever changing it. Now I need to figure out the best way to carry over stagnant articles – somehow do a check before displaying this Page so that if there’s an older post that doesn’t have a newer one to replace it, then it’ll still display the older one. Each of these authors has to post to a certain category (assigned to them) every month. So there will only be the one post per month in the one category – so I believe there will be a way I can just check to see if a post written in August of 2007, and placed in the “Fish” category…to check the meta_key against the current date and category, and if a newer post has not been published for the current month, it would use the old one. (and if October 2007 doesn’t have one, it would still use August’s – until a new post was made.)

Now, if I can figure that out…and the archiving…

Maybe I should make this into a plugin. (If I knew how, I probably would!)

Okay, I think I got it. Needs further testing, but I made it a little bit easier on myself by finding a few plugins that already did part of the job and modifying some things. So, I’m going to put the resolution here, in case it helps anyone else.

This is for a newsletter for a local non-profit organization. They want their newsletter to be printable, but also viewable on the web by month. They wanted each board member to log into WP and write a post (which would actually be an article for the newsletter each month)and then the thing would be made easier to put together, if I could get WordPress to list things accordingly. Right now, the newsletter chair has to take article submissions and lay them out by hand in Microsoft word, and then we had to strip out the Word crap and edit it into HTML to display on the page – all by hand. This was to make things easier, so people could just write their article and all the newsletter person has to do is print out the end result.

So I put in a custom meta key, “newsletter_month_year”. The end user just has to write their article/post, and enter in a value for what month and year they want the article to appear in. So it wouldn’t matter what *day* they actually wrote the post – so if they wrote their article in July, but wanted it to appear in December’s newsletter, then they’d just have to put in “December 2007” as the meta value.

So I set the *current* month’s newsletter page to be displayed via a Page. I wrote a custom query to compare the current month to the month put in the meta area. If the meta matched the current month, it would display that post on the Page. Here is what I placed in the page.php file:

<?php if (is_page('12')) {
// get current month and year
$curr_month = date('F');
$curr_year = date('Y');
$curr_date = $curr_month . " " . $curr_year;

// get database information
 $pageposts = $wpdb->get_results("SELECT * FROM $wpdb->posts, $wpdb->postmeta WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id AND $wpdb->postmeta.meta_key = 'newsletter_month_year' ORDER BY post_date ASC", OBJECT);

// start new loop

if ($pageposts) : foreach ($pageposts as $post) :

`$post_id = $post->ID;
$key = “newsletter_month_year”;
$single = “true”;
$month_ID = get_post_meta($post_id, $key, $single);
$news = str_replace(‘ ‘, ‘_’, $month_ID);`

`if ($month_ID == $curr_date) {
setup_postdata($post); ?>`

<div class="post <?php echo $news; ?>" id="post-<?php the_ID(); ?>">

<!--put in your stuff for the post output here-->

<?php }
endforeach;
endif;
 } else { ?>

<!--now put the regular stuff for the other Page layout here-->

Now the issue was archiving. So what I did was save the above custom query for displaying the *current* month, but to archive, I also made sure the posts were saved in the “Newsletter” category – which happens to be category ID #13 for me. So, for this problem, I used the Custom Query String plugin, and turned a certain thread in these forums into a plugin. Then I called in both so that the Newsletter Page and the Newsletter categories would display the sidebar with the archives listed by month. (The Custom Query plugin orders the posts by category *and* meta – I had to alter it a little bit to get it to look at the meta tags – and each month is displayed showing only the meta for that month, and in order by category.)

It’s a lovely thing that works very well. I wish I was a more apt plugin author, because I think this – if it were all bundled together correctly – would be a nice plugin for people who want to use only their posts as a newsletter for their site.

Anyway, hope it helps someone else out!