I was asked how to list all the posts in a WordPress database as a follow-on question from this blog post that explains how to search all your WordPress posts for a certain character string or link.
If this article helps you, please help me by clicking one or more of the social media buttons on the left. Thank you!
If you want know how to access your WordPress database using SQL, then follow the same post I just mentioned above for instructions on how to get started via your cPanel dashboard. Once you’ve done that and located the SQL window in cPanel, you can issue an SQL query to ask anything you like of the database.
Please be aware that you can seriously damage your WordPress database if you update any data without full knowledge of what you are doing. However, this post is only about reading data, not writing it so doing only what we talk about here does not pose any danger.
Make Sure You Get More Than 30 Rows Of Data
In this case we are listing the titles, slugs, authors and number of comments on each published post. We are also limiting our results to 1000. If you omit the LIMIT TO portion of the query you will probably get no more than 30 results as the your cPanel interface typically has a built in limit of 30 rows of data. You can change this number as you wish. Of course if you don’t yet have 30 published posts this last point won’t make any difference to you.
Most of this information returned by this query is available on the WordPress dashboard, but doing it this way can mean you get all the results on one page, and you can order them in certain ways not available from the dashboard.
SQL WordPress POst Query – Ordered By Number of Comments and Post Title
SELECT p.id as post_id, u.user_nicename as author, p.post_title, p.post_name as post_slug, p.post_date as local_publish_date, p.comment_count FROM wp_posts p, wp_users u WHERE p.post_status='publish' AND p.post_type='post' AND u.id = p.post_author ORDER BY p.comment_count DESC, p.post_title LIMIT 1000
Of course you can play around with the SQL query above to display more or less information as you see fit or to order the results in a different way. I have ordered by the number of comments descending, but you might want to order in alphabetical order of post title or in date order. In which case the queries would be like this ones below, respectively.
SQL WordPress Post Query – Ordered By Post Title
SELECT p.id as post_id, u.user_nicename as author, p.post_title, p.post_name as post_slug, p.post_date as local_publish_date, p.comment_count FROM wp_posts p, wp_users u WHERE p.post_status='publish' AND p.post_type='post' AND u.id = p.post_author ORDER BY p.post_title LIMIT 1000
SQL WordPress Post Query – Ordered By Post Date
SELECT p.id as post_id, u.user_nicename as author, p.post_title, p.post_name as post_slug, p.post_date as local_publish_date, p.comment_count FROM wp_posts p, wp_users u WHERE p.post_status='publish' AND p.post_type='post' AND u.id = p.post_author ORDER BY p.post_date DESC LIMIT 1000
How About Pages?
You might want to have the above query examples work not only on posts, but also on pages. If so, that is easy to do. The following example will output the data for posts and pages.
SELECT p.id as post_id, p.post_type as type, u.user_nicename as author, p.post_title, p.post_name as post_slug, p.post_date as local_publish_date, p.comment_count FROM wp_posts p, wp_users u WHERE p.post_status='publish' AND p.post_type IN ('page', 'post') AND u.id = p.post_author ORDER BY p.post_type DESC, p.post_date DESC LIMIT 1000
