Don't be daunted by SQL - it's probably the easiest way to export a list of all the posts on your WordPress site, along with metadata such as publication date.
Are you looking to export a list of all the published posts on your WordPress site? I needed to do that recently as the starting point for a content audit. And after considering a couple of plugins, I ended up exporting the data from my WordPress database myself using an application in my hosting account called phpMyAdmin and a snippet of SQL code.
To be clear, I wasn't looking to export the actual post content - just a list of post titles along with some simple metadata such as URL slug, original publication date and most recent modified date.
My plan was then append to that data with some columns of my own, such as whether I had added a table of contents to each post, or reviewed the content recently. As such I wanted the data to be exported in CSV format so I could continue working on it in Excel or Google Sheets.
Before going down the code route, I wanted to see whether there were any WordPress plugins that would enable me to export a list of posts.
The first plugin I tried was a free plugin called Export All Post Meta.
When activated, this adds a new menu option in the WordPress back office which you'll find at Tools > Export Posts. It gives you the option of choosing the post type (e.g. post or page), posts statuses (e.g. published or draft), and post meta fields (e.g. post author or post title) you wish to export:
However any CSV I tried to generate with Export All Post Meta ended up being empty aside for the following warning:
"Warning: fputcsv() expects parameter 2 to be array"
I don't know if this error is particular to my site, but it led me to ruling out this plugin.
I also looked at a paid plugin called WP All Import, which dominates Google search results for terms such as 'export list of WordPress posts'. It also has more than 100,000 active installations and an excellent average rating from users of 4.5 out of 5.
However a standalone licence (for export only) is priced at $99, so I ruled it out on the basis of cost. That's when I decided to do it myself using SQL.
To export a list of posts yourself, you'll need access to an application called phpMyAdmin in your WordPress hosting account. Most reputable hosts should provide you with this as part of your hosting dashboard.
Not wanting to bombard you with terms, but the phpMyAdmin application will allow you to query your MySQL database using a query language called SQL.
Here's how to find phpMyAdmin in my WordPress host of choice, SiteGround (your host may be slightly different):
1. Go to Websites > Site Tools
2. Click on Site > MySQL to access the MySQL Manager menu page (shown below)
3. Click on the PHPMYADMIN tab
4. Click the ACCESS PHPMYADMIN button
Once you're in phpMyAdmin, you'll need to select the correct database for your site:
This will give you a list of the tables within your database. WordPress databases follow a standard structure (with some differences depending on your theme and plugin). So you should see a table called wp_posts in your list - this is the table that holds your post content - as well as pages and navigation menu items. It's also the table we're going to query using SQL.
Click on wp_posts in your list of tables and you'll see some example rows:
This will include pages, post revisions (if you have these enabled) and so on. But don't worry about that - the thing here is to make a note of the names of any columns (fields) you want to export. For me, these were:
But you might want to include others, such as comment_count - which could be a good indicator of which posts are the most engaging.
(Note that some post metadata, such as which tags and categories are selected for each post, isn't held in wp_posts so is outside the scope of this basic guide!)
1. Still in phpMyAdmin, click on the SQL tab in the menu
2. In the empty box at the top, paste in the following code:
1 2 3 4 5 6 7 8 9 10 11 | SELECT post_title, post_name, post_date, post_modified FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date |
Hopefully this code is self-explanatory, but we are selecting particular columns (which you can change to match your selection) from the wp_posts table. We only want posts (rather than, say, pages) that are published (rather than, say, drafts). We want the output to be ordered by post date, oldest first.
3. Click Go (button on the right)
PhpMyAdmin should now display the results of your query.
That's it! You now have a list of your published WordPress posts which you can use as a basis for further analysis.
Here's a screencast of me accessing phpMyAdmin, applying the SQL query and exporting a list of posts:
Buy me a coffee
I don't usually write comments, but thank you for your post. I saved time and made my work a lot easier for a single procedure. Thank you!