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 start up the MySQL command line client, connect to the WordPress database 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 I opted to call it the WordPress SQL Executioner. Actually it’s more of a web-based version of the mysql command line client. It performs both selects and describes for analysis, and inserts, updates, and alters for modification. In fact you can send it just about any SQL command MySQL accepts. Here’s a screenshot to give you an idea of what I’m talking about:

Simply enter your SQL query and click “Execute SQL”. If it’s a select, describe, show, or explain, an HTML table will be returned with your results. If it’s any other command that modifies data or structure, 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 fate is in your own hands.

The tables listed above the SQL box 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 wp-sql-executioner-1.0.zip (v1.0)
2. Unzip the file and upload wp-sql-executioner.php to your plugins directory:
   /path/to/wordpress/wp-content/plugins
3. Activate the plugin (don’t forget!)
4. Goto Manage > SQL Executioner and have fun!

As always, questions, comments, and suggestions are always welcome.

Feel free to if you found this useful.

• WordPress Suicide
• Broken blog = man obsessed
• Yes I know my blog is busted
• how to count unique records with sql
• tracing intellectual meanderings

name

blog (optional)

comment