Using SQL to Manage WordPress: the Definitive Guide

DZone 's Guide to

Using SQL to Manage WordPress: the Definitive Guide

In this article, I have compiled a guide as well as 15+ ready to use SQL queries for managing comments and users, batch editing your posts, cleaning up your database, and many more.

· Database Zone ·
Free Resource

WordPress stores a lot of things in the database. Using SQL queries, you can easily perform tasks that would take a lot of time and hassle otherwise. In this article, I have compiled a guide as well as 15+ ready to use SQL queries for managing comments and users, batch editing your posts, cleaning up your database, and many more. 

Before Getting Started

There are several ways to run SQL queries. Most of you probably have a cPanel installed on your server. This is the case if your host is Vidahost, A Small Orange, or In Motion Hosting, for example.

To access phpMyAdmin from cPanel, simply log in to cPanel and click the phpMyAdmin icon in the Databases section.

Once you’ve entered phpMyAdmin, you first have to select your blog database, then click on SQL tab to display the page which allows you to run any kind of queries.

Another option is to use a plugin named SQL Executioner. As the name says, the aim of this plugin is to allow you to run SQL queries on your WordPress database from within the Dashboard.

Important things to note:

  • Always have a fresh backup of your database when applying those SQL queries. You can do so manually or use a WordPress plugin such as WP-DBManager.
  • This article uses default table prefix wp_. Make sure you change the prefixes to match the ones used by your database.

Delete All Comments with a Specific URL

Spam is definitely a problem ith WordPress blogs, happily SQL is here to help.

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

Source: WPRecipes

Delete all Trackbacks

Most people, including me, think that trackbacks are useless. Clean up your blog database with that nifty query.

DELETE FROM wp_comments WHERE comment_type="trackback";

Source: WordPress.org Forums

Close Trackbacks on All Posts at Once

Even better than deleting them, you can close all trackbacks at once with this query.

UPDATE wp_posts SET ping_status = 'closed';

Source: Dig WP

Bulk Delete All Unapproved Comments

Too lazy to check all of your unapproved comments? This is the super fast way to delete them all at once.

DELETE from wp_comments WHERE comment_approved = '0';

Source: WordPress.org Forums

Bulk Delete All Comments Marked as Spam

Got 5400 spam comments and don’t want to go through dozen of pages to delete them? This query is for you.

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

Source: Dig WP

Delete All Post Revisions and Associated Data

Make your database lighter by removing post revisions and all associated data. A query I run on my blogs every 3-6 months.

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision';

Source: Solidly Stated

Remove Unused Shortcodes in Post Content

Shortcodes are really cool, but at some point, you need to be able to remove the unused ones. Rather than editing each of your posts, run this simple query! Replace [tweet] by the unused shortcode to remove.

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

Source: WPRecipes

Replace a Word by Another in Post Content

This can be very useful to update a link, for example.

UPDATE wp_post SET post_content = replace(post_content, 'old_word', 'new_word' ) ;

Add a Custom Field to All Posts

If you’re always using a specific custom field, you better add it on all posts at once rather than editing X amount of your posts. Here you go!

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'MyCustomField'
AS meta_key 'myvalue AS meta_value
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'MyCustomField')
`` AND post_type = 'post';

Source: The Customize Windows

Delete Very Old Posts

Are all your posts very outdated? Here’s an easy way to remove them. For optimal results, you should definitely use 301 redirections to redirect deleted posts to your homepage or updated versions of those posts.

Source: xarj.net

Get a List of All Commentators' Emails

Not really that you should use email of your commentators for anything (except with their consent) but here's the way to get a list of all emails stored in the wp_commentstable. Note the use of DISTINCT to make sure the query won't return any duplicates.

SELECT DISTINCT comment_author_email FROM wp_comments;

Source: Dig WP

Assign Posts to a New Author

If for some reason you want to transfer posts from an author to another, this query is yours. You need to update this query with both the old author and new author IDs before running it.

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';

Source: WPRecipes

Update User Password

Here's a quick way to update any password. Don't forget to replace username by the user name of the user you'd like to update the password.

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

Source: WordPress Support

Batch Disable All Plugins

In case something goes wrong with your blog, it can be a good idea to deactivate all your plugins, in case one of those was the source of the problem. Here's how you can batch disable them using a SQL query.

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

Source: WPRecipes

Downsize Your Database by Removing Transients

Transients are a simple and standardized way of temporarily storing cached data in the database by giving it a custom name and a timeframe after which it will expire and be deleted. But sometimes, transients set by WP and countless plugins can take a lot of space in your database. Good news, transients can be safely removed with this simple query.

DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');

Source: Stack Overflow

Further reading:  WordPress Transients API – Practical examples.

Get Rid of Unused Tags

As your database is probably filled with lots of tags you don't use anymore, you should consider cleaning it with the following:

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

Source: WPMU Dev

Change All Your URLs/Domain Names

WordPress stores absolute URLs within the database. Which means that if you change your blog domain name, you'll have to update every absolute URL. Using SQL, this is extremely simple to do. Just edit the three queries below and update the old and new domain names, then run it. Done!

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');

Source: Smashing Magazine

sql databases, web developement

Published at DZone with permission of Jean-Baptiste Jung , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}