As a WordPress website owner, there are numerous tasks to take care of on a daily basis. From updating blog content to adding new products and changing prices, the list goes on. Additionally, regular plugin updates and providing developers with access are also part of the daily WordPress routine. However, tweaking settings can be time-consuming, especially when following lengthy tutorials with multiple instructions for a single action. Fortunately, many changes can be made with a simple SQL query, which can be run in your database manager menu. In this article, we will provide you with some basic SQL queries that can help you achieve results in just a few minutes.
First Steps
Before executing any SQL queries, you need to ensure that you are modifying the correct database when there is more than one website in your hosting account. You can find the database name assigned to a specific WordPress installation in your wp-config.php file on the string:
define (‘DB_NAME’, ‘yourdbname’)
You also need to pay attention to the table prefix, as you will need it to specify when running SQL commands. It is located in the same file at the bottom of the page settings.
For example, if the database name is _wp622 and the table prefix is wpc7_, you can proceed to the Database manager. Most control panels use PHPMyAdmin for database management.
Once you are there, find your database name in the list and click on it. Before making any changes, create a backup for this database to quickly restore it in case something goes wrong. We cover how to backup WordPress in detail in our general backup guide. To give you a quick walkthrough, choose the Export option, choose the method and format according to your needs and press Go (here we have set all the default options).
Replace URL
If you plan to update your website URL by adding an SSL certificate or making other modifications, use the following command:
UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldurlofthewebsite.com’, ‘http://www.newurlofthewebsite.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
For example, if you want your URL to be https//: and your domain name is example.com, modify the wp_options table name and the URL as follows:
UPDATE wpc7_options SET option_value = replace(option_value, ‘http://www.example.com’, ‘https://www.example.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
Ensure that you receive a success message after executing the command. Note that this command is basic and will only replace the main URL of your website. If you plan to change the domain name of your website from example.com to test.net, you will need to modify it including all the tables where the URL of your website is met such as wp-posts, wp-postmeta, and tables with rows containing URLs generated by plugins (for instance, WooCommerce). You will need at least basic SQL training to do that. Alternatively, you can open the database you exported in any text editor and replace all mentions of your old domain with the new one using Ctrl+H. If this sounds too complicated, other tools exist that can automate this process (such as the interconnect/it database search & replace script or the Better Search & Replace plugin based on the same script) while just uploading the file to your website folder for the working site and opening it in a browser.
Create New Admin User
If you need to add a new user with an Admin role to your installation, use the following command and modify it according to your preferences:
INSERT INTO `wp_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`)
VALUES (‘yourlogin’, MD5(‘yourpassword’), ‘firstname lastname’, ’email@example.com’, ‘0’);
INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`)
VALUES (NULL, (Select max(id) FROM wp_users),
‘wp_capabilities’, ‘a:1:{s:13:”administrator”;s:1:”1″;}’);
INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`)
VALUES (NULL, (Select max(id) FROM wp_users), ‘wp_user_level’, ’10’);
Modify the tables and login values according to your desired username and databases. For example, if you want to create a user mydeveloper with the password mypassword, whose name is John Doe and email is test@test.com, use the following query:
INSERT INTO `wpc7_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`)
VALUES (‘Mydev’, MD5(‘mypassword’), ‘John Doe’, ‘test@test.com’, ‘0’);
INSERT INTO `wpc7_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`)
VALUES (NULL, (Select max(id) FROM wp_users),
‘wp_capabilities’, ‘a:1:{s:13:”administrator”;s:1:”1″;}’);
INSERT INTO `wpc7_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`)
VALUES (NULL, (Select max(id) FROM wpc7_users), ‘wp_user_level’, ’10’);
Note that you don’t need to modify the rows names (i.e., wp_user_level) or play with numbers 0, 10, 13 as they represent the user role and corresponding rights. After modifying the query, press Go. The output should show successful results.
Change Admin Login Username
To change the username login name, go back to the MySQL tab and run the following command:
UPDATE wp_users SET user_login = ‘newlogin’ WHERE user_login = ‘admin’;
For example, if your default username is mydeveloper and you want to set secureduser instead of it, use the following command:
UPDATE wpc7_users_users SET user_login = ‘secureduser’ WHERE user_login = ‘mydeveloper’;
Ensure that you receive a success message after executing the command.
Change Admin Password
Following the security rules of regular password changes, you may also want to change the password for your secureduser. Here is the query for it:
UPDATE wp_users SET user_pass = MD5( ‘new_password’ ) WHERE user_login = ‘youruser’;
For example, if you want to change the password for secureduser to $tR0ngP@s$w03D, use the following command:
UPDATE wpc7_users SET user_pass = MD5( ‘$tR0ngP@s$w03D’ ) WHERE user_login = ‘secureduser’;
Press Go and wait for the success message.
Delete Spam
For users who publish a lot of posts and leave comments open for interaction, the issue with spam comments may become really painful. While you may filter comments by manual approval, you will probably want to find a way to quickly delete everything you have not approved. Here’s how:
DELETE FROM wp_comments WHERE wp_comments.comment_approved = ‘spam’;
For example, if you want to delete spam comments in wpc7_comments, use the following command:
DELETE FROM wpc7_comments WHERE wpc7_comments.comment_approved = ‘spam’;
Note that the prefix needs to be modified in both places where it’s met since wp_comments.comment_approved is a separate field in the table. Ensure that you receive a success message after executing the command.
Delete All Unapproved Post Comments
At some point, you may realize that you are tired of filtering and reading comments for articles before making the final decision to publish them, so you want to delete them. There is a command for that:
DELETE FROM wp_comments WHERE comment_approved = 0
For example, if you want to delete all unapproved post comments in wpc7_comments, use the following command:
DELETE FROM wpc7_comments WHERE comment_approved = 0
Ensure that you receive a success message after executing the command.
Conclusion
While it may seem that following these instructions may take longer than doing things manually, that’s not the case. The bigger your website is, the more time you have to spend on it. One single action performed separately for ten posts and you end up with ten times more time spent on execution. By running these commands, you save a ton of your precious time and can use it on more important things such as content planning or looking for inspiration ideas. Happy blogging!
