Getting all the posts on a single date from Wordpress, in list form

March 21, 2010 4:11 PM

Wordpress and MySQL-savvy coders, please help me do something.

I have a ton of posts in my Wordpress site, and I need let users and myself see them, by date. What I want to do is put a widget on my sidebar with three drop-down menus - month, date, year - and clicking submit will return a page that contains the publish times (time of day) and titles of all posts published on that date, just in plain list form.

Can some kind soul who knows how to write a query hand-hold me through this?

posted by jbickers to Computers & Internet (5 answers total)

Response by poster: For more context, the site is www.wfpk.org, and this is to be an alternative to the clickable calendar. Each post is a single song. Want people to be able to pick a date and view the entire playlist for that date, without having to load that clickable calendar on each visit. Also, we need to be able to dump lists of songs played on certain dates, for report purposes.
posted by jbickers at 4:12 PM on March 21, 2010

Do you actually need to write a sql query to implement this? It seems like you just need a plugin that adds a form that takes in year, month, and day, and redirects to http://www.wfpk.org/year/month/day/ (and if you don't care about people with javascript turned off, you can do this entirely in javascript)
posted by inkyz at 5:00 PM on March 21, 2010

Response by poster: But redirecting to http://www.wfpk.org/year/month/day produces paginated content, i.e. page of 30 entries and a link to "older entries." What I need is a printable report of EVERY post from that date, all on the same page. Also, using the year/month/day URL produces a result in reverse chronological order, which sucks for the user (we hear all the time "I wanted to see what song was playing at 8 in the morning but I had to click 15 times to get to that hour").
posted by jbickers at 5:24 PM on March 21, 2010

You could just hack the code that limits the listing to 30, and set it to some astronomical number, like 5000 instead. (Chances are that this is done with a 'LIMIT' statement at the end of the SQL query.)

Similarly, you could adjust the "ORDER BY" portion of the query. Chances are good if you're getting the newest stuff first that the query includes "ORDER BY post_date DESC" which says to order the posts in DESCending date (most recent first). Just delete the DESC part, and that should order the list chronologically.

As for the modified search form, I'd check out the WordPress plugin directory to see if any of the pre-written plugins might scratch your itch.
posted by Wild_Eep at 7:48 PM on March 21, 2010

Interacting with the WP database directly is a bad idea. The schema can change, and it's probably more work that just interacting with the query_posts() function that WP provides.

Here's a roadmap of how I'd implement your time search:

1. Create your form. It'll just be a dumb, static form, which passes its variables (via GET/POST) to the main WordPress page.

2. Then in your main page template, you're going to intercept your above queries while still playing nice with WordPress.

3. Validate the stuff passed in, and use it to formulate a custom query_posts() call with the date/month/year.

4. The rest of the query_posts() attributes should let you hone the results until they're user friendly.
posted by cowbellemoo at 7:28 AM on March 22, 2010