WordPress, the famous content management platform, stores all the content including posts, comments, categories etc. in a MySQL database. MySQL is one of the most popular relational database system which is used by most of the web hosting services. Whenever you perform any action on WordPress website, internally changes are actually done in the database tables. For example, when you add a comment, the comment is saved in a database table. When you add a post, the contents of the post are actually inserted to a specific table reserved for posts and so on.
There are times when you need to perform bulk operations i.e. operations on a lot of records. For example, when you need to delete all the spam comments. In such scenarios, doing it individually for each record is an unimaginable task. In such scenarios SQL Queries are the best solution in such use cases.
Before we move on to discuss various useful SQL Queries, please note that it is important to take a full backup of your database before executing any SQL Query so that you can recover from any undesirable situation. To take a backup, there are a lot of readymade plugins available like WP-DB-Backup which you can use. Otherwise you can also manually take backup of your database using below steps
Step 1: Login to phpmyadmin
Step 2: Select the database and click on export
Step 3: Select all the tables as we need to take a full backup of Database.
Step 4: Select SQL as we want to export the database tables as .sql file
Step 5: Click on “Save as File”
Step 6: Click on “Go” button which will download a .sql file.
Once you have backed up your blog either using a plugin or manually, you can make changes in your database.
If you are new to PhpMyAdmin, Let’s discuss about how to run phpMyAdmin SQL Queries first:
Step 1: Login to your phpMyAdmin or access the phpMyAdmin tool from your hosting cPanel.
Step2: On the left side you will see all the available databases, Click on the database in which you need to make changes.
Setp3: You will see the list of tables that are in the database on the right side.
Step 4: To run the queries that we will discuss below in this article, Just click on the sql tab and then you will see a text box or field where you can execute the queries.
So, now you know how to execute queries, let’s look at some essential SQL queries which you should have in your arsenal.
- Transfer ownership of all articles of a specific author: In case you need to assign all articles written by Author 1 to Author 2, then you need to obtain the author ID first by viewing the author’s profile and selecting the “user_id” value from the address bar. After that just execute the below query.
- 0.0.1 UPDATE wp_posts SET post_author=”author_2_id” WHERE post_author=”author_1_id”
- 0.0.2 DELETE FROM wp_comments WHERE comment_type=’pingback’
- 0.0.3 SELECT * From wp_terms wt
- 0.0.4 INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy=’post_tag’ AND wtt.count=0;
- 0.0.5 DELETE FROM wp_comments WHERE comment_approved = 0
- 0.0.6 UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date < ‘2016-01-01’ AND post_status = ‘publish’;
- 0.0.7 UPDATE wp_users SET user_login = ‘NewUsername’ WHERE user_login = ‘Admin’;
- 0.0.8 UPDATE ‘wordpress’.’wp_users’ SET ‘user_pass’ = MD5(‘NEW_PASSWORD’) WHERE ‘wp_users’.’user_login’ =’USER_ID’ LIMIT 1;
- 0.0.9 SELECT DISTINCT comment_author_email FROM wp_comments;
- 1 Share this:
- Delete all pingbacks : To delete all the pingbacks in the comments on your blog, you can directly execute the below query
DELETE FROM wp_comments WHERE comment_type=’pingback’
- Identify unused tags: To identify tags which are not used on any post of yours, just run the following query.
SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy=’post_tag’ AND wtt.count=0;
- Deleting all unapproved comments: To delete all the comments which are in state unapproved, execute the following query
DELETE FROM wp_comments WHERE comment_approved = 0
- Disable comments on older posts: In case you do not want viewers to add more comments on your older posts, you can do that by executing this simple SQL query
UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date < ‘2016-01-01’ AND post_status = ‘publish’;
- Change default ‘Admin’ UserName: By default the username for every WordPress installation is “Admin”. If you want to change it, you can do that using the below SQL query
UPDATE wp_users SET user_login = ‘NewUsername’ WHERE user_login = ‘Admin’;
- Manually reset wordpress password: To manually reset wordpress password, use the below SQL Query
UPDATE ‘wordpress’.’wp_users’ SET ‘user_pass’ = MD5(‘NEW_PASSWORD’) WHERE ‘wp_users’.’user_login’ =’USER_ID’ LIMIT 1;
- Get list of all commenters email addresses: This SQL Query is useful when you need to collate list of email IDs of all the people who have added comments on your WordPress blog.
If You have More Essential WordPress SQL Queries, Please share with us using the comment section below and don’t forget to share this post in your social circle and show you love by hitting Like on Facebook, Twitter and Google+ as well.