6 Mega Time Saving WordPress SQL Queries
Behind the scenes of each WordPress website there is a MySQL database driving it. And although 99% of the time you can make changes without touching this MySQL database, there are some serious time saving queries that can turn out to be lifesavers. In this post I will be listing 6 of these uber time savers.
1) Reset User Password
This can be a godsend if you or one of your clients forget a user’s WordPress login credentials, opps!
UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';
2) Update Siteurl and Homeurl
A great query especially after importing a WordPress website from your localhost or another URL:
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldaddress.com','http://www.newaddress.com') WHERE option_name = 'home' OR option_name = 'siteurl';
3) Find and Replace URLs in Post Content
Another great query to use after importing a database. WordPress stores links as absolute paths, which means things like images within your posts will still be linking back to your old URL after the import. This quick query will find any references to the old URL and replace them with the new URL:
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldaddress.com','http://www.newaddress.com');
4) Transfer Posts from one User to Another
Moving posts between authors manually (post by post) can be a long and tedious task, lucky there’s a much easier way to do this using SQL.
Firstly you are going to need to identify the IDs that WordPress has attached to the users. To do this just login into your WordPress admin area and click the ‘Users‘ link, If you are logged into an administrator account this will bring up all of the registered users on your blog. Find the two authors you are wanting to transfer posts between and click on their name. Take a note of the number next to user_id in the address bar, this will be one of the two IDs you’re going to need to complete the SQL query below.
UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';
5) Delete all Spam Comments in one go!
Nobody likes spam and deleting spam comments one at a time makes you hate it that little bit more. Thankfully with the SQL below you will be able to delete all of those nasty little comments at once and save yourself some precious time:
DELETE from wp_comments WHERE comment_approved = '0';
6) Delete Post Revisions
Overall Post Revisions are very helpful, especially on multi-user sites. However, they can quickly fill up your database with thousands of unneeded records that can cause pages on your website to take a long time to load. The following SQL will delete all Post Revision and any Meta Data attached to them, so use it wisely!
DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);
