Sorting posts by multiple metadata information

Hi there,

If anyone could give me any advice with the below that would be great.

I have 3 meta keys attached to posts in a specific category (5), they are: day, month, year.

The month key has a value of 1-12 and I’m ordering the posts by that in the below query:

<?php
$querystr = "
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = 5
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->postmeta.meta_key = 'month'
ORDER BY $wpdb->postmeta.meta_value ASC
";

$pageposts = $wpdb->get_results($querystr, OBJECT);

?>

This works and will output all posts with a month key of 1, then 2 and so on. However, when I do this the day key ordering gets messed up, so the individual posts for each month will be listed by day as 1,12,2,20,3 etc.

I can solve this by changing the last two lines to:

AND $wpdb->postmeta.meta_key = ‘day’
ORDER BY ABS($wpdb->postmeta.meta_value) ASC

But of course then ALL posts are ordered by day.

What I want is to be able to order them by date first *then* sequentially by day.

Sorry if the above makes absolutely no sense, I’m rather new to programming and SQL.

Many thanks,
Oliver