Select All Posts And Categories From A WordPress Database In MySQL
When you just want a list of everything you've posted on a Wordpress site, use this MySQL query.
October 8, 2017
I’m getting ready to go through all my blog posts, and wondered how I’d get a nice check list that includes all of my posts and which category they’re in. While there may be a plugin I can use for such a task, I really just want ot be able to SSH into the webserver, run a quick MySQL query, and either dump that out to a csv (for dorking later on a spreadsheet) or just spit it out to the screen so I can copy and paste it into a word processor.
It’s a little bit of a mouthful, but here’s a query that will just give me titles and category names, ordered by category name. Mind you, this is just the query itself. I’m not at all handy with PHPMyAdmin, but I believe you could plug this in there and dump out to a file:
SELECT wp_posts.post_title AS ‘Title’, wp_terms.name AS ‘Cateogry’
INNER JOIN wp_term_relationships
ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_terms
ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
INNER JOIN wp_term_taxonomy
ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE wp_posts.post_status = ‘publish’
AND wp_posts.post_type = ‘post’
AND wp_term_taxonomy.taxonomy = ‘category’
ORDER BY wp_terms.name;
And you’ll end up with something like this:
|OpenBravo at Work||News|
|Open Source Gateway Server||Hardware|
|Switching to Linux||Applications|
I happened to have run it from a bash prompt, so I had a little added both ends. It essentially went:
mysql -p ‐‐execute=”blahblah blah blah;” dbname
The mysql -p means “ask me for my password,” which you’ll usually need in order to run a query from bash, then you’ll need ‐‐execute, followed by the query encased in quotes. THEN, you need to plug in the name of the database. If you’re running several blogs on one server with their own databases, you’ll have to run this query on each one.
If you want to read it in a spreadsheet without copying and pasting, you’ll want to dump it out to a csv file. Pretty much the same command, but with a bit of an addendum:
mysql -p ‐‐execute=”blahblah blah blah;” dbname > filename.csv
Then you’ll just have to ftp or scp the file down from the database server to your local machine.
Want to do this on a WordPress page? Check out my post: In WordPress Get All Posts On A PagePrevious