Useful WordPress Plugins

There’s a ton of WordPress plugins out there to choose from. So, how do you know which you ones you should use & which to stay away from? Here’s a list of useful WordPress plugins every site should have.

This list of useful WordPress plugins was last updated on November 7, 2017. Suggest your favorite plugin in the comments below for consideration to be added.

It’s a cinch to install useful WordPress plugins that add extra functionality with just a click. Though, this can be dangerous for many sites since many don’t review the code, performance, and get a little click-happy with the install button. Don’t get click-happy! Ask yourself: Continue reading “Useful WordPress Plugins”

WordPress AJAX: Frontend & Backend Implementation

I was recently working on a WP site that required AJAX functionality on the frontend. Having implemented WordPress AJAX for my Book It! Transportation plugin on the backend, I figured it would be a cinch. Come to find out, it required a little more work to accomplish.

I was recently working on a WP site that required AJAX functionality on the frontend. Having implemented WordPress AJAX for my Book It! Transportation plugin on the backend, I figured it would be a cinch. Come to find out, it required a little more work to accomplish.

After digging around multiple blogs and the WordPress Codex, I found it a little difficult to find full working examples. Get ahead of the learning curve by learning how to implement WordPress AJAX for both the frontend and backend below.


WordPress AJAX for the Backend

This is a cinch to do. Currently, the core of WordPress uses AJAX only in the administration screens. For instance, AJAX is used for instant updates when you are doing comment moderation, and when you are adding and deleting items from lists such as categories, blogroll, and posts; AJAX is also the technology behind the auto-save functionality on post and page editing screens. Several themes and plugins also use AJAX; for instance, some post rating plugins use AJAX to store the visitor’s rating in the database and then display an updated average rating.

Since AJAX is already built into the core WordPress administration screens, adding more administration-side AJAX functionality to your plugin or theme is fairly straightforward, check out the example below.

This should be fairly straight forwared. It uses WP’s admin_footer action (see add_action) to include some JS in the footer that makes the AJAX magic happen. Take a look at $.post if you’re not familiar with it.

With the code above, you’re defining your action, a WordPress nonce for security, and any other data you want to send. You’ll use the action you define as part of the WP function you’ll create that handles the AJAX request. See below:

We’re using WP’s wp_ajax_(action) hook to handle the AJAX request. This hook allows you to create custom handlers for your own custom AJAX requests. The wp_ajax_ hook follows the format “wp_ajax_$youraction”, where $youraction is your AJAX request’s ‘action’ property you sent in the JS above.

Also, be sure to include the check_ajax_referer to verify that the request came from the right place.

That’s it! You will need to add a few details, such as error checking, but hopefully the example above will be enough to get you started on your own administration-side AJAX plugin.

WordPress AJAX Backend Example

Here’s a full working example of WordPress AJAX in the backend. This will produce a JS alert box with the text 1044 when a page has been loaded in the administration panel. In the functions.php file:

WordPress AJAX for the Frontend

This takes a little extra work to accomplish. Since WordPress 2.8, there is a hook similar to wp_ajax_(action):

You’ll need to use this hook if you’re planning on implementing WordPress AJAX on the frontend. If you want to fire your AJAX function both both logged-in and logged-out users, you’d do this:

Something else to keep in mind when implementing WordPres AJAX on the frontend is, unlike on the admin side, the ajaxurl javascript global does not get automatically defined for you, unless you have BuddyPress or another Ajax-reliant plugin installed. So instead of relying on a global javascript variable, you’ll need to declare a javascript namespace object with its own property, ajaxurl. Use wp_localize_script() to make the URL available to your script, and generate it using this expression: admin_url( 'admin-ajax.php' ). See below:

Another thing to keep in mind is, both front-end and back-end AJAX requests use admin-ajax.php so is_admin() will always return true in your action handling code. When selectively loading your AJAX script handlers for the frontend and backend, and using the is_admin() function, your wp_ajax_(action) and wp_ajax_nopriv_(action) hooks MUST be inside the is_admin() === true part.

AJAX requests bound to either wp_ajax_ or wp_ajax_nopriv_ actions are executed in the WP Admin context. Carefully review the actions you are performing in your code since unprivileged users or visitors will be able to trigger requests with elevated permissions that they may not be authorized for. See below:

Here the AJAX action my_frontend_action will trigger the PHP function my_frontend_action_callback() for all users. The AJAX action my_backend_action will trigger the PHP function my_backend_action_callback() for logged-in users only.

WordPress AJAX Frontend Example

Here’s a full working example of WordPress AJAX in the frontend. This will produce a JS alert box with the text 1044 when a page has been loaded in the administration panel. In the functions.php file:

In the example.js file:


Learn more about WordPress AJAX

WordPress Developer Tips & Tricks

If you haven’t already guessed, I’m a huge fan of WordPress. Having worked with both Drupal and WP, hands down I’d pick WordPress over Drupal any day. Though WordPress is a powerful CMS, there are some limitations that I hate to say, Drupal makes a little easier to overcome. But, if you’re a developer like myself, those limitations can be tackled with a little coding.

I’ve put together an ongoing post of useful WordPress tips and tricks to make life a little easier for other developers. Feel free to comment below and share your thoughts. tips or tricks you might have. This is my go-to guide to reference ways to make WordPress work exactly how I want it and hope it will be yours too!

Remove Unneeded Code from the WordPress Header

Make It Work! Overcoming WordPress Limitations

Feature Fun! WordPress Tips & Tricks

Useful WordPress SQL Queries


Customize HTML Markup

Customize Comments Markup

In a typical WordPress theme you output the entire list of comments for a Post/Page by using the function wp_list_comments(). This doesn’t offer much by the way of customizing what HTML markup gets generated for that comment list. To write your own markup for the comment list, you can use a callback function as a parameter in wp_list_comments(), so it’s just as nicely abstracted.

In functions.php

		function my_theme_comments( $comment, $args, $depth ) {
			$GLOBALS['comment'] = $comment;
		   	echo '<li ' . get_comment_class() . ' id="li-comment-' . get_comment_ID() . '">';
		   	if ( $comment->comment_approved == '0' ) {
		   		echo '<em>' . _e 'Your comment is awaiting moderation.' ) . '</em>';
		   	}

		   	// Comments markup code here, e.g. functions like comment_text();
		}
		

In comments.php

		wp_list_comments( 'callback=my_theme_comments' ); 
		

Remove Unneeded Code from the WordPress Header

Removing unnecessary code from your site is essential to optimizing it for both performance and SEO:

  • Your important page content gets moved further up on the page.
  • You increase your content to code ratio.
  • Your pages will load faster. Visitors may not notice, but search engine spiders will.

Here’s some code I routinely remove from the WordPress sites I work on:

<link rel="EditURI" type="application/rsd+xml" title="RSD" href="http://www.benmarshall.me/xmlrpc.php?rsd" />

Are you editing your WordPress blog using your browser? Yes? Then you’re probably not using a blog client and this link can probably be removed. This link is also used by a few 3rd party sites/programs that use the XML-RPC request formats. One example is the Flickr API. So if you start having trouble with a 3rd party service that update your blog, add this back in. Otherwise, remove it.

To remove the EditURI/RSD link from your header, open functions.php (in your theme folder) and add this line:

remove_action( 'wp_head', 'rsd_link' );
<link rel="wlwmanifest" type="application/wlwmanifest+xml" href="http://www.benmarshall.me/wp-includes/wlwmanifest.xml" />

If you don’t know what Windows Live Writer is (it’s another blog editing client), then remove this link.

To remove the wlwmanifest link from your header, open functions.php and add this line:

remove_action( 'wp_head', 'wlwmanifest_link' );
<link rel="shortlink" href="http://wp.me/36m0W" />

URL shortening is sometimes useful, but this automatic ugly url in your header is useless. There is no reason to keep this. None.

To remove the shortlink from your header, open functions.php and add this line:

remove_action( 'wp_head', 'wp_shortlink_wp_head' );

Remove the WordPress Generator (with version information) Tag

<meta name="generator" content="WordPress 3.4.2">

This announces that you are running WordPress and what version you are using. It serves no purpose. You should always be running the latest version of WordPress. If you are living life on the edge and are a few releases behind, why advertise how vulnerable you are?

To remove WordPress Generator from your header, open functions.php and add this line at the bottom of the page:

remove_action( 'wp_head', 'wp_generator' );

Make It Work! Overcoming WordPress Limitations

One of the most common things I hear from Drupal developers about why they don’t like WordPress, is it has limitations. This makes me laugh only because everything Drupal is complex and time-consuming. Yes, there are some limitations with WordPress, but their platform makes it a cinch to overcome these problems. Instead of taking hours to do something like it does in Drupal, you can knock out the same task in WordPress in less than 5 minutes.

Here’s some quick code snippets that will help you overcome the limitations WordPress has:

Change Add New Post to Add New Article

Add New Article

Have a client that prefers the Add New Post to say Add New Article? Use the code below in your functions.php file to keep them happy:

add_filter( 'gettext', 'change_post_to_article' );
add_filter( 'ngettext', 'change_post_to_article' );
function change_post_to_article( $translated ) {
	$translated = str_ireplace(  'Post',  'Article',  $translated );
  	return $translated;
}
Be careful with this one. The code you put in this hook will run every time WordPress runs a string through its translation filters. Complex cases and conditionals could add a considerable amount of overhead, especially when loading pages filled with translation strings, such as the administrative pages. But if you just want to rename one thing that confuses your client (for example, maybe changing “Posts” to “Articles” for that corporate client who doesn’t “blog” yet), then these hooks can be very handy.

Redirect Failed Logins

By default, WordPress redirects users back to the current page upon successful authentication, but what about failed logins? Here’s a hook and some code that you can put in your functions.php file that will redirect failed log-ins to any location of your choosing.

add_action( 'wp_login_failed', 'my_front_end_login_fail' );  // hook failed login

function my_front_end_login_fail( $username ) {

	// Where did the post submission come from?
  	$referrer = $_SERVER['HTTP_REFERER'];

  	// If there's a valid referrer, and it's not the default log-in screen.
  	if ( !empty($referrer) && ! strstr( $referrer,'wp-login' ) && ! strstr( $referrer,'wp-admin' ) ) {

  		// Let's append some information (login=failed) to the URL for the theme to use.
   		wp_redirect( $referrer . '?login=failed' );
    	exit;
  	}
}

Add Excerpts to Pages

Page Excerpts

By default, Pages do not support excerpts. Did you know that adding excerpt support to the built-in Page type is as simple as adding a few lines of code?

add_action( 'init', 'my_add_excerpts_to_pages' );
function my_add_excerpts_to_pages() {
  	add_post_type_support( 'page', 'excerpt' );
}

Add Your Own Credits to the Admin Footer

If you build WordPress websites for clients, then you should certainly make sure that WordPress gets its due. It wouldn’t hurt to sneak in a little credit to your agency either.

add_filter( 'admin_footer_text', 'my_admin_footer_text' );
function my_admin_footer_text( $default_text ) {
  	return '<span id="footer-thankyou">Website managed by <a href="http://www.highfivery.com">highfivery.com</a><span> | Powered by <a href="http://www.wordpress.org">WordPress</a>';
}

Feature Fun! WordPress Tips & Tricks

WordPress is great as a start to a fully-featured website, but sometimes you need some extra features to really make it do what you need. Here’s some quick ways to easily add additional features to your WordPress site.

Add Image Source Fields

Crediting and linking the source of any republished photo or illustration on the web is one of the most important best practices of web publishing. Unfortunately, there isn’t a standard way of doing it in WordPress and authors are left with their own decision on how and where to credit the original author or website.

To add a source name and URL to your images, add the following code to your functions.php file:

add_filter( 'attachment_fields_to_edit', 'add_image_source_url', 10, 2 );
function add_image_source_url( $form_fields, $post ) {
  $form_fields['source_name'] = array(
    'label' => __('Source Name'),
    'input' => 'text',
    'value' => get_post_meta($post->ID, 'source_name', true),
                'helps' => __('Add the name of the source where the original image was posted.'),
  );

  $form_fields['source_url'] = array(
    'label' => __('Source URL'),
    'input' => 'text',
    'value' => get_post_meta($post->ID, 'source_url', true),
    'helps' => __('Add the URL where the original image was posted'),
  );
  return $form_fields;
}

add_filter('attachment_fields_to_save', 'save_image_source_url', 10 , 2);
function save_image_source_url($post, $attachment) {
  if (isset($attachment['source_url']))
    update_post_meta($post['ID'], 'source_url', esc_url($attachment['source_url']));
  
  if (isset($attachment['source_name']))
    update_post_meta($post['ID'], 'source_name', $attachment['source_name']);

  return $post;
}

Thanks to the following people:

ShareThis Slowing Down Your Site? Here’s Why.

I’ve never been a fan of ShareThis. It’s caused nothing but headaches and slow load times on sites I’ve installed it on. I had always assumed it was because they had slow servers, but until recently, never really dug into it. If you’re having problems with ShareThis slowing down you site, read below. I was pretty surprised to find out what was really going on.

I cringe every time I hear someone mention ShareThis. When optimizing a site, it becomes a nightmare to speed up; a paranoids worst fear with all the third-party calls it makes — and a blackhole for sercuity experts tracking down the seemingly ever-changing external scripts it loads.


ShareThis Third-Party Resources

In the process of implementing a CSP, I was shocked to find the total number of resources ShareThis calls. At the time of writing this, I counted a total of 39 domains and subdomains it the ShareThis plugin was calling:

  1. http://w.sharethis.com (JavaScript, Image)
  2. http://wd-edge.sharethis.com (JavaScript)
  3. http://wd.sharethis.com (JavaScript)
  4. http://w.sharethis.com (CSS)
  5. http://l.sharethis.com (Image)
  6. http://adadvisor.net (Image)
  7. http://bcp.crwdcntrl.net (Image)
  8. http://rc.rlcdn.com (JavaScript)
  9. http://d.agkn.com (JavaScript)
  10. http://secure-us.imrworldwide.com (JavaScript)
  11. http://log.dmtry.com (JavaScript)
  12. http://match.adsrvr.org (JavaScript)
  13. http://www.wtp101.com (JavaScript)
  14. http://www.adadvisor.net (Image)
  15. http://p.brilig.com (Image)
  16. http://e.nexac.com (Image)
  17. http://su.addthis.com (Image)
  18. http://rtd.tubemogul.com (Image)
  19. http://d.p-td.com (Image)
  20. http://ds.reson8.com (Image)
  21. http://loadus.exelator.com (Image)
  22. http://dp2.33across.com (Image)
  23. http://p.nexac.com (Image)
  24. http://p.raasnet.com (Image)
  25. http://p.rfihub.com (Image)
  26. http://i.w55c.net (Image)
  27. http://loadm.exelator.com (Image)
  28. http://a.triggit.com (Image)
  29. http://sync.mathtag.com (Image)
  30. http://r.casalemedia.com (Image)
  31. http://dpm.demdex.net (Image)
  32. http://idsync.rlcdn.com (Image)
  33. http://load.s3.amazonaws.com (Image)
  34. http://d.xp1.ru4.com (Image)
  35. http://segments.adap.tv (Image)
  36. http://dm.de.mookie1.com (Image)
  37. http://ib.adnxs.com (Image)
  38. http://tags.bluekai.com (Image)
  39. http://sync.tidaltv.com (Image)

Nuts, huh?! Not a big surprise anymore why ShareThis was causing my slow page load times. From what I found, the majority of the calls are for ad and user habit tracking pixels. No wonder their a free service, their probably getting paid big bucks from companies to track what you’re doing. Though, I understand they need to connect to the various social network APIs to grab numbers, I don’t think they need to add tracking pixels for advertisements and user habits.

An example is adadvisor.net and crwdcntrl.net. Their domains used by Targus Info and Lotame which is an advertising company that is part of a network of sites, cookies, and other technologies used to track you, what you do and what you click on, as you go from site to site, surfing the Web. Over time, sites like adadvisor.net and crwdcntrl.net can help make an online profile of you usually including the sites you visit, your searches, purchases, and other behavior. Your profile can then be exchanged and sold between various companies as well as being sold to other advertisers and marketers.


Alternatives to ShareThis

After finding out everything ShareThis is doing, it was time to find some alternatives. Here’s some I’ve found that seem to be less resource intensive—though may still load ad and user habit tracking pixels, not nearly as many as ShareThis:

WordPress Plugins

If you prefer a more plug-and-play options, here’s some pretty good WordPress plugins and Drupal modules for social sharing.

1. Flare

Flare is a simple yet eye-catching social sharing bar that gets you followed and lets your content get shared via posts, pages, and media types.

Flare

2. Social Count Plus

Display the counting data of Twitter, Facebook, YouTube, Instagram, Steam Community, SoundCloud posts and comments.

Social Count Plus

3. Dig Dig

Your all in one share buttons plugin. Add a floating bar with share buttons to your blog. Just like Mashable!

Dig Dig

4. AddThis

AddThis Share Buttons help drive traffic to your site by helping visitors share, bookmark and email your content to over 330 services.

AddThis

5. Shareaholic

Adds an attractive social bookmarking menu and related content widget to your posts, pages, index, or any combination of the three.

Shareaholic

Drupal Modules

1. Social media

The social media module helps integrate your website with social media sites such as Twitter, Facebook and Google+. It provides an centralized way of managing social media profile information and plug-in widgets such as follow and share buttons.

Social Media

2. Easy Social

Centered in a single place, you don’t need to worry in including external javascript libraries, and enabling several social modules.

Easy Social

3. AddToAny

This module helpers your readers and subscribers share, email and bookmark your articles and pages using the popular services such as Facebook, Twitter, Pinterest and over 100 more.

AddToAny

4. AddThis

This is the #1 bookmarking and sharing button on the net. Simple and easy to use button that’s used by 14 million websites worldwide and reaching 1.3 billion people worldwide per month.

AddThis

5. Service links

Service Links facilitates the adding of social networks links or javascript buttons within the content with a special focus on developers and themers interested to extend their number or customize their displaying. Built on Drupal for Drupal doesn’t include commercial advertising or spying hidden code and doesn’t require third part libraries.

Service links

How to Fix WordPress CSS & JS From Breaking

I recently came across a strange WordPress issue that was driving me up a wall. After completing a fresh install in a MAMP environment, I was having intermittent issues where it appeared the CSS and JS wasn’t getting loaded in the admin dashboard.

Ever run into CSS or JS failing to load when working on a WordPress project locally? Me too!!! I even tried a fresh install using MAMP, but still got intermittent issues. It was driving me nuts until I did a little googling and found what the problem was.

WordPress loadscripts Screenshot Error

If you’re seeing something like what’s shown above, you’re probably having the same issue I was with the load-scripts.php and load-styles.php files. I was surprised to see on a fresh install of WordPress, console was returning a bunch of CSS and JS errors. I took a look at one of the problem files, load-scripts.php from the console and found:

WordPress load-scripts.php Console Error

Yikes! Initially I thought my install got corrupted somehow, so tried to re-installing WordPress again with no luck. After digging around, I came across this post by Michael Novotny.

Basically, he found that hacking a WordPress core file — which is a big no-no, could fix the problem. In wp-admin/load-styles.php he replaced this line:

error_reporting(0);

with:

error_reporting( E_ALL | E_STRICT );

and Viola! All was working again. Although like I said, editing a core WordPress file is never a good idea.

The Solution

So what’s a good fix for this bug? One of Michael’s readers, Dan, posted a comment in response to his post:

I also had this issue with MAMP pro. I found that adding:

define( 'CONCATENATE_SCRIPTS', false );

to wp-config.php worked as well. Perhaps you might feel a little better modifying config file over a core file.

This is a much better fix instead of hacking at WordPress’s core files. So in wp-config.php at the bottom of your file, place the following code:

define( 'CONCATENATE_SCRIPTS', false );

Check out this article by Jean Galea for further reading on this pesky bug.

Most Useful WordPress SQL Queries

WordPress is one on the most popular CMS around and for good reason. The ease at which it allows anybody to build and maintain websites can’t compare to other systems like Drupal or Joomla. Sometimes though, you’ll need to transfer a WordPress site to another URL or clean up your database to boost server performance. This can sometimes be tricky if you attempt to handle everything through the admin panel. To make life a little bit easier, I’ve put together a list of the most useful WordPress SQL queries.

WordPress is one on the most popular CMS around and for good reason. The ease at which it allows anybody to build and maintain websites can’t compare to other systems like Drupal or Joomla. Sometimes though, you’ll need to transfer a WordPress site to another URL or clean up your database to boost server performance. This can sometimes be tricky if you attempt to handle everything through the admin panel. To make life a little bit easier, I’ve put together a list of the most useful WordPress SQL queries.

Before you proceed…

It’s always good practice to backup your database before making any major changes. Should something go wrong, you’ll always be able to restore the old site. If you need help, try one of these useful WordPress plugins:

  • WP-DB-Backup
    WP-DB-Backup allows you easily to backup your core WordPress database tables. You may also backup other tables in the same database.

  • WP-DBManager
    Allows you to optimize database, repair database, backup database, restore database, delete backup database , drop/empty tables and run selected queries. Supports automatic scheduling of backing up, optimizing and repairing of database.

Alternatively, you can backup your database through phpMyAdmin manually.

The Most Useful WordPress SQL Queries

Running simple SQL queries like the ones listed below is easy. One of the easiest ways to accomplish it is through phpMyAdmin. If you don’t have phpMyAdmin installed or unsure how to access it, like with most things WordPress, there’s a plugin that can help you out. The WordPress SQL Executioner is a plugin that allows you to execute SQL queries through the admin panel.


Update the site URL & home URL

When you install WordPress for the first time, it stores the absolute path of the site URL and home URL in the database. This can make it a little tricky when you transfer your WordPress site from one URL to another. When you try, your site won’t load because the absolute URL is still set to the old URL. Fixing this is easy by updating the site URL and home URL in the database:

Update the GUID

One of the most crucial updates to make after a site transfer is the GUIDs. This is important because the GUID is used to translate post and page slugs to the correct absolute path. Updating this is just as easy as updating the site URL and home URL:

Update the URLs in your content

Like the site URL and home URL, WordPress uses absolute paths when embedding media and links into your posts or page content. When you transfer a WordPress site to a new URL, you’ll need to make sure to update those URLs so they don’t get broke. This can be a tedious and time-consuming process if you have a lot of content to update. Running the SQL query below will make it fast and easy:

Update image paths only

If you use a CDN or cloud service like Amazon CloudFront to serve images, you’ll need to update the image paths in your existing posts and pages so they get loaded by the service instead of your server. Use the query below to only update image paths:

Update your posts meta information

WordPress stores special post information in a separate table. If you’ve transferred your WordPress site, you’ll need to update that information should any of it point to your old URL. Use the query below to easily do this:

Change the default “admin” username

When you first install WordPress, it’s default account username is set to “admin”. Since this is widespread knowledge, it causes security concerns and should be changed. Changing it to a more unique name can boost your site’s security. Use the query below to change the default “admin” username:

Change your WordPress password

Need to change your WordPress password and can’t find where to do it in the admin panel? Use the query below to quickly make the change:

Assign posts from one author to another

Say you want to transfer articles from one author to another. This is another time consuming and tedious process if you’ve got a lot of posts on your site. You can easily and quickly make this change, but first need to obtain the author’s ID by going to your user page in the WordPress admin panel. Click the user’s name to view their profile and then look at the address bar taking note of the “user_id”. Then, use the query below to easily transfer ownership from articles assigned to one author to another:

Delete revisions

When you edit WordPress posts of pages, by default, it saves each revision so you can easily revert should something go wrong. This can cause use up your server resources when you have thousands of entries. Your database will grow significantly and will cause an increase in loop iterations, data retrieval and will affect page load times. To help optimize your WordPress database, delete old revisions that you don’t need anymore by using the query below:

Note: Bear in mind that all revisions from each post will be deleted, including all of its meta data.

Delete post meta

Installing or removing plugins is a very common task for WordPress. Some of the plugins make use of the post meta to store data pertaining to the plugin. After you have removed the plugin, those data are still left inside the post_meta table, which will no longer be needed. Run the following query to clean up the unused post meta value. This will help to speed up and reduce the size of your database.

Export all comment emails with no duplicates

Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.

Delete all pingbacks

Popular articles receive plenty of pingback. When this happens, the size of your database increases. In order to reduce size of the database, you can try removing all the pingbacks.

Delete all spam comments

If you have plenty of spam comments, going through each page to delete spam can be tedious and frustrating. With the following SQL query, even if you have to face deleting 500 over spam comments, it will be a breeze.

  • 0 = Comment Awaiting Moderation
  • 1 = Approved Comment
  • spam = Comment marked as Spam

Delete unused tags

In a WordPress database, if you run a query to delete old posts manually from MySQL, the old tags will remain and appear in your tag cloud/listing. This query allows you to delete the unused tags.

Find how many entries are spam

We look at the column comment_approved. Here WordPress stores the type of the comment. We collect the amount of the different values with COUNT(*) and put it into the variable amount.

comment_approved amount
spam 12118
1 104
post-trashed 1

The 1 represents approved, real comments. We are already seeing: only 0.85% of all comments are spam. Unfortunately, this is a normal value. The mean part is: MySQL must always go through the entire table to find the real comments for the output in the published articles. With an index, an optimized query structure, this goes faster.

Find which posts suffer from the most spam

The more popular a post is, the more spam it attracts. Some posts get almost never spam, others are downright overwhelmed. We get the 20 most spammed posts with this query:

comment_post_ID amount
1776 2299
2184 1840
143 1759
1366 1145
2377 960
2456 512
2536 497
683 486
1373 236
1402 200
1522 174
2458 161
1168 127
10473 121
647 116
2357 114
2831 111
2420 110
741 94

Now we could protect for these articles the comments with a registration or just close the comment form. Both solutions are not ideal.

Find where the most spam comes from

comment_author_IP amount
173.242.120.58 450
198.200.37.51 386
198.200.37.59 287
192.74.240.170 268
109.163.236.87 218
192.74.230.131 181
212.59.28.172 157
198.200.37.83 153
91.236.74.117 148
212.59.28.221 147
142.0.138.77 140
142.0.136.9 117
192.74.228.242 109
142.4.117.43 91
137.175.1.235 88
142.4.97.173 82
142.4.117.162 80
198.200.33.140 80
192.74.228.161 73
193.105.210.217 63
142.4.96.66 63
91.231.40.28 55
193.105.210.216 54
142.4.96.74 54
192.74.228.164 51
192.162.19.193 48
80.93.213.249 48
192.74.231.156 46
142.0.136.12 46
91.231.40.27 46

There is so much spam from some IP addresses that we can lock them out safely. This is works in a .htaccess, for example, like this:

# IP block list
order allow,deny
deny from 173.242.120.58
allow from all


Do you have any useful WordPress SQL queries you use?

I’d love to hear from you! Comment below with some useful WordPress SQL queries you use to update, optimize and manage your site.