The WordPress SQL Executioner
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:
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
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
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.
- Download SQL Executioner v1.2 from the WordPress.org Plugin Directory (or from me here)
- Unzip the file and upload the
sql-executionerfolder to your
- 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.
Aww yeah! before it executes, can you have it say >>”Your sysadmin has probably already given you this lecture:
1. with great power comes great responsibility
2. there is no 2″
I actually forget how that goes now.
WordPress Suicide prompts you “For the love of God, are you sure?” but that was a little too overtly religious for my tastes, so for this one I decided to go with the lamer: “This query may modify data in your database. There is no undo. Are you sure?” Of course that only comes up for inserts, updates, and deletes, not selects and the like.
But I do like “with great power comes great responsibility” or “you are playing with fire, you might get burned…”
Can i use this plugin to delete all posts from a specific categorie?
I´m working with WordPress 2.3.3.
You should add scheduling on it using the internal wp cron
I liked “For the love of God, are you sure?” Had me laughing for about ten minutes.
I wonder if you can tell me if I can delete all the comments in moderation with this. I neglected this blog for months, and some spammers got ahold of it and now there are 6,700 spam commnets in moderation. WordPress only lets me perform a bulk spam moderation to 20 at a time. Can you help me figure out the SQL on this?
Andy, try this (make sure to backup your database first!):
does this still work in the udpated versions of WP? like.. 2.7.1 for example?
YOU ARE A HERO! Thaks Justin! I LOVE WordPress SQL Executioner – saves me so much time.
the plugin doesn’t works with WP v2.8.x
Can I link statements together? like:
Tony, yes, I believe you should be able to enter multiple SQL statements separated by semi-colons. When in doubt, give it a try with something benign, e.g.
“You do not have sufficient permissions to access this page” happens when I click here:
Execute SQL commands on your WordPress database. *Goto Manage > SQL Executioner* to operate.
Disregard, sorry — issue was I put the plugin in a directory under plug-ins (neatness habit of mine).
Once I installed directly in plug-in directory works like a charm (just needed one simple query):
478 rows affected
is this current for june 2011, wordpress version 3.1.3?
how could I pull most recent entries from a compressed wp database file
and insert them into the live proper database.
situation: webhost somehow began directing traffic for the blog onto an old server with an old account that used to host the blog. So users were fed the old blog, which connected to the old database, and hence entries were made to the wrong database.
Now traffic will be once again properly directed, and the host has provided me with a compressed backup of that old database which also holds a few new entries. I just want to merge the “new” (mis-directed) posts (and any user account settings) into the current, live database that should have received teh posts (and user settings) in the first place.
Any help? the proper database is running wp 3.1.3, the wrong database was running wp 3.1.2.
TIA, for ANY help you can provide!!!!
ionrane, this plugin should work for the 3.x version of WordPress.
As far as how to get your various databases sorted out, I’d suggest a brute force approach: just manually update the current blog with the content that was accidentally updated on the old database. Good luck.
as we know posts have comments, pictures and taxonomy and deleting posts only will remove all dependencies or these will become orphans?
myown, correct. WordPress’ DB schema does not enforce referential integrity, so deleting posts may leave orphan comments and other records.
I run a WPMultiUser site. Some people like to hotlink to images. This could cause big copyright problems for me. I need to disable (even leaving a big red X there is okay) all of the hotlinked images already posted.
What approach would you suggest to removing that html code, enough of it to cause the images not to display?
Yes, I’m even willing to make an ugly bribe. Seriously, this is an important problem I need to straighten out. I will definitely give a nice donation. After all, you’re helping me with a commercial problem.
Oops. I thought I had a search and replace plug-in here earlier. Obviously the executioner is not going to help me.
Хороший плагин, но не работает с кириллицей.
When I execute the below query it puts a 0 in the field.
If I remove the post_content + then the below query works.
Glenn, in SQL,
+is an addition operator only, it doesn’t do string concatenation. For that you need to use the SQL
concat()function. Try this instead:
The concat() function did the trick.
is there a detailed video tutorial on how to use SQL Executioner?
Your plugin has saved me quite a few headaches, awesome stuff here man, I’ll donate once client pays up lols..
What is the appropriate syntax to reach custom field data? It appears that your plugin (which is incredible) does not make the custom fields apparent. I am trying to change the value in the publish-date field based on a value in a custom field, such as:
set job_created_at = now()
where meta__premiumcode = “Urgent”
“premiumcode” is a custom field and so it does not show up as a column in the $wpjb_job” table along with the standard fields.
Obviously i am fairly new to this so this may seem elementary to others but please help me if you can.
kwalker, I’m not sure where your “job” table is coming from, a plugin perhaps? If I’m understanding you correctly, generally custom post fields are stored in the postmeta table. You’d find the custom field name in the meta_key column, and the value in meta_value. Generally you’d join the postmeta table with the posts table. Feel free to email me if you still have questions.
UPDATE acnh_comments SET comment_approved=’spam’ WHERE comment_approved=’0′;
Justin, this could be a timesaver that I’m looking for.
WordPress ET Divi theme site developed on localhost at dinokart2.co.uk
Switched it over to the live environment at dinokart.co.uk
All the links work fine, as do the product images in Woocommerce.
However, any images in placed in the product text area have kept the old URL. Ie. dinokart2.co.uk/etc
Is there a way of using this neat plugin to fix the issue, modifying all the wrong URLs to dinokart.co.uk/etc?
Hope I’m making sense!
Norman A, yes, though definitely test this on your localhost with a backup of your WordPress database just to be sure you don’t break anything. Based on your comment, I think this is the query you need:
That will change all instances of of the text dinokart2.co.uk in your post content areas to dinokart.co.uk. Feel free to email me if you have any other questions.
Thanks Justin, very kind of you. Will give it a test drive!
How to use sql trigger in wordpress plugin i tried dbDelta, $wpdb->query(); nothing works
how to post the tables on a post or a page
Saludos amigo. Interesante el plugin. Estoy empezando a trabajar con WP y me parece bastante amigable. Pero te planteo un problema a ver si me puedes ayudar. En la Web que estoy armando cree unas bases de datos donde se almacena informacion proveniente de un formulario personal que arme en Html. Tengo un plugin qur me permite introducir el php y aqui es donde viene el problema. Con secuencias de sql consigo conectarme a la BD y a la tabla. Pero no consigo que sql introduzca valores nuevos. Cuando coloco las sentencias para insertar pareciera que WP no ejecuta las lineas ya que lo que esta dentro de php ninguna linea se ejecuta. Espero me puedas dar una idea u ayudar u decirme si este plugins me puese ayudar a trabajar con mis bases dd datos