There is probably a plugin to do this lying around somewhere, but I thought I’d show you how to list all your posts directly from your WordPress database via cPanel.
This example deals with standard cPanel hosting that I use and recommend, where there is one database per WordPress site.
Find The Right Database
Each WordPress installation on a standard cPanel account has its own database. So the first thing to do is to determine which database belongs to the site you want to interrogate. If you have several sites hosted on a single account, the database belonging to each may not be obvious.
In my case I want to list all the posts in Genesis Club Training. So I need to locate the WordPress database used by Genesis Club Training.
- To do this I go into cPanel and then click on File Manager. Then navigate to my site and click on it on it in the left hand panel.
- Next locate the wp-config.php file
- Then click on View to read it
- Inside the wp-config.php file you’ll find a line like this:
- Also look for a line that describes the table prefix you have used on your site. It will look something like this:
Construct The SQL Query
Next you go into phpMyAdmin from within cPanel and locate the database that you identified on the left-hand side. Once you have done that you are ready to query the database.
I’ve used the table prefix that applies to my tables in the selected database, but you must substitute your table prefix in here.
Making sure that you have selected the correct database, click on the SQL tab and enter the following SQL query. Of course you must substitute my table prefix of nop_ for the one that is defined for your database.
This will create a list of post titles and post slugs. You cannot get a list of URLs specifically as the permalinks are not held in the database. You could select the guid field, (which starts out as the internal URL of a post) but this would not necessarily provide the URL of each post. For example, if you have moved a site in the past the guid will remain as it was originally and might not represent a post’s current URL.
You can of course construct the URL to match your permalink structure. For example I use the permalink post_id/postname so to list all URLs, in my case I might do this :