Beginner's GuideTutorialsWordpress

Time Saving SQL Queries for WordPress


In case you’re a WordPress web page proprietor, there’s quite a few stuff to take care of.

Constantly updating your weblog content material materials, together with new merchandise, altering prices, and we do not even level out frequent plugins change, providing a dev with entry and doing many further points as a part of every day WordPress routine.

Usually, if it is important to tweak settings, you uncover a tutorial of a protracted web-page with many instructions to hold out a single movement. Nonetheless what in case you knew that the majority of these changes is likely to be modified with a simple SQL query, a command you run in your database supervisor menu.

Since you are a busy specific particular person and saving your time is a priority, listed under are the important SQL queries that will allow you to comprehend the results in a few minutes.

First Steps

When there’s a few web page in your web internet hosting account, earlier to executing SQL queries, it is important to only ensure you’re modifying the right database. You would discover the database establish assigned to a selected WordPress arrange in your wp-config.php file on the string:

define (‘DB_NAME’, ‘yourdbname’)

Moreover, be aware of the desk prefix, as you may want it to specify as quickly as working SQL directions. It’s positioned within the equivalent file on the bottom of the net web page settings.

WordPress Table Prefix

On this occasion above, the database establish is _wp622. The desk prefix is wpc7_. Now, as soon as we realize it, let’s go to the Database supervisor. Practically all of administration panels use  PHPMyAdmin for database Administration.


As quickly as you are there, uncover your database establish inside the itemizing and click on on on it:

Select Database

Sooner than making any changes, simply ensure you create a backup for this database, to have the power to shortly restore it merely in case one factor goes mistaken.

We cowl how to backup WordPress intimately in our regular backup info. Nonetheless to current you a quick walkthrough, choose the Export chance, choose the tactic and format in response to your desires and press Go (proper right here now we have now set all the default decisions):

Export Database

As quickly as completed, you are safe to proceed. Press the menu SQL and let’s get started:

SQL Menu

Trade URL

Within the occasion you intend to exchange your web page URL by together with the SSL certificates to your web page or by doing totally different modifications, make sure you make use of the following command:

UPDATE wp_options SET option_value = change(option_value, '', '') WHERE option_name = 'home' OR option_name = 'siteurl';

Let’s say I would love my URL to be https//: and my space establish is On this case, I want to swap the wp_options desk establish and the very URL:

UPDATE wpc7_options SET option_value = change(option_value, '', '') WHERE option_name = 'home' OR option_name = 'siteurl';

SQL Replace Sitename

And await successful message.

SQL Success Message

Why is it so essential to see it? Within the occasion you press Go nevertheless there are some errors inside the query, the system will not be capable to meet the request and you may get hold of errors that implies that the execution failed. The error will look as follows and might level out the reason why the strategy failed.

SQL Error Message

Please phrase that this command is a main one and might change solely the precept URL of your web page, so in case you intend to change the realm establish of your web page from to examine.web, you may want to change it along with all the tables the place the URL of your web page is met equal to wp-posts, wp-postmeta and as well as take care in regards to the tables with rows containing URL generated by plugins (for example, WooCommerce).

To do this, you may needn’t lower than basic SQL training. One other chance might be to open the database you exported in any textual content material editor and with Ctrl+H change all the mentions of your outdated space to the model new one. If this sounds too subtle, a variety of various devices exist that help automate this course of (such as a result of the interconnect/it database search & replace script, or the Better Search & Replace plugin based totally on the equivalent script)  whereas merely importing the file to your web page folder for the working web site and opening it in a browser.

Create New Admin Individual

If it is important so as to add a model new client with Admin function to your arrange, you may have to make use of the following command and modify it relying in your preferences:

INSERT INTO `wp_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`)   VALUES ('yourlogin', MD5('yourpassword'), 'firstname lastname', 'email [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');

Observe, that it would be best to swap the tables and login values in response to your required username and databases.

I want to create a client mydeveloper with the password mypassword, my developer’s establish is John Doe and the e-mail is [email protected]. So my query will look as follows:

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 Create a new Admin user

Observe that I don’t wish to swap the rows names (i.e. wp_user_level or play with numbers 0, 10, 13 as they indicate the patron perform and corresponding rights. As quickly as completed, I press Go.

The output must current the worthwhile outcomes like the following ones:

SQL Create a new Admin user Results

Change Admin Login Username

To change the username login establish, return to MySQL tab and run the following command:

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

Let’s say your default username is mydeveloper, the one you created sooner than and in addition you want to set secureduser instead of it. In our particular case, we run the following command, as now we have now moreover the default prefix wp7c_:

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

Time Saving SQL Queries for WordPress

The worthwhile output appears to be as follows:

SQL Change Admin login username Success

Change Admin Password

Following the security guidelines of standard password adjustments, you might also want to change the password in your secureduser. Proper right here is the query for it:

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 admin password

Press Go and await the success message:

SQL Change admin password Success

Delete Spam

For purchasers who publish quite a few posts and go away the suggestions open for interaction, the issue with spam suggestions may develop into truly painful. While you may filter the suggestions by information approval, you may most certainly want to uncover a strategy to shortly delete the whole thing you have not permitted. Proper right here’s how:

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

When personalised to the precise case we’re discussing proper right here:

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

SQL Delete Spam

Observe: the prefix should be modified in every places the place it’s met, since wp_comments.comment_approved is the separate topic inside the desk.

Proper right here is the worthwhile output:

SQL Delete Spam Success

Delete All Unapproved Put up Suggestions

Eventually, it is doable you may discover that you simply’re tired of filtering and finding out the suggestions for the articles sooner than making the last word option to publish them, so that you simply want to delete them. There is a command for that:

DELETE FROM wp_comments WHERE comment_approved = 0

Observe, that the comment_approved half does not need the modification as this tab represents the default function inside the desk that is immutable.

The modified command appears to be as follows:

DELETE FROM wpc7_comments WHERE comment_approved = 0

SQL Delete Unapproved Comments

As quickly as completed, you might even see the identical outdated consequence for effectively executed directions:

SQL Delete Unapproved Comments Success

Whereas it may seem that following the instructions may take longer than merely doing points manually, that’s not so. The bigger your web page is, the additional time you have to spend on it. One single movement carried out individually for 10 posts and you end up with 10x further time spent on execution.

So in precise reality, you save a ton of your treasured time by working these directions and should use this time on further crucial points equal to content material materials planning or searching for inspiration ideas.

Comfy operating a weblog!


Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button

Adblock Detected

Please consider supporting us by disabling your ad blocker