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’
FROM wp_posts
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:

Title Category
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.

 

Need a hand? Looking for something you didn't see?

Give a rundown of what kind of trouble you're having, and we'll see if we can help.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

 

 

CLOSE
CLOSE