As a business owner, you always take care of your online web presence and try to cut downtime and cost as well as wish to produce excellent results. Here, I would like to share with you a few tricks which will help you to resolve your issues very quickly without developers help and keep away from long descriptive articles.

Database is used for storing a collection of information or data into the computer system. Hereby, I would like to share with you a few issues and basic SQL queries to get the desire results in a couple of minutes.

First Step:

If you’ve more than one website in your host account, then before executing any query you need to make sure you are modifying the correct database. To find the right database name, you need to open your specific WordPress installation in your “wp-config.php” file on the string:

Once you find out your database, you need to find table prefix, which you can find at the bottom of the page settings. Because, while executing SQL Command you need to specific table prefix.

As I have already highlighted the database name on the above attached screenshot which is “_wp622” and table prefix “wpc7_”. I assume you’ve a clear idea about it, then we can go to “Database Manager”. Just to inform you that, most of all the control panel uses “PHPMyAdmin” for database management.

Then after, we need to find the database correct database from the list and click on it.

Before executing any modification, we always suggest all readers to take a backup, so you can promptly restore it if anything goes wrong.

Shortly, we will come with our detail-oriented guide on “How to backup WordPress site”. But at this moment, I will give you a quick 1,2,3 step demo to backup:

  1. First click on Export button
  2. Secondly click on Export Method and select your desire Format (Below I have attached screenshot)
  3. That’s it. Click on GO

If you’ve executed this above-shared path then, it’s good to go for the next steps on the SQL Query execution part.

Issue 1: Replace URL

As per Google pre-requisite, If you have made a plan to update SSL certificate to a site which is mandatory then you can use below-mentioned code:

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

Let’s say, your existing URL start with http://www.xyz.com and you wish to change it by https://www.xyz.com then in this case you need to do the modification in the table called “wp_options”.

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

Once you execute the command wait for the success message:

The most essential part is to check the success message because it may be a scenario wherein a small mistake in query can throw an error, and the system will not execute the command and as a result, you will not find desire changes. For your reference, below I have shared with you an error message.

Issue 2: Create New Admin User

For instance, if you wish to add one more “Admin Role” to your installation, then you can write the following command and change according to your requirement.

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

Note: I’ve just created a sample code for you, but you need to change the table name, login credentials, and database name as per your requirements.

As a result, you may receive the following success output.

Issue 3: Change Admin Login Username

To change the login username in WordPress, you need to click on the MySQL tab and execute the following command.

Note: You need to change the information as per your WordPress Installation table name and username and desire username in the query.

UPDATE wp_users SET user_login = 'newlogin' WHERE user_login = 'admin';

Once, the query will be executed; you must check the success message.

Issue 4: Change Admin Password

Below I’ve written query to change the password for secureduser.

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

Click on Go button and confirm the success message:

Issue 5: Delete Spam

If you’ve published a lot of posts and open the comment section for interaction between live users then you may face an issue of spam comments. If you tried to filter those spam approved comments, and even wanted to delete not approved comments will be a time-consuming and tedious job for anyone. By using SQL query, you can promptly resolve this issue, let me share with you how:

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

Below I have shared with you successful output:

Issue 6: Delete All Unapproved Post Comments

When you’re receiving a lot of comments on your articles and no time to read them or respond to them then you can delete all those comments by using below one-line command.

DELETE FROM wp_comments WHERE comment_approved = 0

Note: Just to inform you that, comment_approved section did not require any changes as this column name shows the default function values and it’s not changeable. So, keep it as it is and execute your query.

As mentioned above, must check and confirm the outcome for successfully executed query:

So, what we have shared with you is, if you have a bigger website; then you need to spend more time on it to resolve the above shared issues. By executing SQL queries you can reduce 10x more time and get the result quickly.

In this case, if you save a lot of valuable time by executing these commands then you can utilize your precious time in important things like improving business, managing and understanding customer queries, content planning, and looking for some inspiring ideas for your business too.

Happy blogging!