I have a list of events in 3 categories, that need to be listed in chronological order, based on a date that is added in the custom field meta value (exp_date) for each post. Category pages are named per their cat num - ex. category-6.php.
It seemed I was able to do the sort, based on info from this post re: using a custom select query.
At first, I thought it was working perfectly, until more posts were added, pushing the total number of posts to 15 - five more than the 10 per page default. Then realized that the post are sorted correctly but only on a per page basis.
What I think is happening is that the results are being pulled from the posts table in order of post date (10 most recent), and then my query is being applied to sort the results on that page. On subsequent pages, results are offset by ten, and then sorted according to my query again.
I think what I need to do stop the original wp query, and use only MY query to get results so that the results line up right on the next/previous pages.
Unclear on how to make that happen, if it can be made to happen and would be very grateful for input or suggestions.
my select query:
SELECT * FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id) WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id AND $wpdb->posts.post_status = 'publish' AND $wpdb->postmeta.meta_key = 'exp_date' ORDER BY $wpdb->postmeta.meta_value ASC