15 Most Important WordPress SQL Queries: Highly Beneficial

WordPress keeps the track of all information and SQL queries that are executed and stored in MySQL database, whether there are posts, comments, pages, categories, blog roll, any plug-in settings or anything. These all are stored by WordPress admin backend that allows you to control and edit all the aspects of information with ease. Basically MySQL is a free relational database management system (R.D.B.M.S) that is mostly available on web hosting services.

WordPress Database can be edited in endless reasons. For example, say you have hundreds or even thousands of posts all within a database and you in that you need to make site wide changes. Going through each record via the admin and making those changes can be time consuming and even the possibility of committing errors raises. So, the only option left with you is to roll up the profiles and opt for the WordPress MySQL database and executing all the required changes in the queries. This will do all the work quickly while maximizing your productivity effectively and efficiently. For further information about WordPress you can have a look in WordPress database description.

This blog explain the mostly used WordPress SQL queries that can be highly beneficial for customization of article or posts, deleting approved comments, removing short codes or may be resetting your WordPress password manually. Below mentioned queries are really beneficial for your WordPress partner. Hope you will like the blog and do let me know if I missed any important query.

Backup Your WordPress Database

Before we proceed further with any changes in the database, be sure to take the back up of your database. it’s a good practice as it ensures that even if you are wrong then you still be able to recover your data.

So for the backup part, you need to install the Db-backup as provided by the WordPress or you can even download the WP-DB manager. But if you wish to take the backup of your database manually then you can prefer PhpMyadmin.

Here are a few steps that you need to follow for taking database manually:

1. Firstly login to your phpMyAdmin.
2. Now select your WordPress database.
3. Thirdly, click on Export at the top of the navigation.
4. Now you need to select the tables you want to backup, or select all tables to back up the whole database.
5. Select SQL to export as keeping the extension as .sql.
6. Check the “Save as file” checkbox.
7. Choose compression type, select gzipped to compress the database to a smaller size.
8. Finally click Go, and a download window will prompt you to save your backup database file.

Most Important WordPress SQL Queries:

Here I’m presenting some the most important SQL queries that will provide you an easy way to run your queries. Well as all you know that PhpMyadmin is counted one of the superb database editing tools. So here in this article we will use this to run all our SQL queries.

You need to follow these 2 steps before running any query:

1. Login to phpMyAdmin panel and select your WordPress database.
2. Click on the SQL tab which will bring you to a page with a SQL query box.

run_sql_query

List of WordPress SQL Queries

Now you are ready to run all most important WordPress SQL queries.

 To Add A Custom Field To All Articles And Pages:

If you want to add a customized field to all the articles and pages then you need to replace the ‘universalCustomfield’ with the replaced name and then replace the ‘myvalue’ to the value of your choice.

Adding a well customized field in the pages and articles.

This query will help you in customizing the particular fields and articles.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, ‘OwnCustomField’
AS meta_key ‘myvalue AS meta_value FROM wp_posts
WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = ‘MonCustomField’);

Add a Custom Field in Articles Only

Now talking about the customization in articles only then you can run the below written query. By this you will be able to customize the article field.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, ‘MyCustomField’
AS meta_key ‘myvalue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = ‘MonCustomField’)
AND post_type = ‘post’;

Adding a Custom Field in only Pages

This query will help you in particular customization of pages only. You can run the below written query.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, ‘MyCustomField’
AS meta_key ‘myvalue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = ‘MonCustomField’)
AND post_type = ‘page’;

How to Update an Email Address with the WordPress Database:

Here mentioning another good example of replace function by which you can add Email Address in the WordPress database. The below written query will replace the email address that has been mentioned in the comment field with a fresh one.

UPDATEwp_commentsSETcomment_author_email= REPLACE(comment_author_email,’old-email@address.com’,’new- email@address.com’ );

 Now Deleting All the Spam Comment

When we talk about deleting the spam comments then this small snippet are the life saver. This will remove all your spam comments by just running the below mentioned query.

DELETE FROM wp_comments WHERE wp_comments.comment_approved = ‘spam’;

Deleting All Unapproved Comments

When you want to remove all your unapproved comments from the blog the then this SQL query does the magic. It will delete only the unapproved comment and would not touch your approved comments.

DELETE FROM wp_comments WHERE comment_approved = 0;

Disable Comments on Older Posts

The another important SQL query for disabling the comments for older posts then you should mention the comment_status as open, closed, or registered_only. This will help you disabling the comments from the older posts.

UPDATE wp_posts SET comment_status =’closed’ WHERE post_date<‘2010-01-01’AND post_status =’publish’;

Enabling and Disabling Of Trackbacks and Pingbacks

If you want to enable the trackback or disable the trackback then you need to just mention the status of the comment as open, closed or registered. This will enable and disable your pingbacks.

For globally enabling of pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = ‘open’;

For globally disabling of pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = ‘closed’;

Enabling and disabling of Trackbacks before a certain date:

This query will help you in disabling the trackback from a particular date, you just need to specify the ping status as open/ close. You can even mention the date by which the editing is to be done that need to be enable or disabled.

UPDATE wp_posts SET ping_status = ‘closed’ WHERE post_date < ‘2010-01-01’ AND post_status = ‘publish’;

Identify and delete posts that are ‘X’ days old

This query will help you to identify and delete the post from any particular day. Here you can mention the days like ‘X’ days old etc. when you need to identify the post that how much day it is old then simply run this query and remember to replace the ‘X’ with the number of days you are looking for.

SELECT*FROMwp_postsWHEREpost_type=’post’ANDDATEDIFF(NOW(),post_date')> X

To delete any posts that are over ‘X’ amount of days run this query:

DELETE FROM wp_posts
WHERE
post_type = 'post'
AND DATEDIFF(NOW(),
post_date) > X

Removing short codes:

Everyone knows that the shortcodes of wordpress is excellent but sometime you need to get rid of the unwanted codes then you can use this query.

UPDATE wp_post SET post_content =replace(post_content, '[tweet]', '' ) ;

Changing the WordPress post into pages and changing the pages into post

By this query you can change the word press pages into post and can convert the post into pages. You just need to run this below query and rest it will do. Remember the query will run through PHPMyAdmin

UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'.

Change author attribution on all posts at once.

If you want to convert the author attributes on every post by once then you need to run this query and it will retrieve the IDs of the word press author.

SELECT ID, display_name FROM wp_users;

And once you have run this query you will retrieve an old and new query. Afterwards you can run the below given query but just remember to change the new author id with the new retrieved id and old id with the old one.

UPDATEwp_postsSETpost_author=NEW_AUTHOR_ID WHEREpost_author=OLD_AUTHOR_ID;

Deleting the post revisions

From this query you will be able to delete the posts revision.

DELETE FROM wp_posts WHERE post_type ="revision";

Enabling and Disabling of WordPress plugins through the database.

Now if you wish to enable and disable the features of wordpress plug-in then first of all login to the wordpress admin and then activate the required plug in then this below given query will help you in enabling and disabling the plug-in instantly.

UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';

Changing the URL destination of a WordPress

Now if you need to change the URL of the desired destination in a wordpress then you need to run the below given query.

UPDATEwp_optionsSEToption_value=replace(option_value,'http://www.old-site.com','http://www.new-site.com')WHEREoption_name ='home'ORoption_name = 'siteurl';

Then you will have to change the URL from the table wp_posts with this snippet:

UPDATEwp_postsSETguid=replace(guid,'http://www.old-site.com','http://www.new-site.com);

And finally you want to your content of the desired post then you can do this perfectly but try to keep the old URL link.

UPDATEwp_postsSETpost_content=replace(post_content,' http://www.ancien-site.com ', ' http://www.nouveau-site.com ');

How to reset WordPress password manually

Here you can reset you password manually in the WordPress just by running this SQL query.

UPDATEwordpress.wp_usersSETuser_pass’=MD5(‘PASSWORD’)WHERE wp_users.user_login =admin LIMIT 1;

Searching and then replace the content in the post

To change the original Text that you want to replace with the Replace Text option by just running the search and replace query mentioned below.

UPDATE wp_posts SET post_content
= REPLACE (post_content,
‘OriginalText’,
‘ReplacedText’);

Webgranth

Admin from India is a founder of WebGranth, an entity of Sparx IT Solutions with over 8 years of experience on different verticals of web design & development. In his quest to spread the knowledge regarding web development, he has been contributing in WebGranth by submitting his valuable blogs.