If you are the owner of a WordPress site, you need to deal with many things.

Constantly update your blog content, add new products, change prices, we didn’t even mention regular plugin updates to provide developers with access and do more as part of the daily WordPress routine.

Sometimes, if you need to adjust settings, you will find a long web tutorial with many instructions for performing a single operation. But what if you know that most of these changes can be changed with a simple SQL query (a command you run in the database manager menu)?

Since you are a busy person and saving your time is a top priority, here are some basic SQL queries that can help you get results in a few minutes.

first step

When you have multiple websites in your hosting account, you need to make sure that you are modifying the correct database before executing SQL queries.You can find the database name assigned to a specific WordPress installation wp-config.php File on string:

define (‘DB_NAME’, ‘yourdbname’)

WordPress definition database

Also, pay attention to the table prefix, because once you run the SQL command, you will need it to specify it. It is located in the same file at the bottom of the page settings.

WordPress table prefix

In the example above, the database name is _wp622. The table prefix is ​​wpc7_. Now, when we know it, let’s go to the database manager. Most control panels use PHPMyAdmin for database management.

phpMyAdmin

Upon arrival, find your database name in the list and click on it:

Select database

Before making any changes, make sure to create a backup of this database so that you can quickly restore it if something goes wrong.

We detailed how to backup WordPress in the general backup guide.But for you to browse quickly, please select Export Option, select the method and format according to your needs, and then press go (Here we set all default options):

Export database

Once completed, you can safely continue.Press menu Check for phrases let’s start:

SQL menu

Replace URL

If you plan to update your website URL by adding an SSL certificate to your website or making other modifications, make sure to 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';

Suppose I want my URL to be https//: and my domain name is example.com. In this case, I need to modify the wp_options table name and URL:

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';

SQL replacement site name

And wait for the success message.

SQL success message

Why is it so important to see it?If you press go However, there are some errors in the query, the system will not be able to satisfy the request, and you will receive an error indicating that the execution failed. The error will look like the following and indicate why the process failed.

SQL error message

Please note that this command is a basic command and will only replace the main URL of your website, so if you plan to change the domain name of your website from example.com to test.net, you need to modify it, including all the URLs that satisfy your website Tables such as wp-posts, wp-postmeta, and also process tables containing rows of URLs generated by plugins (such as WooCommerce).

To do this, you need at least SQL basic trainingAnother option is to open the database you exported in any text editor and use Ctrl+H to replace all the mentioned old domains with the new ones.If this sounds too complicated, there are a variety of other tools that can help automate this process (e.g. Interconnect/IT database search and replacement Script, or Better search and replace plugin Based on the same script), at the same time just upload the file to the website folder of the work site and open it in the browser.

Create a new admin user

If you need to add a new user with an administrator role to the installation, you must 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 protected]', '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');

Please note that you need to modify the table and login values ​​as needed username with database.

I want to create a user My developer With password My password, My developer’s name is John Doe Email is [email protected]. So my query will look like this:

INSERT INTO `wpc7_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`)
 VALUES ('Mydev', MD5('mypassword'), 'John Doe', '[email protected]', '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'

SQL creates a new admin user

Note that I don’t need to modify the row name (i.e. wp_user_level Or play with numbers 0, 10, 13 Because they mean user roles and corresponding permissions.After finishing, I press go.

The output should show successful results, as shown below:

SQL creates a new administrator user result

Change the administrator 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';

Assuming your default username is My developer, The one you created and set up Security user Instead of it. In our special case, we run the following command because we also have the default prefix wp7c_:

UPDATE wpc7_users_users SET user_login = 'secureduser' WHERE user_login = 'mydeveloper';

The successful output is as follows:

SQL changed the administrator login username successfully

Change the administrator password

Follow the security rules of regular password change, you may also want to change your password Security user. This is its query:

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'youruser';

For this particular password change command, our command is as follows:

UPDATE wpc7_users SET user_pass = MD5( '[email protected]$w03D' ) WHERE user_login = 'secureduser';

SQL change administrator password

according to go And wait for the success message:

SQL successfully modified the administrator password

Delete spam

For users who publish a large number of posts and keep comments open and interactive, the issue of spam comments can become very painful. Although you can filter comments by manually approving, you may want to find a way to quickly delete all content that you have not approved. That’s it:

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

When customizing for a specific situation, we will discuss here:

DELETE FROM wpc7_comments WHERE wpc7_comments.comment_approved = 'spam';

SQL delete spam

notes: The prefix needs to be modified in the two places it encounters, because wp_comments.comment_approved Is a separate field in the table.

This is the successful output:

SQL delete spam successfully

Delete all unapproved post comments

At some point, you may realize that you are tired of filtering and reading the comments of the article before making the final decision to publish the article, so you want to delete them. There is a command:

DELETE FROM wp_comments WHERE comment_approved = 0

Please note that the Comment_Approve The part does not need to be modified, because this tab represents the immutable default function in the table.

The modified command is as follows:

DELETE FROM wpc7_comments WHERE comment_approved = 0

SQL delete unapproved comments

After completion, you can see the usual results of successful execution of the command:

SQL successfully deleted unapproved comments


Although it seems that following the instructions may take longer than manual operations, this is not the case. The bigger your website, the more time you spend on it. Performing a single operation on 10 posts will eventually take 10 times longer to execute.

So, in fact, by running these commands, you can save a lot of precious time, and you can use this time for more important things, such as content planning or finding ideas for inspiration.

Happy blogging!