20 Most Useful SQL Queries for WordPress

WordPress uses SQL databases to store various data such as articles, web pages, categories, and more. You can benefit from the functionality of various SQL snippets and use them to manage, remove/add/complete different things on your WordPress website.

You can use these SQL snippets for WordPress to manage post meta, identify unused tags, add custom fields to all your pages, delete spam comments, disable old comments, remove various shortcodes, convert your posts into pages, and pages into posts, change the author’s attribution throughout the entire WordPress posts, disable/enable all plugins, change the default ‘admin’ username, reset your password, and more.

So, without any delay, here you have an excellent collection of 20 useful SQL snippets for WordPress.

Add a Custom Field to All WordPress Posts & Pages

Take a look at this great code snippet that allows you to add a customized field to each and every post and page that you have in your WordPress database.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyCustomFieldValue AS meta_value FROM wp_posts
WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField');

Identify Unused WordPress Tags

You can automatically identify unused WordPress tags and delete them. Have a look and see if this snippet is useful for your online projects.

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;

Batch Delete All Unapproved WordPress Comments

If your WordPress website is attacked by spam comments, or you just want to delete multiple comments very quickly, this snippet is the right thing for you.

DELETE FROM wp_comments WHERE comment_approved = 0

Disabling & Enabling WordPress Trackbacks & Pingbacks

Use this SQL snippet to successfully enable or disable WordPress trackbacks and pingbacks for all users. You just need to specify the date and mention if it is open or closed.

UPDATE wp_posts SET ping_status = 'open';

Identify & Delete WordPress Posts that are over ‘X’ Days Old

This WordPress snippet identifies and deletes all the posts that are over any selected day that you choose. This helps you save a lot of time and effort.

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

Change Your WordPress Posts Into Pages and Vice-Versa

Here is a great SQL command that enables all pages to convert all pages into posts. Also, you have another command that does the exact opposite.

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

Changing the Destination URL of a WordPress Site

If you are looking to change the URL destination of a WordPress website from one server to another, this snippet is very useful and eases your work.

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

Manually Reset your WordPress Password

Should you need to reset your WordPress password, this code snippet comes in handy and it allows you to manually perform this task.

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

Changing the URL of WordPress Images

Check out this code snippet that you can use to change the paths of your images. This SQL command can be quickly inserted into your website, at it works perfectly.

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

Display conditional comment links

Have a look at this code snippet that allows you to display conditional comment links to your WordPress website. This can be useful in many situations. Check it out!

<div class="metadata">
        By <?php the_author(); ?> on <?php the_time("l, F j, Y"); ?><br />
        <a href="#comments">
                <?php // conditional comment link text
                if (comments_open()) :
                        comments_number(__('Post a Comment'), __('1 Response'), __('% Responses'));
                else :
                        comments_number(__('Comments'), __('1 Response'), __('% Responses'));
                endif; ?>
        </a>
</div>

Delete WordPress Post Meta

Here you have great resources that allow you to successfully delete all WordPress post meta. The post meta stores data on your WordPress each time you install or remove a plugin. Use this code snippet to delete them.

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

Batch Delete WordPress Spam Comments

This is a small but effective code snippet which enables you to delete the WordPress spam comments. This makes it easy to remove spam, close or open comments for old posts, enable/disable pingbacks, and more.

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

Disable WordPress Comments on Older Posts

Copy and paste this code snippet into your WordPress website to disable WordPress comments to older posts. This is a time-saving snippet that comes in handy in many situations.

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

Delete WordPress Comments With a Specific URL

Should you ever need to delete specific comments with a particular URL, this code snippet does just that. Copy and paste it into your WordPress website for great results.

DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ;

Quick copy/paste shortcode snippet

You can rapidly copy-paste any shortcode snippets that you find useful. This enables users to share the post’s short link on various social media websites.

<?php if (function_exists('get_shortlink')) { // WP 3+ ?>
<div class="short-url">Short <abbr title="Uniform Resource Locator">URL</abbr>: <input onClick="this.focus(); this.select();" value="<?php echo get_shortlink(get_the_ID()); ?>" size="30" type="text" /></div>
<?php } ?>

Removing Unwanted WordPress Shortcodes

Use this code snippet to successfully remove all unwanted WordPress shortcodes. You can do this by running this SQL query on your WordPress database.

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

Change Author Attribution On All WordPress Posts

Here you have a great code snippet with instant results that you can easily add to your WordPress website. This comes in handy if you want to change the author’s attribution on every post. The first command, shown below, will get IDs of all users in your WP installation:

SELECT ID, display_name FROM wp_users;

The next command will change attribution of any posts belonging to a specific author to another author of your choice. Please keep in mind that you need to remember author IDs from the first command above.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Disable or Enable All WordPress Plugins

This is a very useful SQL code snippet that you can use for your WordPress website. This allows you to quickly enable or disable all your plugins.

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

Change the Default ‘Admin’ WordPress Username

Earlier we learned how to manually change the password, and now, with the help of this code snippet, you can rapidly change the default ‘Admin’ username into any name you want.

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

Open external links in new tab

Should you want to open external links in a new tab, this code snippet does just that. Copy it into your WordPress website and enjoy its functionality.

$('a[rel^="external"],a[rel$="external"]').attr({target:"_blank",title:"Opens in a new tab or window"}).append('');

The post 20 Most Useful SQL Queries for WordPress appeared first on Web Design Blog | Magazine for Designers.

via http://ift.tt/2oaqQWo

Pages

Web Design