The Obligatory WordPress “Gutenberg” Editor Hot Take

Of course you knew I'd have to make a Steve Guttenberg joke to start this off. But maybe there's something to it. (Side note: I just realized his last name has two "t"s, unlike Johannes, the inventor of movable type.)

I've been a professional web developer since before the term existed (1996, to be specific). I've been using WordPress for my blog since 2006, for occasional work projects since 2008, and as my primary web development platform since 2014. Working solo and being an introvert disinclined to participate in conferences, my contribution to the WordPress community has come mainly in the form of submitting a handful of plugins to the Plugin Directory. And, of course, writing a ton of blog posts here on various obscure problems I've encountered along the way.

I say all of this solely to establish whatever credibility I may or may not need in offering my half-baked assessment of what is, at the time of this writing, a half-baked WordPress plugin… but one that is destined — in short order, and for reasons that are up for much debate — to become the fundamental user experience of writing in WordPress.

If you're unfamiliar with Gutenberg, here's the plugin project in GitHub, and here's a recent blog post by Automattic founder and WordPress development lead Matt Mullenweg defending the rationale for the project, and here are a bunch of other reviews of the project, both pro and con, that I've read over the past three days.

So far documentation is (understandably) scarce, but I've poked around a bit to learn what I can and see how I might be able to customize it to meet my needs as a web developer building custom themes for clients.

I have a few things about Gutenberg that I'd like to explore with this post:

  1. My initial reaction to the Gutenberg interface itself
  2. Thoughts on how this will affect the work I do
  3. Opinions and speculation on the motivation behind the project

Getting to know Gutenberg

Some of the reviews I linked to above are critical of Gutenberg, in ways I don't think are entirely fair. Mainly because, yes, this is beta software. In fact, at this point I'd say even calling it beta is generous. This feels more like alpha testing, given how much of the development is still incomplete, and how much things are changing. I just downloaded version 0.9 yesterday and version 1.0 is already out, with a number of significant changes, including some that broke work I was doing on custom CSS styling for Gutenberg output just this morning.

The point is, there are plenty of things to criticize about how Gutenberg works at this point. But it's important to consider whether or not those are things that are intended to be the way they are, or if they're just incomplete features or unaddressed bugs. Here are a few of my favorite examples:

First, a screenshot from the special "Demo" page included with the plugin to help users familiarize themselves with how Gutenberg works.

Um… OK. Which "really wide" button would that be now?

One of the demo blocks is an image, and its caption suggests you try out a feature that — as far as I can tell — doesn't yet exist. This should be your first sign that criticism of the tool may be, at this point, a bit premature.

Gutenberg is full of fun surprises, like things randomly breaking with no real explanation. Welcome to beta software!

Oh this poor, suffering block. Also “previewed” is an understatement. When this error appears, it also means you can’t edit the block in question. Hit Refresh and hope for the best!

But I think the real coup de grâce is the lack of polish on some fringe elements, such as how the text block you're editing might randomly bounce around the page if there's a floated image preceding it, as the editing tools surrounding the block you're typing in appear and disappear. Or this… the beloved triple scrollbar!

Which one does what? Just scroll and see!

I've also discovered a fun bug that randomly inserts question marks when you switch italics on/off with keyboard shortcuts. But you don't see them in Gutenberg, only in Preview mode. And then if you try to delete them in Gutenberg, it just starts randomly eating your text. That's forced me to go into raw Text mode a few times in this very post to clean up Gutenberg's mess. (Yes, my secret is out… I'm using Gutenberg to write this post!)

Many other reviewers have noted some of the drawbacks of Gutenberg's features. It's still not true WYSIWYG. It seems to want to be direct in-page editing, so why isn’t it that? Many elements are still dependent upon your theme for proper styling (like the margins below captions on this post — but I will probably have fixed that in my CSS before you are reading this). A lot of the blocks don't really offer many styling options, which seems a bit self-defeating.

And, of course, it seems at this point that it will completely break the old "metabox" concept of the editing screen, and thousands of plugins and themes as a result. I really don't know how the core team intends to resolve that issue, although sidebar metaboxes seem to be handled under the Document tab in the Settings sidebar. A lot of popular plugins (Yoast SEO, anyone?) that rely on more horizontal width will need to be drastically rethought to work in that context, though.

This leads into my next topic: how will Gutenberg affect the work I do as a web developer?

So… how will Gutenberg affect the work I do as a web developer?

My initial gut reaction to the news of Gutenberg was that it seemed to be eliminating the reason I exist in the WordPress ecosystem. As a developer specializing mostly in custom theme development for clients, it looked like Gutenberg was going to destroy my business. And as an occasional contributor of free plugins to the WordPress community, it looked like Gutenberg was going to create a lot of unnecessary (and unpaid) work for me, rebuilding my plugins to function in this new paradigm.

The latter may well be true, but the former won't. Gutenberg does nothing at all to eliminate the need for theme developers — it just changes how we do a few things. And, importantly, the ability to create custom block types opens up new opportunities for developers of both themes and plugins to invent new ways of both displaying and working with content in a WordPress site.

The two big questions I'm left with, which are not yet answered, are:

  1. Is this really an improvement over the existing TinyMCE editor, for developers and clients who are using WordPress not as a blog platform but as a general-purpose CMS?
  2. Is it worth adapting to this new way of doing things? Or is this effectively Automattic showing me the door?

Pondering these questions takes me to my final topic…

Why this? Why now? Why at all?

I suppose there's a "Who moved my cheese?" element to this. After all, I've spent the past two years honing my concept of modular design and the first eight months of 2017 perfecting a reusable core theme that relies heavily on Advanced Custom Fields (specifically, Flexible Content blocks) and the WordPress Customize API to achieve many of the same things Gutenberg does. My theme offers considerably more flexibility and formatting options than Gutenberg does, though I will readily acknowledge that Gutenberg seems easier to use than my heavily customized ACF Flexible Content blocks are.

The point is, I am already deeply immersed and heavily invested in a particular set of tools that are intended to achieve many of the same aims as Gutenberg, and that, in some ways, do a better job of that.

But wait, what really are the aims of Gutenberg? Who asked for this? Who is it benefitting?

While Matt Mullenweg talks big about how it will benefit practically everyone in the WordPress ecosystem — developers and agencies, plugin developers, theme developers, core developers, web hosts, and (last, but… not least?) users — it seems clear to me and to a lot of other critics of the project that the primary beneficiary is Automattic itself.

I find it curious that this project is named after the inventor of movable type, since one of the earliest WordPress competitors is called… Movable Type. This project is transparently an effort at fighting back against the likes of Wix, Weebly, Squarespace and Medium, each of which has in its own way been eating away at the potential market for WordPress.

But, you know, I cringe a bit at talking about "markets" when we're discussing open source software. And that's the crux of the problem. WordPress has, for many years, existed as a Jeckyll-and-Hyde duo of WordPress.org — the open source project underlying self-hosted WordPress sites and the huge developer and designer community of which I am a part — and WordPress.com — the commercial, hosted, software-as-a-service (SaaS) platform owned by Automattic.

That list of SaaS platforms I mentioned above — Wix, Weebly, Squarespace and Medium — represents competition to both sides of the WordPress dichotomy, but in significantly different ways.

To the open source WordPress.org community, they're — mostly — competition in that they represent the "low end". Small businesses and organizations that have limited budgets or a determined DIY ethic are inclined to use them, until they realize how quickly they are hamstrung by the limitations of the tools they offer. Then those businesses and organizations hire designers and developers like me to take their websites to the next level, and we use WordPress as a way to build exactly what they need, because its open architecture and self-hosting mean there's no limit to our ability to customize WordPress to do exactly what we need. They are also a more ominous existential threat to our businesses, because they’re constantly improving, and eventually they won’t be so limited in ways that work to our advantage. So to that end, we need WordPress to evolve. But Gutenberg seems in some ways to be WordPress skating to where the puck is, rather than leapfrogging the competition, to mix metaphors.

But to WordPress.com, those SaaS platforms are much more direct competition, because they are offering exactly the same thing that WordPress.com offers: a hosted platform with limited customization capabilities. I have steered a number of clients away from Wix or Squarespace over the years, but I have steered just as many away from WordPress.com, and for the exact same reasons.

Medium is another story. In that it's literally about stories. Automattic is definitely feeling the heat from Medium, but this is a world apart from the scenarios I described in the two preceding paragraphs. Medium is challenging WordPress.com specifically as a blogging platform.

It's true that WordPress started as blogging software. But over the years it has become so much more than that. I have built over 100 websites on WordPress since switching to the platform full-time in 2014, but every single one of them has used WordPress as a CMS, not as a blog. Hardly any of those sites even have blogs. A key feature of one of the plugins I created, in fact, is to hide the Posts and Comments items in the WP admin interface, since hardly any of my clients use them.

Anecdotal evidence is not data. I wouldn't suggest that my business use case for WordPress as a CMS necessarily means it should no longer be thought of primarily as blogging software. But I am hardly alone, and I'd be willing to bet that a large majority of my fellow developers who have made a substantial part of their career in client services, using WordPress as the underlying technology, would agree with me. Because… wait for it… blogs don't make money. OK, a few do. But there aren't enough profitable blogs to warrant an entire industry of paid designers and developers to build them.

This has been the most jarring aspect of the whole Gutenberg debate for me… the realization that Matt Mullenweg still thinks of WordPress primarily, if not exclusively, as blog software. And it seems that his singular passion is really what's driving Gutenberg, above all else.

I do think Gutenberg, when it has a few more layers of refinements and polish, will be a superior content editing experience to what the current implementation of TinyMCE offers. But it is a huge change, and I don't think it jibes with the way most WordPress.org sites use the platform. That's not to say they can't or won't adapt. But it also says nothing about why they should.

When WordPress Treats an Administrator Like a Contributor

The first sign that something was wrong was when I tried to create a new page on the client’s site. The blue Publish button I normally see was replaced with Submit for Review. What the…? That’s what WordPress users with the lowly Contributor role usually see. But I’m an Administrator — the most mighty role known to the world of (single-site) WordPress. (Yes, multi-site installations also confer the fearsome title of Super Admin upon a select few.)

Worse still, if I tried to click Submit for Review, it wouldn’t actually save!

Other problems abounded — I tried to create a new user with Administrator privileges, just to see if my own user account was corrupt. Couldn’t save that, either.

I had Debug Bar installed, and I noticed it was giving an error:

WARNING: wp-admin/includes/post.php:641 - Creating default object from empty value
get_default_post_to_edit

Well, that’s not good. Googling the error didn’t lead to anything immediately helpful, besides this comment that led me to explore the database structure in phpMyAdmin for any problems.

Yes, there were problems. Many of the tables, including wp_options, wp_posts, wp_postmeta and wp_users were missing their primary keys. A bit more digging into the WordPress core showed that, for complex reasons (i.e. I don’t totally get it), without primary keys on these tables, WordPress can’t determine the post type of a new post, and if it can’t determine the post type, it can’t determine the user’s capabilities with regard to that post type, which all comes back to…

WARNING: wp-admin/includes/post.php:641 - Creating default object from empty value
get_default_post_to_edit

Googling on the matter of WordPress tables missing their primary keys (or, perhaps more pertinently, their auto-increments), led me to a solution!!

Fixing WordPress indexes, foreign keys and auto_increment fields

Well, a partial solution. Because the database I was working with was not damaged in exactly the same way as the one the OP was working with, I couldn’t use the sample code directly. I had to go through the database and manually create a few primary keys, delete a bunch of auto-draft posts that all had an ID of 0, etc. Then I had to skip a few lines of the OP’s SQL code because they referred to tables that hadn’t lost their keys in my case, for whatever reason. But this is the… key… to solving the problem.

Now then, how did the database get this way? Well, the site lives on a fairly creaky old Fatcow (ugh, that name) shared hosting account, running an old version of MySQL and an almost unrecognizably ancient version of phpMyAdmin. We were undertaking major content changes on the site, so I copied it over to my own sleek, modern staging server running the latest and greatest of everything. The idea was that we’d get all of our changes in place just the way we wanted on the staging server, rather than mess up the live site for 2-3 weeks, and when we were done, we’d just copy everything back over.

Slick. Right? Sure, if both servers are running reasonably identical software versions. Which of course is never the case. Ever.

Apparently when I copied the site back to Fatcow, due to the older MySQL (or possibly phpMyAdmin) version, certain things like the primary keys and auto-increments — and, I’d be willing to bet, but I’m not sure it matters, the collation as well — got lost along the way.

Another obscure WordPress problem: setting document.domain for cross-site scripting iframes breaks Gravity Forms AJAX submissions

Whew… that title was almost as long as the variable name I’m about to throw out in a code example.

I spent well over an hour beating my head against the wall on this problem today before narrowing it down to a Gravity Forms issue. The scenario: I have a site that is loading iframes from a different subdomain. As is common in this situation, I wanted to be able to adjust the height of the iframe with JavaScript, to match the height of the page within the iframe and prevent internal scrollbars.

The solution to that problem is readily available on teh interwebz, with the addition of a bit of extra JavaScript to allow cross-site scripting: both the containing page and the contained page need to specify the same document.domain so browsers will let them talk to each other.

Not long after we put this in place, my client informed me that none of their AJAX-based Gravity Forms were working. The spinner would just spin indefinitely, even if (usually) the form actually did submit properly. It didn’t take me long to narrow the problem down to a JavaScript error pertaining to cross-site scripting. I found that AJAX and document.domain don’t mix. Or at least that seemed to be the issue.

But that’s where I hit a wall. No one else seemed to be describing the exact problem I was having. Most solutions involved adding a Access-Control-Allow-Origin header, but that didn’t do anything for me.

Eventually I realized that was because the problem wasn’t with the AJAX, per se. It was the fact that Gravity Forms adds its own hidden iframe where it works some secret mojo on AJAX submissions. And that iframe needed to have document.domain added to it, just like my site and the other subdomain I was loading in iframes did.

So the question then was, is there a Gravity Forms hook to modify its iframe output? Fortunately, the answer is yes.

The gform_ajax_iframe_content filter pretty much does what it says on the tin. Add a filter to insert the necessary JavaScript, and you’re good. The only thing I don’t get about this is the name given to its lone input parameter. I mean, really? (Actually… I do think I understand it, but I don’t understand it.)

Anyway… here’s what you need to make this work. Just replace example.com with the correct domain name. And if you’re running on a version of PHP before 5.3, you won’t be able to use an anonymous function. But you’re not running an old version of PHP, are you?

add_filter('gform_ajax_iframe_content', function($doctype_html_html_head_meta_charset_utf_8_head_body_class_gf_ajax_postback_form_string_body_html) {
  echo "<script>document.domain = 'example.com';</script>\n";
  return $doctype_html_html_head_meta_charset_utf_8_head_body_class_gf_ajax_postback_form_string_body_html;
});

WordPress challenge of the day: customizing The Events Calendar’s RSS feed and how it displays in a MailChimp RSS campaign

This one’s a doozy. I have a client who is using The Events Calendar, and they want to automate a weekly email blast listing that week’s events, using MailChimp.

The Events Calendar automatically generates an RSS feed of future events, inserting the event’s date and time in the RSS <pubDate> field. And MailChimp offers an RSS Campaign feature that can be scheduled to automatically send out emails with content pulled in from an RSS feed.

So far so good. But there were a few things the client wanted that were missing:

  1. Show exactly a week’s worth of events. The RSS feed just pulls in n events… whatever you have set in Settings > Reading > Syndication feeds show the most recent.
  2. Display the event’s featured image. Featured images aren’t included in WordPress RSS feeds, neither the default posts feed nor The Events Calendar’s modified feed.
  3. Show the event’s location. This is also not pulled into the RSS feed at all.

To make this happen, I had to first get the RSS feed to actually contain the right data. Then I had to modify the MailChimp campaign to display the information.

The problem in both cases surrounded documentation. RSS, though it’s still widely used, is definitely languishing if not dead. The spec is well-defined, but there’s not a lot of good information about how you can customize the WordPress RSS feed, and even less about how to customize The Event Calendar’s version. What info I could find was generally outdated or flat-out wrong — like the example in the official WordPress documentation (the old documentation, to be fair) that has at least three major errors in it. (I’m not even going to bother to explain them. Just trust that it’s wrong and you shouldn’t use it.)

Now that I’ve put in the hours of trial and error and futile Googling, I’ll save you the trouble and summarize my successful end result.

Problem 1: Show a week’s worth of events in the RSS feed

It took a surprising amount of effort to figure out how this is done, although in the end it’s a very small amount of code. Part of the problem was that I was not aware of the posts_per_rss query parameter, and therefore I wasted a lot of time trying to figure out why posts_per_page wasn’t working. Maybe that’s just my dumb mistake. I hope so.

I also spent a bunch of time trying to get a meta_query working before I realized that The Events Calendar adds an end_date query parameter which makes it super-easy to define a date-based endpoint for the query.

You need both of these. Depending on how full your calendar is, the default posts_per_rss value of 10 is possibly not enough to cover a full week. I decided to change it to 100. If this client ever has a week with more than 100 events in it, we’ll be in trouble… probably in more ways than one.

Here’s the modification you need. Put this in your functions.php file or wherever you feel is appropriate:

// Modify feed query
function my_rss_pre_get_posts($query) {
  if ($query->is_feed() && $query->tribe_is_event_query) {
    // Change number of posts retrieved on events feed
    $query->set('posts_per_rss', 100);
    // Add restriction to only show events within one week
    $query->set('end_date', date('Y-m-d H:i:s', mktime(23, 59, 59, date('n'), date('j') + 7, date('Y'))));
  }
  return $query;
}
add_action('pre_get_posts','my_rss_pre_get_posts',99);

What’s happening here? The if conditional is critical, since pre_get_posts runs on… oh… every database query. This makes sure it’s only running on a query to retrieve the RSS feed and, specifically, The Events Calendar’s events query.

We’re changing posts_per_rss to an arbitrarily large value — the maximum number of events we can possibly anticipate having within the date range we’re about to set.

The change to end_date (it’s actually empty by default) sets a maximum event end date to retrieve. My mktime function call is setting the date to 11:59:59 pm on the date one week from the current date. You can just change the 7 to another number to set the query to that many days in the future. There are a lot of other fun manipulations you can make to mktime. Check out the official PHP documentation if you’re unfamiliar with it.

Every add_action() call can include priority as the third input parameter. Sometimes it doesn’t matter and you can leave it blank, but in this case it does matter. I’m not sure what the minimum value is that would work, but I found 99 does, so I stuck with that.

Problems 2 and 3: Add the featured image and event location to the RSS feed

RSS is XML, so it has a syntax similar to HTML, but with its own specific tags. (And with XML’s much stricter validation requirements.) WordPress uses RSS 2.0. This can get you into trouble later with the MailChimp integration, because MailChimp’s RSS Merge Tags documentation gives an example of the RSS 1.5 <media:content> tag for inserting images, but you’ll actually need to use the <enclosure> tag… which MailChimp also mentions, but not in conjunction with images. Still with me?

All right, so the first thing we’re going to need to modify in the RSS output is the images. And don’t believe that official WordPress documentation I mentioned earlier. It. Is. Wrong. My way works.

The next thing we want to do, and we’ll roll it into the same function (because I want to contain the madness), is to add in the event’s location. There’s no RSS tag to account for something like this. You could add it to the <description> tag, although I found that since the WordPress rss2_item hook seems to be directly outputting RSS XML as it goes, I didn’t track down a way to modify any of the output, just add to it.

There’s another standard RSS tag that WordPress doesn’t use — or at least doesn’t seem to use — the <source> tag. This is supposed to be used to provide a link and title of an external reference for the item, but I’m going to take the liberty of misusing it to pass along the location name instead. In my particular case I’m not using it as a link; I just need the text of the location name. But the url attribute is required, so I just stuck the event’s URL in there. (I also added a conditional so this is only inserted on events, not on other post types. But for images I figured it would be a nice bonus to add the featured image across all post types on the site. You may want to add your own conditionals to limit this.)

Here we go:

function my_rss_modify_item() {
  global $post;
  // Add featured image
  $uploads = wp_upload_dir();
  if (has_post_thumbnail($post->ID)) {
    $thumbnail = wp_get_attachment_image_src(get_post_thumbnail_id($post->ID), 'thumbnail');
    $image = $thumbnail[0];
    $ext = pathinfo($image, PATHINFO_EXTENSION);
    $mime = ($ext == 'jpg') ? 'image/jpeg' : 'image/' . $ext;
    $path = $uploads['basedir'] . substr($image, (strpos($image, '/uploads') + strlen('/uploads')));
    $size = filesize($path);
    echo '<enclosure url="' . esc_url($image) . '" length="' . intval($size) . '" type="' . esc_attr($mime) . '" />' . "\n";
  }
  // Add event location (fudged into the <source> tag)
  if ($post->post_type == 'tribe_events') {
    if ($location = strip_tags(tribe_get_venue($post->ID))) {
      echo '<source url="' . get_permalink($post->ID) . '">' . $location . '</source>';
    }
  }
}
add_action('rss2_item','my_rss_modify_item');

You might be able to find a more efficient way of obtaining the $path value… to be honest I was getting a bit fatigued by this point in the process! But it works. You really only need that value anyway in order to fill in the length attribute, and apparently that value doesn’t even need to be correct, it just needs to be there for the XML to validate. So maybe you can try leaving it out entirely.

Put it in MailChimp!

OK… I’m not going to tell you how to set up an RSS Campaign in MailChimp. I already linked to their docs. But I will tell you how to customize the template to include these nice new features you’ve added to your RSS feed.

Edit the campaign, and once you’re in the Campaign Builder, place an RSS Items block, then click on it to open the editor on the right side. Set the dropdown to Custom, which will reveal a WYSIWYG editor full of a bunch of special tags that dynamically insert RSS content into the layout. For the most part you can edit everything here… except for the image. You’re going to have to insert one of these tags into the src attribute of the HTML <img> tag. That requires going into the raw code view, which you can access by clicking the <> button in the WYSIWYG editor’s toolbar.

A few key tags:

*|RSSITEM:ENCLOSURE_URL|*
This is your code for the URL of the image. Yes, it has to be put into the src attribute of the <img> tag directly. There’s not a way that I could find to get MailChimp to recognize an <enclosure> as being an image and display it inline.

*|RSSITEM:SOURCE_TITLE|*
This will display the location name, if you added it to the <source> tag.

*|RSSITEM:DATE:F j - g:i a|*
I just though I’d point this out: you can customize the way MailChimp shows an event date by inserting a colon and a standard PHP date format into the *|RSSITEM:DATE|* tag. Nice!

If you’re interested in a nice layout with the featured image left aligned and the event info next to it, here’s something you can work with. Paste this in its entirety into the WYSIWYG editor’s raw code view in place of whatever you have in there now. Yes, inline CSS… welcome to HTML email!

*|RSSITEMS:|*
<div style="clear: both; padding-bottom: 1em;">
<img src="*|RSSITEM:ENCLOSURE_URL|*" style="display: block; float: left; padding-right: 1em; width: 100px; height: 100px;" />
<h2 class="mc-toc-title" style="text-align: left;"><a href="*|RSSITEM:URL|*" target="_blank">*|RSSITEM:TITLE|*</a></h2>

<div style="text-align: left;"><strong>*|RSSITEM:DATE:F j – g:i a|*</strong><br />
*|RSSITEM:SOURCE_TITLE|*</div>

<div style="clear: both; content: ''; display: table;">&nbsp;</div>
</div>
*|END:RSSITEMS|*

Update! I have encapsulated this functionality, along with some configuration options, into a plugin. You can download it from the WordPress Plugin Directory.

Shut off and lock down comments on your WordPress site with 5 lines of SQL

Comments are kind of passé. Well, OK, they’re still everywhere, but they’re almost universally garbage. Meaningful discussion happens on social media these days, even if it’s prompted by a blog post. And if you’re using WordPress as a general-purpose CMS rather than just as a blogging tool, then you probably have no use for comments whatsoever.

Yet, they’re built in, and they’re a spam magnet. Even if your templates aren’t actually showing comments anywhere, the default WordPress settings allow comments to come in, cluttering up your database and nagging you with a disconcertingly large number in a bright red circle in the WordPress admin bar.

Yuck.

Fortunately, if you have direct database access and the fortitude to run a few simple lines of SQL, you can quickly accomplish the following:

  1. Purge all queued spam and pending comments (while safely retaining any old, approved comments for archival purposes
  2. Prevent any new comments from appearing on any of your existing posts/pages
  3. Prevent comments from ever being accepted on future posts/pages

The last of those is a simple setting. In WP admin, you can go to Settings > Discussion and uncheck the second and third boxes under Default article settings at the top of the page. Actually, uncheck all three of those. If you’re going to turn off incoming pings, you should turn off pingbacks. But my SQL code below doesn’t.

screen-shot-2016-09-09-at-12-22-19-pm

If you’re just starting a brand new WordPress site and you don’t ever intend to allow comments, just go and uncheck those boxes and you’re done. But if you’re trying to rescue a long-suffering WordPress site from drowning in spam, read on.


Here then in all of its glory is the magic SQL you’ve been looking for:

DELETE FROM `wp_comments` WHERE `comment_approved` != 1;
DELETE FROM `wp_commentmeta` WHERE `comment_id` NOT IN (SELECT `comment_ID` FROM `wp_comments`);
UPDATE `wp_posts` SET `comment_status` = 'closed', `ping_status` = 'closed';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_comment_status';
UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_ping_status';

Want to dissect what each of these lines is doing? Sure…

Line 1

DELETE FROM `wp_comments` WHERE `comment_approved` != 1;

This is going to delete all “pending” and “spam” comments. It leaves approved comments untouched. Note: you may have spam comments that are approved; one site I was just working on had thousands that were “approved” because the settings were a little too generous. I can’t give a catch-all SQL statement to address that problem, unfortunately. It requires analyzing the content of the comments to some extent.

You’d think maybe `comment_approved` = 0 would be better, but I found as I poked around that the possible values aren’t just 0 or 1. It may also be spam. It may be something else. (I haven’t researched all of the possibilities.)

Line 2

DELETE FROM `wp_commentmeta` WHERE `comment_id` NOT IN (SELECT `comment_ID` FROM `wp_comments`);

There’s a separate table that stores miscellaneous meta data about comments. There’s a good chance there’s nothing in here, but you may as well delete any meta data corresponding to the comments you just deleted, so here you go.

Line 3

UPDATE `wp_posts` SET `comment_status` = 'closed', `ping_status` = 'closed';

This is going through all of the existing posts — which don’t include just “posts”… “pages” are posts, “attachments” are posts… anything in WordPress is a post, really — and setting them to no longer accept comments or pingbacks. This doesn’t delete any comments on the posts that were already approved; it just prevents any new ones.

screen-shot-2016-09-09-at-12-21-58-pm

It’s the equivalent of going into every single post and unchecking the two boxes in the screenshot above. But it only takes a couple of seconds. FEEL THE AWESOME POWER OF SQL!!!

Line 4

UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_comment_status';

Remember that screenshot near the beginning of this post, showing the three checkboxes under Settings > Discussion? Well this is the equivalent of unchecking the third one.

Line 5

UPDATE `wp_options` SET `option_value` = 'closed' WHERE option_name = 'default_ping_status';

And this is the equivalent of unchecking the second one.

So there you have it. No more comments, no more spam, no need for an Akismet account.