Exporting a list of all your WordPress posts (with publication dates) using SQL

PUBLISHED 12 May 2023 - UPDATED 15 Aug 2023

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.

Exporting data for a content audit

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.

Free and paid plugins

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:

Export Posts with Export All Post Meta

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.

The DIY option - accessing phpMyAdmin

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

MySQL Manager in SiteGround

Identifying columns to export

Once you're in phpMyAdmin, you'll need to select the correct database for your site:

  1. Click on Databases in the top menu
  2. Click on your database name - there may be several listed here, one for each staging site for example. I know that my production database starts with 'techni', the same as my 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:

Example WordPress database 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:

  • post_title
  • post_name (this is the slug, or end part of the URL)
  • post_date
  • post_modified

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!)

Querying your database

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:

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.

Exporting your data from phpMyAdmin

  1. In the very bottom menu, click Export. There's more than one 'Export' option on the page - you want the one in the 'Query results operations' panel as shown in the image below.

Export results from SQL query

  1. Change the Format dropdown to CSV (or choose a different option to suit your requirements)
  2. To export all of your data, ensure that Dump all rows is ticked. I know that 'Dump' is a slightly alarming term, but don't worry - this operation won't change or delete anything from your database
  3. Click Go (button on the right)
  4. Save your export file when prompted

That's it! You now have a list of your published WordPress posts which you can use as a basis for further analysis.

Video tutorial

Here's a screencast of me accessing phpMyAdmin, applying the SQL query and exporting a list of posts:

Subscribe
Notify of
guest
1 Comment
Oldest
Newest
Inline Feedbacks
View all comments
Rickyunic
6 months ago

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!

James Clark
Hi! I'm James Clark and I'm a freelance web analyst from the UK. I'm here to help with your analytics, ad operations, and SEO issues.
1
0
What do you think? Leave a commentx
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram