WordPress – Find the list of Users who have posted the content – using SQL

WorPress is the famous content management system allowed to post Blog content. When we post the content in WordPress sites; the details of the content and the publisher (author) who posted the content; will be stored in the WordPress’s back-end database(s).

If you allowed to EveryOne to register and post the content into your WordPress site; you will have to manage the postings, comments & the authors regularly to avoid SPAMs and SPAM users (authors). SPAM users are the authors, post some not useful; repeated data into the site. We must have to periodically check this and delete (or block) the SPAM users to not to post the SPAM content.

When we delete the SPAM users, we always SHOULD focus on deleting all content posted by the SPAM Users. WordPress provides the functionality to delete the Users and also “Delete All Content” the Users posted.

Then, how to find the SPAM content? If your site is BIG, it is difficult to find it manually; instead it is better to use WordPress Plugins / Modules to find the SPAM content.

But for the smaller sites, if you want to manage the SPAM content manually; instead of using another Plugins / Modules; it is always better to find the SPAM user first, and delete all the content the SPAM User posted; instead of finding a SPAM content. This will save lot of your time.

WordPress displays list of Users and the Posts count in the Users Page. But if your site has thousands of Users; it is difficult to find which Users have posted the content. So what we do now is, we write an SQL statement to figure out the list of Users who has posted the content in the WordPress site.

We do it from back end; using phpMyAdmin tool or any other tool to support to connect to the database; and we write an SQL statement to find the list of users who have posted the content.

Step 1. Go to the database using phpMyAdmin (or any other relevant tool), where your WordPress website data stored.

Step 2. WordPress stores the posts details in wp_posts database table. It has the field, post_author, to store the User Id of the author who has posted the content. If there is no posts from the user; there will be no user entry details in this table.

Step 3. Once the user is registered in the WordPress site; user data is stored in wp_users database table. User id is stored in its’ ID field and the Name of the User is stored in display_name field. This the user ID referring in wp_posts database table in the field post_author, when the user posts the content. These are the ONLY fields we are going to use in the SQL statement; that’s why I referred only these fields.

Step 4. Now we write an SQL statement to identify the list of Users who has posted the content:

SELECT DISTINCT wp_posts.post_author, wp_users.display_name FROM wp_posts, wp_users WHERE wp_users.ID = wp_posts.post_author

Step 5. If you identify any SPAM users from the result of above SQL query; delete the User(s) from your WordPress site; from Users page. This will allows you to delete all the content of the SPAM user, posted in the site.

If you like this Article, please post your feedback.

🙂 Sahida

WordPress – Find the list of Users who have posted the content – using SQL

Leave a Reply

Your email address will not be published.

Scroll to top