WordPress runs on a backend web server, the web programming language PHP and MySQL for managing databases. Being at the peak of its popularity, you would find that there are a lot of plugins available for even the minutest of tasks, but there are many cases where a simple SQL query can lead to a faster solution to the underlying problem.
This tutorial is written based on a simple assumption that you are not new to WordPress and the working of WordPress. Even if you are a beginner, you can still try these out, but I would advise you to be cautious. Let us explore a few SQL hacks in WordPress.
Backing up your data:
Before you start working on interacting with the database directly, make sure you back up your data first. You could read about managing your WordPress database with PhpMyAdmin and export your database before running any command.
Alternately, you can export your data through WordPress. Log into to the wordpress admin (http://wordpress-path/wp-admin/) and go to Tools > Export.
Select ‘all content’ and click ‘Download Export File’. Keep the generated XML file safe before you start fiddling with the SQL queries I am about to tell you.
To log into MySQL, run ‘mysql -u <username> -p -D <database_name>’ in a UNIX based system. You would be prompted for the password, which you must enter to get access to the shell.
Alternately, in Windows based servers, open the MySQL shell and you would be asked for a password for the root user. To select a database enter ‘use <database_name>’ after logging in.
Caution while making Queries:
Before running any query, type the command ‘begin;’. After the changes, if you are satisfied, enter ‘commit;’ to save the changes. If you did something wrong, enter ‘rollback;’ to revert back to a state before you started the session with ‘begin’.
Note: I am assuming the WordPress database table prefix is the one by default- “wp_”. In case it is different, change the table names accordingly. The rest remains the same.
1. Delete Post Revisions:
Although post revisions are usually hidden from view of the Admin, they are saved in the database nonetheless. Over the months, if not years, piles of such post revisions get saved in your database. Agreed they don’t usually bother you, but, if the database size becomes huge depending on the number of your posts, it can render display of data slowly, and in the process, make your site slower.
Naturally, you can manually go through the list and delete post revisions one-by-one, but that is a very tedious task. After all, in this world, where everything seems to be automated, why not this task as well?
Manually checking number of posts:
SELECT count(*) FROM wp_posts WHERE post_type = “revision”;
| count (*) |
| 356 |
Whoa! That’s a big number!
The delete query:
DELETE FROM wp_posts WHERE post_type = “revision”;
Query OK, 356 rows affected (0.06 sec)
The wp_posts table contains all the data for the pages and posts, where the post_type can take values of ‘post’, ‘page’ or ‘revision’. This query would get rid of all posts which have the type as ‘revision’.
2. Removing Spam Comments:
After you start promoting your blog or site in the internet and it gains some popularity, it would be inevitable for you to control the users who visit your site. The more the number of visitors, the higher is the chance of finding spam in comments. What if, some fine day, you are bombarded with thousands of spam comments?
The innocent way here is manually start deleting them, but are you sure you are up for the task?
The easy way is to remove through one simple SQL query.
DELETE from wp_comments WHERE comment_approved = ‘0’;
The wp_comments table holds comments and the field comment_approved has two values – 0 (signifying false) for unapproved and 1 (meaning true) for approved comments, which also appear on the site. This query would remove all unapproved comments.
Remember that this query would delete other unapproved comments as well.
A better way for comment management and saying goodbye to spam is using the Akismet Plugin.
3. Change post authors:
Sometimes it so happens that you start writing your posts under the admin username, and realize it only after it is too late. Manually reverting back the authors of the posts would be too time consuming.
The second scenario in which this might occur is while importing posts. Usually the username under which you are logged in during the import, which is usually the first task after installing the site and done under admin, becomes the author of those posts.
The best way here is to use an SQL command to do the work. To do this, you need to first check the ids corresponding to the author. Run the following query.
SELECT id, display_name FROM wp_users;
Let’s say I want to transfer all the admin posts to the user ‘random name’. To do it, I simply need to run the following.
UPDATE wp_posts SET post_author = NEW_AUTHOR_ID WHERE post_author = OLD_AUTHOR_ID;
In my case, the Old and New author ids are 1 and 2 respectively.
To make sure everything went well, we just double check the wp_posts again.
SELECT post_name, post_author FROM wp_posts;
This query simply finds posts with the old author ID and assigns them the new author ID! That simple.
4. Manually Reset Password:
You might be great at setting passwords, but what happens when you forget your password? SQL to the rescue! Run the following command.
UPDATE wp_users SET user_pass = MD5(‘<password>) WHERE wp_users.user_login =’admin`;
Now this is a bit complicated, right? Most good softwares don’t store passwords as plain text for the simple reason that in case of a hack or compromise of data, at least the direct passwords should not be visible. We hash it using an MD5 algorithm which is not easy to decrypt.
We use MySQL’s built-in MD5 algorithm here and hash the updated password using the username. This can be mighty helpful if you somehow get locked out of the admin area!
5. Change Post Content:
How about a new way to do some search and replace? Run the following query.
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘<old_content>’, ‘<new_content>’);
What this would do is that it would search all your posts for <old_content> and replace it with <new_content>. To perform it for a single post, run the following:
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘<old_content>’, ‘<new_content>’) WHERE id = <post_id>;
This would do the replacements in a single post only.
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘www.example.com’, ‘www.example2.com’);
6. Change Admin Username:
You hate logging into WP Admin with the admin username, don’t you? Even I do! But alas, WordPress doesn’t allow us to change the admin username! Don’t worry, SQL comes to the rescue yet again!
UPDATE wp_users SET user_login = ‘coolAdmin’ WHERE user_login = ‘admin’;
Next time you can login to WP Admin using ‘coolAdmin’!
In the final analysis, I sincerely hope that these SQL queries really helped you get on track with some issues that are known to trouble innocent WordPress users since time immemorial! Always remember that all plugins use a PHP wrapper over these same SQL queries and you can get things done faster if you know a little bit of SQL. Believe me, it’s not that hard too! SQL is just plain English.