27 Handy SQL Query Hacks for WordPress


The post 27 Handy SQL Query Hacks for WordPress appeared first on Make A Website Hub.

If you have been managing a WordPress website for a while then you probably know that this content management platform uses a MySQL database to store every bit of information it needs to display your pages and apply your chosen settings. WordPress and MySQL make a great…

The post 27 Handy SQL Query Hacks for WordPress appeared first on Make A Website Hub.

27 Handy SQL Query Hacks for WordPress

If you have been managing a WordPress website for a while then you probably know that this content management platform uses a MySQL database to store every bit of information it needs to display your pages and apply your chosen settings.

WordPress and MySQL make a great partnership; both are free, and using WordPress means a constant source of new plugins and features to make life easier. But you can manage your site just as well, and in some cases even better, from phpMyAdmin if only you know how to work from the MySQL side. That, of course, is just a matter of running queries against the database WordPress relies on.

It’s not essential to know MySQL to build a WordPress site, but knowing how to run some useful queries that will save you headaches is definitely an advantage.

How to Manage your WordPress Database

If you’re familiar with phpMyAdmin and SQL (structured query language), skip ahead, but if this is new:

You can find phpMyAdmin on your hosting cPanel if you don’t know where to access it. When the console opens, select your site database from the list on the left. It should reflect the domain or username given to you when your WordPress site was set up, followed by suffix “_wp”, if the name hasn’t been changed. It is advised you don’t mess with the information_schema database at this point.

If you see multiple databases and can’t identify the proper database, select them one at a time, and each time open a new query window by clicking the SQL tab. Type in:

Check the entries under the column named option_value. You should recognize your siteurl, blogname, and other information right there on the top few rows. If you the correct info is there, you have the right database. Before you do anything else, feel free to explore the tables and column names to see what you’ve got here. You have to select a database (and thus its tables) to run queries against it. If you want to see what’s in each table, use the SELECT * query above, swapping out wp_options for whatever table name you want to explore. Just don’t change any values yet.

First Step: Backup your Database

WordPress needs the information in the database to re-create every last post, comment, and setting you’ve created. No matter how competent you are with SQL, remember that one typo or mistaken click could destroy information that corrupts your database to the point where your site won’t load at all. Any kind of UPDATE or DELETE query could mean loss of critical site or content information. Backing up the database before you start tinkering with your data means you can always put it back just the way it was.

You can download plugins like WP-DB-Backup or WP-DBManager to assist you with this from your admin dashboard, but you can also do it straight from phpMyAdmin before you get started. The quickest and easiest way:

1. Login to phpMyAdmin.
2. Select your WordPress database.
3. Click on Export at the top of the window
4. In the FORMAT drop-down list, select SQL to export as a .sql file. It should be the default choice.
5. Click Go, and a copy of your database will be downloaded.

If you wanted to, you could click the Custom button for other options.

Alternately, you could go to the Operations tab, enter a file path in the Copy Database To box, select options, and click Go.

Restore a backup of your MySQL database

1. To restore the database, click the Import tab.
2. Leave the FORMAT as SQL or change it to whatever format was used
3. Browse to where you saved your backup of the database.
4. Click Go.

Note that phpMyAdmin does have some file size limits, so if your database has grown very large you’ll have to either trim it down using some of the queries below, or try another method such as one of the plugins. Upload size is usually 2MB, and these are the settings to look for:

post_max_size = 8M
upload_max_filesize = 2M

Note that post_max_size must be the same or larger than upload_max_filesize.
These settings may be set globally across the server depending on your installation, and you can’t change them. If not, you may find them in your php .ini file.

On Apache installations you may be able to change these settings locally by putting a “.htaccess” file in phpMyAdmin’s directory. The contents of the file should be:

php_value post_max_size 20M
php_value upload_max_filesize 20M

Change ’20M’ to however large you want file limits to be. If you get Internal Server Errors (500) then this likely means your host does not allow you to change these settings and you need to remove this file.

If you’re restricted by file sizes, you’ll just have to make multiple file backups by selecting the Custom checkbox and choosing individual tables for each backup file rather than the entire database, and restoring them one at a time.

SQL Hacks for WordPress Users

Now that that’s out of the way, here are some sample queries you can run from that same query box that will help in managing WordPress and MySQL databases:

 

1. Change your URL

WordPress stores the absolute path for your site URL and home URL. If you transfer your site to another server or domain, it will not load if the URLs are not updated. You can do this by running:

 

2. Update GUID (Globally Unique Identifier)

If you have uploaded your blog site from your computer to a new server or new domain, you also should update the URLs for the GUID field, since this is used to translate the post or pages slug to the absolute path.

 

3. Update URL in Content

Within the content of each post’s data, MySQL stores the old URLs referencing the old source. You need to change these to the new domain.

 

4. Update Image Path

You might have heard about using Amazon CloudFront as a Content Delivery Network (CDN) to offload image delivery. After you’ve created a CNAME record, use the query below to update image paths in WordPress to load from Amazon CloudFront.

You also need to update the GUID for image attachment:

 

5. Add a New Field to Posts

This query adds a custom field to every post on your site. You can name the new field anything you like to easily identify it later by changing ‘MyNewCustomFieldValue’ to what you want (it must not already exist).

The following will do the same for all the pages on your site:

 

6. Find all Posts with Field Name

If you need to find all the posts with the new field you’ve created (or any of the existing fields) you can use the following query, being sure to replace ‘FIELD_NAME’ with the name of the field you are searching for. Of course, you can also do the same for pages by changing ‘post_type’ = ‘post’ to ‘post_type’=’page’.

 

7. Delete Post Meta

Every time you install a plugin WordPress makes changes to the wp_postmeta table. After you remove the plugin, the data will still be there as useless clutter. You can clear it up with this query; just remember to change ‘UselessMetaKey’ to the appropriate value.

 

8. Change Default Username

WordPress creates the default user account under the name “Admin”. This is so obvious that it can be a problem if a hacker or someone else intercepts your account password. They just login and take or delete what they want. For your own peace of mind, you can change this default username.

9. Identify Unused Tags

If you delete your old posts, the old tags for each will remain in the database. This query allows you to identify all of the old tags.

10. Deleting Spam

If you run a blog, you’ve gotten sick of all those spam comments piling up. This one SQL command will clear out all the comments you’ve marked as spam:

11. Reset Password

Here’s a handy one if you’ve ever forgotten your WordPress password or just want to change it (or someone else’s):

12. Re-assign Articles

If you want to take ownership of some old legacy articles that came with the site, or that you now own the rights to, you can easily do it, just so long as you know the ID of old and new authors (which you can get from the Author & User page in the admin panel). Just click the author’s name and look for the user_id field.

13. Delete Revisions

If you edit a post multiple times in WordPress, it will save revision copies, which can add up to quite a bit over time and is a huge waste of resources slowing down your database. Clear them out like so:

This will delete all revisions; if you only want to delete specific ones you’ll have to retrieve the ID number and add it to the WHERE clause.

14. Delete All Unapproved Comments

Swamped with new comments you don’t have time to look at? Try this:

15. Disable Comments on Old Posts

For this query, you can specify the comment_status as ‘open’, ‘closed’, or ‘registered_only’, as well as including date criteria.

16. Export Comment Emails as Unique Records

Accumulating hundreds of comments can also lead to dozens of comments from the same email address. If you want to export all those emails for mailing campaigns or other needs, you can create a list of distinct emails with this query:

When it has returned the resulting list, under Query results operations, select the Export option.

17. Delete Pingback

Your most popular posts could generate tons of pingbacks accumulating in the database. To get rid of them, use this query:

18. Delete Comments from a Specific URL

If you’ve been getting a lot of spam from the same URL, you can remove all the garbage in one pass with the following query. The ‘%’ means you can delete all entries containing that portion of the URL string, which is handy to avoid typing URLs, but you may want to be careful not to be too general and delete URLs you didn’t mean to.

If you have spam comments that all contain the same URL, then this query allows you to remove them in one go. The following query will delete all comments with a particular URL. The ‘%’ means that any URL containing the string within the ‘%’ signs will be deleted, so I was sure to add the qualifier that this only applies to spam comments.

19. Identify & Delete Posts that are over ‘X’ Days Old

If you ever need to identify and delete posts that are over a certain amount of days old, then this snippet will help.

To identify any posts that are over ‘X’ amount of days run this query, remembering to replace the ‘X’ with the number of days you are looking for:

To delete any posts that are over ‘X’ amount of days run this query:

20. Change WordPress Posts into Pages (or Pages into Posts)

Changing posts to pages is easy enough:

… and if you want to change them back again:

To change individual posts (pages) you’ll have to include the correct ID field in a WHERE clause.

21. Removing Shortcodes

One of WordPress’ features is that it allows us to reuse repeated snippets of code by identifying them via shortcodes. Shortcodes are convenient, but if you decide you don’t want to use one anymore, run this query, replacing “trashshortcode” with the name of the one you’re trying to lose.

22. Find and Replace Post Content

If you find you need to update or change existing text across multiple posts, for whatever reason, the easiest way to do it is with the following easy query:

23. Find Posts where Field is Missing

If you want to find all the posts or pages where, in fact, your new field has not been added, you can just use this query, again, being sure to change ‘FIELD_NAME’ to the name of the field you’re checking for, or changing ‘post’ to ‘page’ to find the pages where this field isn’t used.

Use this MySQL query to get all posts with a missing custom field. We’ll query post_type=page for the purpose of this tutorial. You will have to replace CUSTOM_FIELD_NAME with the actual custom field name.

24. Queries to Find Edited Posts

It can be sometime handy to query recently modified (last month or last few days) posts in wordpress mysql tables. Here are some handy queries:

If you’re constantly updating facts and figures in your posts, it can be easy to lose track. The following series of queries will help you to find posts that have been recently modified for specific time periods:

In the last month:

In the last week:

You can change the interval to whatever value you need, such as DAY or HOUR.

25. Find All Posts by Category

First you will need to find the id for a category name. An easy way to find the id for a specific category of posts is the following query (replacing ‘GENERAL’ with the name of the category you had in mind:

Then you can find all posts by that term_taxonomy_id (replacing 999 with the correct ID):

26. Which Posts Get the Worst Spam

You can find out the top 10 posts on your site that seem to attract the most spammers. You can always change the ’10’ to a 5 or 20, or whatever you’re interested in.

27. Monthly Posts

If you are allowing posts from guests or other authorized users, you can determine how many articles come from which author for a timespan of say, 1 month in this example.

When you’ve got a lot of articles to juggle, this can help generate data for post tracking, such as overall trends or site growth. If you wanted to, you could be more specific by adding post_author to the WHERE clause or specifying a particular MONTHNAME and YEAR.

As you can see, there’s almost nothing you can’t do with your WordPress site in the MySQL database.

There are a variety of good tutorials available out there, but hopefully we’ve simplified a lot of tasks for you by providing the above examples.

Good luck!