When managing large WordPress websites that contain thousands of posts, pages, categories, comments, and hundreds of authors, there are times when you need to make extensive changes. For instance, you might need to convert all the posts under a certain category into pages, or remove all the comments marked as spam. Rather than spend hours, you can run a few database queries.
WordPress saves almost everything into a database (generally MySql). But be careful. ALWAYS do a backup before executing SQL queries into your database. This will prevent headaches – trust me. It takes a few minutes, and it will save you hours, days or even months of work.
Here’s how queries can help you in your daily work.
Scenario 1: A terrible plugin
A friend invites you to try a super great new plugin they found surfing the WordPress directory. You have the “great” idea of testing it into a “production” website. What happened next? White screen of death! All over your website – the frond end, the back end. Everything is white. What can you do to fix it? Because you know the problem is the plugin, run a query to disable it and fix the issue. You are safe – so breathe normally again.
Look for your hosting information – you should have received an email from them when you bought the service. Log into the hosting backend, search for your database administration tool – probably PhpMyAdmin – and run this simple query:
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
Buala! You can now access your WordPress backend and enable the plugins you had and delete the bad one. Then call your friend with the dodgy plugin suggestion.
Take home: Never try a new/unknown plugin on a production website. First install it into a development one. You can create a WordPress website very easily and test it here. It will save you time, and also angry clients.
Scenario 2: Who is that author? That isn’t me!
A new client asks you to build a website. You discuss what they want, hire the hosting, find a nice theme, and work on the content. During the first week, you create a few pages – let’s say 10 pages, and 10 posts. And then the client says, “Hey! I’ve written some new blogs and articles, can you add them to the site?” “No problem at all,” you respond. You spend the next week adding lots of new articles and blogs, and now have more than 50 pages and 100 posts.
After fixing some small issues, everything is in place to take the site live. You happily call your client, and tell them “It’s ready. Take a look”. What’s the first comment of the client? “Why are you the author of all the pages?” In that moment you think, “Why did I use my user to create all the content?” Don’t worry – there’s a query for that.
First, don’t forget to create the new author. Once you have it, run the following query to get the author id (if you don’t already know that).
SELECT ID, display_name FROM wp_users;
Then run this query:
UPDATE wp_posts SET  post_author = NEW_AUTHOR_ID WHERE post_author = OLD_AUTHOR_ID ;
Take home: If you are working on a website with many writers – journalists, for instance, where article ownership is important – remember to create the authors first, so you can assign them immediately.
Photo Credit: http://www.sxc.hu/photo/1092552