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.

Rules, Rules, Rules

I think a lot about rules. I’m not a rigid stickler for rules. I believe a lot in taking rules in context. There are times rules matter, and times they really don’t. But I do think it’s important to understand the rules. There are two things to understand about rules: 1) that they exist to keep things running smoothly, and 2) that there is (or at least should be) a reason behind any good rule. Rules that have no clear, broadly agreeable purpose or that are difficult to follow should be reconsidered.

But a lot of rules are pretty simple. Like the rules of the road. And I think a lot about rules of the road, because I’m on the road a lot, in various ways — in a car, on a bike, or on foot.

The rules of the road are simple, but they don’t seem quite as simple to me living in Minneapolis in the 2010s as they did when I was a kid growing up in a small town in the 1980s. Back then, roads were for cars. The only people who biked were kids and adults who had lost their licenses for DUI. And you biked on the sidewalk.

People only walked with their dogs, and generally only in a 2-block radius of their house, the lone exception being the one Vietnam vet with untreated PTSD who refused to wear shoes or use the sidewalk. He could always be seen around town with his dreadlocks, blanket and bare feet, shuffling along in the boulevard grass. I hope things got better for him.

But I digress. That was the 1980s. In contemporary Minneapolis, everyone uses the roads for just about everything. And sometimes it gets messy. There are many places in the park areas of the city where there are three parallel strips of asphalt: a pedestrian path, a bike path, and the road. All clearly marked for their intended purpose. In most of these places, the road is very narrow — two lanes with no shoulder or parking lanes. But you get pedestrians on the bike path (why? who the hell knows?) and bikes on the road (why? to get away from the dumbass pedestrians! or because they think they’re in the Tour de France!) and things get tangled up.

Even when you’re on regular city streets, biking can be a hazardous endeavor. The rule (whether it’s codified as a city ordinance or just a gentle encouragement on road signs) is “SHARE THE ROAD”. But there are cars that nearly run bikers onto the curb, just as there are bikers who ride side-by-side at such a leisurely speed that I wonder how they keep their balance, backing up car traffic for blocks. SHARE THE ROAD goes both ways.

Me? I’m too scared as a biker to ride on major thoroughfares if I can possibly avoid it. I usually stick to those dedicated bike paths when I can. Otherwise, I try to ride on low-traffic residential streets, generally a block or two over from the major thoroughfare. It just seems much, much safer.

But you do encounter clueless drivers. Drivers who will stop for you at an intersection when they don’t have a stop sign and you (the biker) do, and are clearly coming to a stop. Or, drivers who will breeze right through their own stop signs even when you (the biker) have the right-of-way, either because they didn’t see you or because they live a block away and always breeze through that stop sign.

Yes… you may have guessed that I am not just speaking hypothetically here. Both of those situations in the previous paragraph have happened to me. In fact, they happened on the same street, one block apart. Obviously the latter situation (which happened last year) is far more dangerous, and it led to me braking so abruptly I nearly flipped my bike, followed by a loud string of profanity hurled in the semi-apologetic driver’s direction.

The former situation happened to me just this morning, and prompted today’s rant. I was approaching a stop sign, and slowing to a stop. To my left, a white SUV also came to a stop, even though they didn’t have a stop sign. Presumably they didn’t trust that I was going to stop, even though I was vigorously waving them on with my left arm as I braked with my right. Even though I came to a complete stop, got off my bike, and even more vigorously waved them on. Finally they did go and I got the satisfaction of having successfully enforced the rules. (Sort of. I mean, I didn’t actually give the universal “stop” hand signal. Yes, I broke a rule. But I figured my vigorous waving-on covered it.)

But that got me thinking about the rules themselves. You have the official rules of the road, which tell you that you stop at a stop sign and don’t stop when you don’t have one. Bikes are supposed to follow the same road rules as cars, with (as I recently learned) a few exceptions designed to facilitate faster movement, most notable being that it’s OK for a biker to run a red light, if they have first come to a complete stop and verified that there is no other traffic (cross traffic or oncoming left turns, for example). But I doubt a lot of drivers know this rule, and when they see bikers doing it, probably assume (like I would have before) that it’s one more biker breaking the rules.

Which gets me to the second kind of rules — the unwritten, unspoken rules that grow naturally from collective experience. There are so many bikers who completely ignore all of the rules of the road that many drivers either a) assume the worst out of any biker they encounter and exert excessive caution or b) hit the bikers. (Or, as happened last year, they lose their fucking minds and drive around hurling cinder blocks out their car windows.)

I feel like the situation I ran into today was due to the second type of rule. The driver of the white SUV has encountered enough unpredictable bikers — who are best known for their peculiarly selective blindness to red octagons — that they weren’t going to take any chances with me. So the fact that I did follow the rules and stop for a stop sign actually caused a problem. A minor problem, to be sure, but still enough that it lingered with me all morning. (I wonder what that driver is thinking about right now. Almost certainly not me. This is my affliction.)

So, we are living in a society where we have two types of rules: the official rules, and the unspoken ones. Often in direct conflict. Which rules take precedence? Sadly, as much as I want to live in a world where the official rules are logical, reasonable, fair to all, and easy to follow, I fear that we really live in a world where the official rules are so often inconsistent, incomprehensible, unjust or just simply a burden — not to mention out of touch with the realities of human behavior — that the unspoken rules become the ones that people actually follow.

So then what? Should I just give up on the official rules? Should I breeze through stop signs on my bike because “everyone else is doing it”? Should I stubbornly adhere to my way of doing things and get my dander up every time I have to frantically gesture at someone else to get them to accept their own right-of-way?

Or, should I just lighten the hell up?

In that spirit, I come to the third type of rules. The Rules.

The Rules is a tongue-in-cheek book of… rules… written by a former coworker and bandmate who is obsessed with cycling to a level I will never be. I ride a secondhand bike to get around town. I have become quite a fan of watching the Tour de France every July, in part just because there’s an app for it that I feel really does 21st century sportscasting right and I wish every sport were covered this way. But mostly because I enjoy seeing the French countryside, admiring the intensity and endurance of the riders, and, occasionally, moments like riders punching morons on the sidelines.

Anyway… forget about city ordinances or social norms. The real rules of cycling are another matter entirely. And far more entertaining than my rants will ever be.

Hey look, new fonts

I’m sure by the time you read this, 5 years from now, I will have changed things 8 to 10 times since writing this, but as of right now… hey, look. Same old site design, but with new fonts!

One new font, actually: Work Sans, in three weights. It’s a great new, no-nonsense but aesthetically pleasing sans serif font that is free which makes it extra nice. (Though I do not begrudge font designers the right to compensation for their work.)

This one initially got my attention by way of a blog post by the great Khoi Vinh. I figured, if he likes it, it’s worth noting.

Last, This, Next

As I was folding a week’s subset of my embarrassingly large collection of printed t-shirts, I reflected momentarily on the history of my pixelated Minnesota t-shirt. I bought that t-shirt last summer and wore it each time I went to the Minnesota State Fair last year, as my symbol of “Minnesota pride”.

Then I started thinking about sharing this story, and about referring to the Minnesota State Fair that took place in 2013 as the “last” Minnesota State Fair, and how the one that will take place “this” year, in 2014, is “this” State Fair, and so on.

Frequently conversations between SLP and me have resulted in confusion based on the different possible interpretations of “last”, “this” and “next” when referring to days, weeks, months, years or events. I tend to use “this” when I’m referring to any unit of time that occurs within the same larger unit of time, whether before or after the current one, although I may be likely to omit “this”.

For example, today is Thursday. The Super Bowl (or, if you prefer, the Suberb Owl) is happening in 3 days. It’s happening “this Sunday”. But what if today was (or is it “were”? I never get that right, either) already “Superb Owl Sunday” and I was (“were”?) talking about the 5K race I’m running in 7 days later? “This” Saturday seems a bit far off in that case. But “next” Saturday doesn’t feel right to me either. Or does it? Is it better for “next” Saturday to refer to a day that’s 6 days away, or 13?

As for my confusion with SLP, the fact that she lived her life on the September-to-June academic calendar for much longer than I did only exacerbated the situation. I’ve always been a stickler (to the point of ridiculousness) for precision in dates. The first day out of school isn’t the beginning of summer; the solstice is. The first day back in school isn’t the beginning of fall; the equinox is. And the first day back in school in late August or early September most definitely is not the beginning of the new year. (Although yes, Rosh Hashanah usually does occur in September so depending on the calendar you use, there’s an argument to be made.)

Ironically, it was only after SLP stopped organizing her life around the academic year that I embraced calling any of the days in early-to-mid June when our kids are out of school (but which are still technically in spring) “summer”, but I will never give up the idea that “this year” refers to the 4-digit number starting with a “2” that comes at the end of the current date. “This year,” to me, means January 1 to December 31. Period.

But what do I mean when I say “this winter”? Sure, winter technically only starts about 10 days before the new year, so it’s almost entirely in 2014. But let’s be honest. In Minnesota, “winter” usually starts in early December, or sometimes as early as October. By my logic, “winter” in Minnesota begins on whatever day snow falls and doesn’t melt away. We had a few light snows in November, but “this winter” began on December 2, 2013.

My point is: language is fuzzy. Assigning vague labels like “last”, “this” and “next” to our days and events relies on a great deal of tacit agreement between ourselves over meaning. This particular quirk of our language has been causing me trouble since I was a kid. Back then I had a lot of time, sitting around bored in school (which I didn’t even realize was the case until much later in life), to ponder and obsess over and get annoyed by things like this. I was trying to create in my mind a world of precision and clarity that didn’t, and couldn’t, exist. Our minds don’t work that way, the world doesn’t work that way, and language, a product of our minds used to help us understand and communicate with each other about the world, necessarily can’t work that way.

I didn’t understand that then, and I only barely do now. Each of us carries around an entire universe in our mind. It’s built on a foundation laid by our genes and constructed around our experiences — and our interpretations of those experiences. Our language can only achieve an approximation of a fraction of that universe, and we have to rely on the assumption that our own version of the language we use is a close enough approximation of the same things in our own mental universe as the language, and the mental universe it represents, of the others around us.

It’s a wonder we can communicate at all.

From the Stupid PHP Tricks files: rounding numbers and creeping inaccuracy

This morning as I walked to the studio I was doing what geeks do best: pondering a slightly esoteric mathematical quandary.

Glass Half Full by S_novaIngraining the American spirit of optimism at a young age, and under dubious circumstances, our schools always taught rounding numbers in a peculiar way. You always round your decimal values to the nearest integer. That part makes sense. But what if the decimal is .5 — exactly half? In my education, at least until late in high school (or was it college?), we were always taught to round up! The glass is half full. Optimism.

Eventually — far later than it should have been, I think — the concept was introduced that always rounding .5 up is not really that accurate, statistically speaking. It might be nice in the case of a single number to be an optimist and think a solid half is good as a whole, but in aggregate this thinking introduces a problem.

If you have a whole lot of numbers, and you’re always rounding your halves up, eventually your totals are going to be grossly inaccurate.

Of course, the same would happen if you were ever the pessimist and always rounded down.

The solution, I later learned, was to round halves up or down, depending upon the integer value that precedes them. Which way you go doesn’t really matter, as long as you’re consistent, but as it happens, I learned it as such: if the integer is odd, round up; if it is even, round down.

In my work, I write a lot of PHP code. Most of it is of the extremely practical variety; I’m building websites for clients, after all. But every once in a while I like to indulge my coding abilities in a bit of frivolous experimentation, and so today I produced a little PHP script that generates 10,000 random numbers between 1 and 100, with one decimal place, and then it shows the actual sum and average of those numbers, along with what you get as the sum and average if you go through all 10,000 numbers and round them to whole integers by the various methods described above. Try it for yourself!

Any time the rounded average is different from the “precise” (and I use that term somewhat loosely) average, it is displayed in red. Interestingly, and not at all surprisingly, when you always round halves in one direction or the other, at least one of those directions will (almost) always yield an incorrect average. Yet if you use the “even or odd” methods, both of those methods will almost always yield a correct average.

It’s all about the aggregate.