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.

Instructions

1. Download SQL Executioner v1.2 from the WordPress.org Plugin Directory (or from me here)
2. Unzip the file and upload the sql-executioner folder to your wp-content/plugins/ directory
3. Activate the plugin
4. 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.