Please hope me generate a custom report from a Wordpress database
November 23, 2009 4:39 PM   Subscribe

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 (4 answers total) 1 user marked this as a favorite
 
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


If you don't have access to PHPMyAdmin, here's a command line that might work:

mysql -h [your mysql host] -u [your db user] -p [your wordpress db] -e "select date(post_date) as postDate, time(post_date) as postTime, post_title from wp_posts where post_date > '2009-01-01' and post_date < '2009-02-01'" > output.txt

That does get a lot of duplicates, though, because Wordpress stores post revisions in the same table. It also produces tab-separated rows, rather than comma-separated. If you want to strip out the revisions, you could add "and post_type='post'" to the end of the query. Changing the tabs into commas is probably best done in whatever tool you're importing the data into, since CSV turns out to be a really poorly defined format, and escaping can be tricky and dependent on what you're importing into.
posted by hades at 6:05 PM on November 23, 2009


« Older How to make my dog have a solid stool?   |   Subvert the dominant, oh geez, I can't even type... Newer »
This thread is closed to new comments.