Silva Web Designs - Web Design / Web Development Specialists
  • Home
  • About
  • Services
  • Portfolio
  • Team
  • Blog
  • Contact
Scroll

Tag: old posts

17 Time-Saving WordPress SQL Queries for WordPress

Posted on 31st August 20202nd September 2020

As you may already know, WordPress stores a lot of non-used information within your MySQL database. This can be things like posts, pages, comments, shortcodes, plugin settings and a lot more. The issue here though is that with such a large database that will always be growing in size, you need to make changes and optimise and clean your database. If we were to clean up a database via the CMS dashboard, this can become a very time-consuming task which is why we made this article today. We are going to give you some very useful time-saving WordPress SQL Snippets to help clean up your website!

Before we start…

MAKE A BACKUP!.

Mistakes can happen, and the database is a very important thing, let’s ensure we can recover the site if a mistake is made. The best way would be to download a backup of your site then do the changes locally, you don’t want your live website to go down right? If you aren’t quite sure how to make a backup, you can read the following article.

Now we have the backup made, let’s dig into these awesome queries!

1. Delete WordPress Post Meta

When you install a WordPress Plugin, they make use of the post meta table to store data. When you remove the plugin, the data will actually still remain in your post_meta table even though it’s no longer being used! We can remove this by running the following SQL query:


DELETE FROM wp_postmeta WHERE meta_key = 'MyMetaKey';

Just change MyMetaKey to your own value before running this query.

2. Identify Unused WordPress Tags

If you run a query to delete old posts (like the one we did above), the old tags will remain within the database. The following query allows you to identify all of the unused tags:


SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Just change post_tag to the ones you are wanting to remove.

3. Delete WordPress Spam Comments in Bulk

This snippet we’ve used quite a lot of times and it really does save time. You can simply delete WordPress spam comments in bulk by running the below query:


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

4. Batch Delete All Unapproved WordPress Comments

The following SQL snippet will remove all thee unapproved comments without effecting the ones which are approved:


DELETE FROM wp_comments WHERE comment_approved = 0

5. Disable WordPress Comments on Old Posts

For this query, just adjust the comment_status to either open, closed, or registered only. Now, all you have to do is specify the date by editing the value 2017-01-01 to the date in which you want to delete posts from.


UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2017-01-01' AND post_status = 'publish';

This query will delete all the posts when the post data is less than 1st January 2017.

6. Disabling & Enabling WordPress Trackbacks & Pingbacks

In this one, we just need to define the code>comment_status as either open, closed, or registered_only.

To globally enable pingbacks/trackbacks for all users, we can use:


UPDATE wp_posts SET ping_status = 'open';

To globally disable pingbacks/trackbacks for all users, we can use:


UPDATE wp_posts SET ping_status = 'closed';

Furthermore, we can specify the ping_status as either open or closed. But, also specify the date by editing the 2017-01-01 to suit your needs:


UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2017-01-01' AND post_status = 'publish';

8. Select & Delete WordPress Posts that are over 'X' Days Old

If you ever need to select and delete posts that are over a certain amount of days old, this snippet will help for sure!

To select any posts that are over 'X' amount of day, run this query. Remember to replace the X with the number of days you are looking for:


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

Once you are happy with the results of this, you can then safely delete them as follows:


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

We simply replaced the SELECT * to the DELETE command.

9. Removing Unwanted WordPress Shortcodes

WordPress shortcodes are a great tool to use, but when you decide to stop using them, they can remain within your post content...

With this query, we can remove any unwanted shortcodes, simple replace notusedcode with the shortcode you wish to remove:


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

10. Change Author Attribution On All WordPress Posts

Ever built the website then the author of the site wanted to replace your user ID with their own? This can be done in several ways but a simple SQL query can resolve this quite quickly. Firstly, we will need to find the User ID as follows:


SELECT ID, display_name FROM wp_users;

Once you have found the old ID and the new ID in which you want to replace, you can run the following command:


UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Replace NEW_AUTHOR_ID with the ID of the user to take the new ownership, and set OLD_AUTHOR_ID which is the current owner.

11. Batch Deleting WordPress Post Revisions

Post revisions can be extremely handy, however, they also increase the size of your MySQL database. You can manually delete post revisions but this is a very time-consuming task. You can remove these using thing handy SQL query:


DELETE FROM wp_posts WHERE post_type = "revision";

12. Disable or Enable All WordPress Plugins

If you've ever encountered the horrible white screen of death and found yourself unable to access the site or WordPress Admin panel after activating a new plugin. Then this little snippet will come of great use. This will disable all plugins instantly and will allow you to regain access:


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

13. Change the Destination URL of a WordPress Site

If you've decided to move your WordPress site manually from one server to another, the first thing you will need to do is tell WordPress the new URL of your website.

Remember and change http://www.old-site.com to your old URL, and the http://www.new-site.com to your new URL.

The first command to use is this one:


UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-site.com', 'http://www.new-site.com') WHERE option_name = 'home' OR option_name = 'siteurl';

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


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

Finally, you'll need to do a search through the content of your posts to be sure that your new URL link is not still linked with any of the old URL references:


UPDATE wp_posts SET post_content = replace(post_content, ' http://www.old-site.com ', ' http://www.new-site.com');

14. Manually Reset your WordPress Password

If you only have a single user on your WordPress installation, and the login name is 'admin' for example. You can reset your password with this simple SQL query. Once executed, it will replace PASSWORD with your new password.


UPDATE 'wordpress'.'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'wp_users'.'user_login' ='admin' LIMIT 1;

15. Search and Replace WordPress Post Content

To search and replace post content, use the following code. Just replace OriginalText with the current text and replace NewText with your new text.


UPDATE wp_posts SET 'post_content'
= REPLACE ('post_content',
'OriginalText',
'NewText');

16. Changing the URL of WordPress Images

If you need to change the paths of your images, you can run the following SQL command:


UPDATE wp_posts
SET post_content = REPLACE (post_content, 'src="https://www.myoldurl.com', 'src="https://www.mynewurl.com');

17. Change the Default 'Admin' WordPress Username

With every WordPress installation, there will be an account which has the default Admin username. By changing the default username, it will give your WordPress admin panel additional security. To do this, change YourNewUsername to your new name:


UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin';

And that's all we have today. We're pretty sure this will save you a great deal of time and we hope it helps you!

Nathan da Silva - Profile

Posted by: Nathan da Silva

Nathan is the Founder of Silva Web Designs. He is passionate about web development, website design and basically anything digital related. His main expertise is with WordPress, Magento, Shopify as well of many other frameworks. Whether you need responsive design, SEO, speed optimisation or anything else in the world of digital then get in touch. If you would like to work with Nathan, simply drop him an email at [email protected]

It’s good to share

Posted in WordpressTagged batch delete, clean up, mysql queries, old posts, pingbacks, post meta, shortcodes, spam comments, tags, time saver, time saving, trackbacks, unapproved, WordpressLeave a Comment on 17 Time-Saving WordPress SQL Queries for WordPress

Get in touch

Do you need a professional designer/developer for your next project? Get in touch using the form below.


    Categories

    • CSS
    • HTML
    • JavaScript
    • jQuery
    • MSSQL
    • MySQL
    • PHP
    • Technologies
    • Wordpress

    Recent Posts

    • How To Send Google Analytics Event with PHP
    • Here’s One Simple Way to Boost Your Online Visibility (Without Breaking the Bank)
    • How to Add the Product Price in WooCommerce as a Shortcode
    • How Can You Make Your Digital Marketing Effective?
    • How to Load More Posts using Ajax with a Button or on Scroll in WordPress

    Love our blog?

    Why not sign up to our newsletter for the latest tutorials and web related gossip.

    Statistics

    • 3
    • 834
    • 221
    • Home
    • About
    • Services
    • Portfolio
    • Team
    • Blog
    • Contact

    © Copyright SILVA - 2021 - Cookie Policy | T&C

    This website uses cookies to improve your experience. Accept Read More
    Privacy & Cookies Policy

    Privacy Overview

    This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
    Necessary
    Always Enabled

    Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

    Non-necessary

    Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.

    SAVE & ACCEPT