Please hope me generate a custom report from a Wordpress database

November 23, 2009 4:39 PM

PHP/MySQL gurus: Please help me get data out of Wordpress in a really specific fashion.

So I need to get a custom report out of our Wordpress database, from a very specific range of dates/times, and in a specific format. I need to be able to specify start and end date/times, and get back a comma-delimited file with this data for all posts in the range:

Date, time, post title

That's it. It would be nice if this were somehow made into a plugin with drop-down menus for the date range, but this is a "we need it right now" situation with zero budget for coders.

So, what's the easiest way to do it? I'm thinking a PHP page that fetches and retrieves the data and outputs it to the browser. But I might be missing some solution that is much easier and more elegant. Also, I have no idea how to code such a page, and have zero confidence with the MySQL command line. Can you help, por favor?

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

You can do this with PHPMyAdmin.

Choose the wp_posts field.

Choose SQL

Enter, (for example to get dates between Sept. 1, 2009 and November 1, 2009):

SELECT DATE(`post_date`) AS postDate , TIME(`post_date`) AS postTime, `post_title`
FROM `wp_posts`
WHERE post_date > '2009-09-01'
AND post_date <>

and hit GO.

At the bottom of the created results you should be able to EXPORT.  

Choose CSV and hit GO.  Or save to a file if you prefer and hit Go.

You could do something similar w/ just a php script I think, but if you've got phpmyadmin, that might be handy.

posted by backwards guitar at 5:43 PM on November 23, 2009

Sorry, the SQL got messed up, and I missed it on preview:

SELECT DATE( `post_date` ) AS postDate, TIME( `post_date` ) AS postTime, `post_title`
FROM `wp_posts`
WHERE post_date > '2009-09-01'
AND post_date < '2009-11-01'
LIMIT 0 , 30 

posted by backwards guitar at 5:44 PM on November 23, 2009

On preview, backwards guitar beat me to it... I had written just about exactly the same query and recommendation for phpMyAdmin... it's an indispensible tool.
posted by usonian at 5:47 PM on November 23, 2009