Using SQL to manage WordPress: The definitive guide

Using SQL to manage WordPress: The definitive guide

Using SQL to manage WordPress: The definitive guide

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.


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: 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: 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.

DELETE FROM wp_posts WHERE post_date < '2010-01-01 00:00:00' AND post_status = 'publish'


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_comments table. 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

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

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 extremelly 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, '', '') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, '','');
UPDATE wp_posts SET post_content = replace(post_content, '', '');

Source: Smashing Magazine

WordPress: Super useful WP-config tips

WordPress: Super useful WP-config tips

WordPress: Super useful WP-config tips

The wp-config.php file is the heart of your WordPress website, where you can define a wide variety of options to control how your WordPress install works. Today, I’m sharing with you a list of super easy tips to take more control of your site using wp-config.php.

Keeping your database and site size small

On large websites, keeping your database small in size can be a challenge. WordPress tends to store a lot of data in your db, like transients or post revisions.

You can easily limit post revisions to a number of your choice (3 in this example) by adding the following in your wp-config.php file:

define('WP_POST_REVISIONS', 3);

If you don’t feel like using the post revision feature at all, you can simply disable it:

define( 'WP_POST_REVISIONS', false );

Also, WordPress stores in the database posts, pages, attachments and comments which have been moved to trash. You can control the number of days it will stay in the trash before being completely deleted. The default is set to 30, but I’ve set it to 1 in this example.

define( 'EMPTY_TRASH_DAYS', 1 );

By default, WordPress creates a new set of images every time you edit an image and when you restore the original, it leaves all the edits on the server. Defining IMAGE_EDIT_OVERWRITE as true changes this behavior.

define( 'IMAGE_EDIT_OVERWRITE', true );


If your web hosting plan supports SSL, you should definitely use that feature to add an extra layer of security to your site. Via wp-config.php, WordPress makes it easy to force SSL logins:

define('FORCE_SSL_LOGIN', true);

And same goes with the admin area of your site:

define('FORCE_SSL_ADMIN', true);

Secret keys are making your site harder to hack by adding random elements to the password. There are currently 4 secret keys and 4 salts that can be defined. To generate unique and secure secret keys, just use this handy generator. Don’t use those below!

define( 'AUTH_KEY',         't`DK%X:oxy|e-Z(BXb/f(Ur`8#~UzUQG-^_Cs_GHs5U-&Wb?pgn^p8(2@}IcnCa|' );
define( 'SECURE_AUTH_KEY',  'D&ovlU#|CvJ##uNq}bel+^MFtT&.b9{UvR]g%ixsXhGlRJ7q!h}XWdEC[BOKXssj' );
define( 'LOGGED_IN_KEY',    'MGKi8Br(&{H*~&0s;{k0tS(O:+f#WM+q|npJ-+P;RDKT:~jrmgj#/-,[hOBk!ry^' );
define( 'NONCE_KEY',        'FIsAsXJKL5ZlQo)iD-pt??eUbdc{_Cn)4!d~yqz))&B D?AwK%)+)F2aNwI|siOe' );
define( 'AUTH_SALT',        '7T-!^i!0,w)L#JK@pc2{8XE[DenYI^BVf{L:jvF,hf}zBf883td6D;Vcy8,S)-&G' );
define( 'SECURE_AUTH_SALT', 'I6`V|mDZq21-J|ihb u^q0F }F_NUcy`l,=obGtq*p#Ybe4a31R,r=|n#=]@]c #' );
define( 'LOGGED_IN_SALT',   'wu$4c$Hmd%/*]`Oom_(hdXW|0M=X={we6;Mpvtg+V.ol$|#_}qG(GaVDEsn,~*4i' );
define( 'NONCE_SALT',       'a|#h{c5|P &xWs4IZ20c2&%4!c(/uG}W:mAvyjI44`jAbup]t=]V7`}.py(wTP%%' );

For client sites

When using WordPress to build a site for a client, a developer is almost all the time concerned that the client won’t do something stupid to the site, requiring a lot of maintenance.

One common thing is a client trying to edit one of the site php files and ending up deleting something important, causing the website to be unavailable. You can actually prevent this to happen by disabling the built-in files editor in wp-config.php:

define('DISALLOW_FILE_EDIT', true);

Another “classic” client mistake is to never update the WordPress core, which leads to potential security breaches. You can force WordPress to update itself automatically by using the WP_AUTO_UPDATE_COREconstant in your wp-config.php file.

define('WP_AUTO_UPDATE_CORE', true);


If your WordPress install prompts you to fill in your FTP credentials each time you need to update a plugin, you can actually save a lot of time by using wp-config.php to memorize it. The three constants below will tell WordPress what are your FTP host, username and password. That way, you won’t have to submit the info each time.

define('FTP_HOST', '');
define('FTP_USER', 'Your_FTP_Username');
define('FTP_PASS', 'Your_FTP_password');

Most quality hosting companies provide SSL to their clients. If your host does, make sure you turn SSL FTP connections on for some extra security.

define('FTP_SSL', true);


When your website is unavailable or behaves strangely, you can make your maintenance work way easier by using wp-config.php and the debugging constants.

Enabling WP_DEBUG will cause all PHP errors, notices and warnings to be displayed.

define('WP_DEBUG', true);

As errors will be displayed on the site and accessible to visitors, a way more elegant way to debug is to use a log. Doing so in WordPress is easy: Once you have set WP_DEBUG to true, you can use WP_DEBUG_LOG, a constant that will make WordPress send all PHP errors and warning into a log located in your wp-content directory.

define( 'WP_DEBUG_LOG', true );

If your database is broken, you can actually repair it easily by accessing the script located at /wp-admin/maint/repair.php after setting WP_ALLOW_REPAIR to true:

define( 'WP_ALLOW_REPAIR', true );

Please note that those constants are intended to be used only when debugging a site. Once you found and fixed the problem, remember to set the values to false!


The wp-config.php file allows some tweaking to ensure a better performance by WordPress. One thing to start with is to increase the maximum memory allocated. Please note that this won’t work if your hosting provider limits the memory, which is often the case on shared hosting.
If you’re looking for a quality web host, I recommend VidahostWP Engine or InMotion Hosting.

define('WP_MEMORY_LIMIT', '96M');

You can also allow even more memory to administrative tasks (which requires more memory than just browsing the blog):

define( 'WP_MAX_MEMORY_LIMIT', '256M' );

WordPress has a built-in caching system, located in wp-content/advanced-cache.php. It can be activated using the WP_CACHE constant:

define( 'WP_CACHE', true );

Source By

Blogging 101

Blogging 101

Blogging 101

Blogging 101 is mostly about the blogging
vocabulary. To understand blogs, you need to know
the terms blog, platform, domain, and web host.
Once you have mastered these key elements of
blogging, you can enter any conversation about
blogging with confidence. After you know what
exactly a blog is, you will be on your way to
passing the final exam of blogging 101.

Blog is short for weblog, which simply means a
series of online posts presented in reverse
chronological order. That’s all! Most blogs are text,
but there are also photo blogs and video blogs. The
rest of blogging 101 has to do with the technical
side of things. If you are setting up a blog, you will
need a platform, a web host, and a domain. A
blogging platform is a computer software program
that allows you to write posts and to update your
blog. Your platform is also what you use to design
the look of your blog, from color scheme to font
size. The web host is sort of like the virtual file
cabinet where your blog is stored. Your computer
communicates with the host when you upload or
edit a post. The domain is the online address of
your blog, and usually ends in ‘dot com’. Now that
you know what a blog is, what a platform is, and
what domains and hosts are, congratulate yourself!
You have passed blogging 101.

How to Convert a date / timestamp to time ago for posts

How to Convert a date / timestamp to time ago for posts

How to Convert a date / timestamp to time ago for posts

Displaying an exact date on your posts is great but why not change things up just a little and display “1 Hour Ago, 2 Hours Ago, 1 Day Ago, 2 Days Ago, 1 Week Ago, etc. Just add this snippet to your single.php, index.php or any other template within the loop and you are good to go.


echo human_time_diff( get_the_time('U'), current_time('timestamp') ) . ' ago';

( WordPress codex functions, hooks, in this snippet. )

get_the_time, the_time, current_time, human_time_diff,

How to Track post views without a plugin using post meta

How to Track post views without a plugin using post meta

How to Track post views without a plugin using post meta

Use the following snippet to track post views on your wordpress blog. The first thing you want to do is add this snippet to the functions.php of your wordpress theme. Follow step 1. and step 2. to track and display the number of views for each post. Updated this snippet from my original post on March 3rd, 2011 to Included a option for Fragment Caching so this snippet will work even on cached pages.



function getPostViews($postID){
    $count_key = 'post_views_count';
    $count = get_post_meta($postID, $count_key, true);
        delete_post_meta($postID, $count_key);
        add_post_meta($postID, $count_key, '0');
        return "0 View";
    return $count.' Views';
function setPostViews($postID) {
    $count_key = 'post_views_count';
    $count = get_post_meta($postID, $count_key, true);
        $count = 0;
        delete_post_meta($postID, $count_key);
        add_post_meta($postID, $count_key, '0');
        update_post_meta($postID, $count_key, $count);
// Remove issues with prefetching adding extra views
remove_action( 'wp_head', 'adjacent_posts_rel_link_wp_head', 10, 0);

Step 1.

This part of the tracking views snippet will set the post views. Just place this snippet below within the single.php inside the wordpress loop.




Fragment Caching

Note: If you are using a caching plugin like W3 Total Cache, the method above to set views will not work as the setPostViews()function would never run. However W3 Total Cache has a feature called fragment caching. Instead of the above use the following so the setPostViews() will run just fine. Tracking all your post views even when you have caching enabled.



<!-- mfunc setPostViews(get_the_ID()); --><!-- /mfunc -->

Step 2.

The snippet below is optional, so use this if you would like to display the number of views within your posts. Place this snippet within the loop.



          echo getPostViews(get_the_ID());

( WordPress codex functions, hooks, in this snippet. )

delete_post, wp_head, get_post, get_the_ID, the_ID, add_post_meta, delete_post_meta, get_post_meta, update_post_meta, remove_action, wp,