If I had a dime for every time someone said “WordPress Suicide is great, but I only want to delete posts that are more than a year old,” I’d probably have 50¢. Which is about when I start to think about solving the problem with a new plugin.
The programmer/database jockey in me knows the request above is insanely easy. Just open up a terminal, connect to my WordPress database with the MySQL client, and fire off the following query:
delete from wp_posts
where post_date < date_sub(curdate(), interval 1 year)
Of course knowing SQL takes experience, but the first barrier for most people is just getting command line access to their WordPress database (especially on shared hosts). I figured if it was easier for the average WordPress user to gain direct access to their database from within the admin interface, it’d be a whole lot easier for me to help them, by giving them custom one-off SQL queries to run. And oh by the way, this would be pretty awesome for the power user.
So that’s what I did, I built a stripped-down phpMyAdmin in the form of a WordPress plugin—“wpMyAdmin”, if you will—though in the end I opted to name it the more ominous-sounding: SQL Executioner. Here’s a screenshot to give you an idea of what I’m talking about:
Screenshot of the SQL Executioner
Simply enter your SQL query and click “Execute SQL”. It behaves a lot like a web-based version of the MySQL command line client. If you enter a SELECT, DESCRIBE, SHOW, or EXPLAIN query, an HTML table will be returned with your results. If it’s any other command that modifies the data or structure of your database, you’ll get a message about the number of rows affected. You’ll have one chance to confirm that you want to execute an INSERT, UPDATE, DELETE, or ALTER, but after that, your blog’s fate is in your hands. Good luck.
The tables listed above the SQL textbox are shortcut references to all the tables in your database. This makes it possible to write a generic SQL query with variables in place of the actual table names so that it’ll work on any WordPress install, regardless of the table prefix (it’s usually wp_). Of course the actual table names with prefixes are accepted. For example, the query above could be rewritten as:
delete from $posts
where post_date < date_sub(curdate(), interval 1 year)
Finally one handy bonus feature, if you click on any of the table names in the list, it will automatically describe the table for you.
Unzip the file and upload the sql-executioner folder to your wp-content/plugins/ directory
Activate the plugin
Go to Tools > SQL Executioner and have fun querying!
Questions, comments, and suggestions are always welcome. If you’re interested in contributing to the code behind the SQL Executioner, it’s hosted on GitHub.
I wish there was a blog where I could learn about the place I live and the people who live (and blog) here. I want to read about things to do, things to see, written by the people who are doing them.
The funny thing is there are so many people trying to solve this problem in different ways, all interesting, all addressing part of the problem, but no one gets close to what I want.
What I want is something like a Boing Boing for San Francisco. A “directory of wonderful things” about the Bay Area, by and for the community. The MetaFilter model comes to mind, but less insular.
Of course the forward-looking part of me would like the concept to scale beyond San Francisco to any metro area, a la craigslist, because it’s hard to live here and not think about starting some kind of technology-cum-media company in your spare time. But let’s not get ahead of myself.
This, in essence, was the big idea that I alluded to last week. Not original or earth-shattering by any means (even MetaFilter Matt has blogged about this), but borne out of a frustration I’ve felt at the hands of the existing SF-specific blogs. And an awareness that I have a need that is not being met. And maybe I’m not alone.
It’s possible that the reason this type of blog doesn’t exist (in San Francisco, no less!) is because it’s actually impossible in practice. The very nature of a good blog has a lot to do with the personality and voice of the blogger, not the city the blogger lives in. Dooce is Dooce because of Heather Armstrong, and though Utah plays a primary role, I’m guessing Dooce would continue to be Dooce from anywhere. Because of our mobility, it’s hard for a blog to be rooted in a single place. And it’s hard to define San Francisco as a single place.
So how do you create a great blog about a place that doesn’t rely on a single voice?
Note: Even though I originally wrote this for WordPress v2.3, it continues to work for all versions of WordPress through v2.9.x.
Background
Who knew that when I migrated my Blogger blog to WordPress way back when, I’d still be supporting a hacked together Haloscan importer 3 years later!
Awhile back, I don’t remember if it was during WordPress 2.2 or 2.3, the folks at Google revamped Blogger, which meant the old Blogger importer no longer worked. So the heroic coders that do the heavy lifting for WordPress set out to write the-Blogger-importer-to-end-all-Blogger-importers, this time using Blogger’s feed-based “API”.
The problem for you classic Blogger folk with Haloscan comments is that the only way to link your Haloscan comments to their post is via Blogger’s postID. Which is apparently not accessible in the feed API. Grrr. Which meant my previous hack for associating the postID with the newly imported WordPress post no longer worked.
Last night, I finally went back to the drawing board and came up with a new solution. I’ve tested it successfully on two different blogs.
Last Sunday I got to see Michael Ruhlman read from his new book, The Elements of Cooking at the San Francisco Ferry Building.
Michael retold the story of what led him to that particular moment in time. An early love of the cooking and the kitchen, seeing Julia Child on TV, a botched attempt at an apple pie cum pear tart, persistence in getting into the CIA to write a book, meeting a restaurateur in Cleveland who connected him with Thomas Keller of the French Laundry who was looking to publish a cookbook…
He emphasized luck. He was lucky to write a book about the Culinary Institute at a time in this country’s history when Americans were developing a passionate interest in food and cooking. He was lucky to meet up with Thomas Keller at a time when he was merely a “guru among chefs” and not yet an internationally known celebrity chef.
He also emphasized speaking the language. Though he was a writer first, his culinary training and ability to speak the vernacular (and hand gestures) of a professional cook meant he was able to quickly earn their trust, and thus receive greater access for his books.
Michael seemed keenly aware of the impact and importance of blogs on writing and the publishing industry. He asked how many people heard about the reading through a blog (2/3 of the 30-40 people there raised their hands—I’d heard about it through his blog). He only recently started blogging at the insistence of Meg Hourihan, and had already come to accept it as an important part of his life, like a pet that needed care and feeding. A place to continue the conversation outside of his books.
Finally he read a passage from Elements on finesse. A survey of some of those almost unquantifiable and subjective tasks a chef will undertake to make a great dish sublime. I might say to make a great dish art.
I asked a question I’ve been meaning to speculate about on my blog—whether he considers his writing something akin to long-form blogging. Of course the obvious answer is no, given the entirely different demands of blogging and book writing. Namely his goal in writing a book is to create a cohesive narrative, to turn life into something that has an identifiable beginning, middle, and end. Whereas in his blogging he said he doesn’t worry about making sure every “i” is capitalized, let alone the rest.
I persisted though and suggested how reading a book is almost like engaging in this distant, unreachable world. Even if it’s nonfiction, the distance of that world from me, and the perfect encapsulating description of the characters make it indistinguishable from fiction. But when I started reading his blog, it took all these characters I remembered from his first two books books, real people he knew and still had contact with, and imbued them with new life. Updated them. It kind of punctured that artificial narrative ending and continued the story.
That and he really seems to have embraced his inner-chef as of late. He admits that his time at the Culinary Institute changed his life. Though he went on to write non-culinary books (experiences he managed to weave into his recent food writing), it seems like he’s starting to define his life around these culinary pursuits, with gigs at the Food Network, etc. He’s never just writing about someone from that “objective” journalistic perspective, he’s very much a character invested in his own stories, as much as we’re all characters in our own blogs.
My usual blogging style is notoriously sluggish. I’ll start with an idea, write a paragraph or two, futz with some photos if I’ve got any, surf the web and get distracted, search for a relevant link, lose my train of thought, reread what I’ve written over and over again, check my email and feedreader, find some food to eat, clip a hangnail that’s bothering me, check out Google News, reload Boing Boing…
Even a short post can suck up 2 or 3 hours like this. It’s ok, I figure, at least I’m not watching TV. But during our trip to Utah, I didn’t have that luxury, so I had to be as efficient as possible with only an hour or so of internet time per day (I know!).
I’ve also been wanting to post more photos than was practical with my paragraph-photo-paragraph-photo format, since some photos really deserve no more than a few words intro, if that. So I started structuring my posts with the bulk of the text at the top, and the images at the bottom. That way I could write the way I wanted to write, from start to finish, without forgetting any juicy details, and then afterwards I could mentally shift to my non-linguistic cortex and work on the photos, without having to break from the narrative or write redundantly descriptive, captain-obvious filler between photos.
Once the writing is down, I immediately go to work on the photos, in the order they were taken, maybe with one quick preview of the set ahead of time. At this point my Gimp Web Photo Editor extension is a crazy time-saver. In most cases, I just tweak the settings for the first photo and use those for each one that follows. After that it’s a quick ls -cr in the terminal to list the photos in order of last modified date, which I copy and paste into my blog post, wrapping each with <img src="/images/ and " alt="" /> and filling in the alt description.
Then I start the revision process, adding relevant links, correcting typos, improving flow, with minimal distraction. After I’ve read it through once or twice without making any changes, I hit Publish, and I’m done with it—ok, maybe I read it through once again on my blog. It’s always amusing how a slightly different context will expose errors and that I missed a dozen times before.
If I had a dime for every time someone said “WordPress Suicide is great, but I only want to delete posts that are more than a year old,” I’d probably have 50¢. Which is about when I start to think about solving the problem with a new plugin.
