How to Work with the WordPress Database: 11+ Useful SQL Queries

One of the best things about WordPress is how easy it makes it for anyone to have a great looking and functional website without any coding knowledge.

It’s perfectly possible to set up a website from scratch, getting it looking exactly how you want it to, and doing whatever you want it to, without writing a line of code.

However there are advantages to being able to do your own manual tweaks to the WordPress backend. There are several types of code used in the typical WordPress site: HTML and CSS are used to control the visual appearance of the site, PHP and JavaScript provide the functionality of the website, and SQL (in combination with PHP) is used to communicate with the database.

WordPress Database Basics

All the content of your site along with its structure and user information is stored in the database. This makes WordPress sites a type of dynamic website where the HTML is generated in real time as opposed to static websites that use pre-written HTML files.

Every time someone accesses your WordPress site, the content is fetched from the database using a series of SQL queries.

All data used by your site is stored in tables in the database. You can think of a table as a storage container for one type of data, so your data is kept organized by keeping different types of data in different tables.

The core WordPress tables are:

• wp_posts – all the content of your posts and pages as well as menu data and media attachments.
• wp_postmeta – meta data for each post. Meta data is added to this table when you add a custom field to your posts so for example, you could add what music you were listening to at the time of writing the post.
• wp_comments – all your comments on posts and pages including author, date, email, etc.
• wp_commentmeta – meta data for comments.
• wp_users – usernames, passwords (encrypted), and other user data.
• wp_usermeta – meta data for users.
• wp_options – general WordPress settings.
• wp_links – used for blogroll links, not really used on most WordPress sites today.
• wp_terms – categories and tags for posts.
• wp_termmeta – meta data for categories and tags.
• wp_term_relationships – links posts with categories and tags.
• wp_term_taxonomy – taxonomies are used for classifying your data. The WordPress default taxonomies are category, tag, and link category. This table manages the taxonomies including their name and description.

These are the basic WordPress tables but you probably have more that are added by plugins and themes you install.

Your WordPress tables may also have a different prefix. “wp_” is the default but it is often changed for security reasons.

Each table in the database has several fields. For example wp_users has:

• ID
• user_login
• user_pass
• user_nicename
• user_email
• user_url
• user_registered
• user_activation_key
• user_status
• display_name

By using SQL you can view or edit any field in any table of the database. You can also add or delete data and create or delete tables if you need to.

How to Use SQL Commands With the WordPress Database

The PHP code in your WordPress site contains SQL commands to communicate with the database but if you want to carry out manual database operations it’s more convenient to be able to send the SQL commands manually.

WordPress uses MySQL as its database management system. MySQL doesn’t come with a graphical user interface and can be managed via the command line, but it’s much easier for most users to “see” their database tables visually.

There are various graphical user interfaces for MySQL but the most popular one is phpMyAdmin. You can usually access phpMyAdmin from your hosting admin panel:

Depending on your webhost you might be automatically redirected to the phpMyAdmin homepage or you might need to enter a username and password first. These details should have been supplied when you set up your hosting account but contact your hosting support if you’re not sure what they are.

Once logged in you should see a screen like this:

Your databases will be listed down the left hand side and there should be one for each WordPress site you have installed on your server. You might also have some other databases for none-WordPress related things.

If you find the database for the site you want to work on and click on it (if you’re not sure which is the right database, you can find it in your wp-config.php file next to DB_NAME), you’ll get an overview of all the tables in the database, which should be the same as those listed above if it’s a fresh WordPress installation:

On this page you can create a new table and click on existing tables to view the data. There are also various tabs along the top of the page that you can click on to carry out various functions:

• SQL
• Search
• Query
• Export
• Import
• Operations
• Routines
• Events
• Trigger
• Designer

You won’t have to worry about most of these unless you want to get into advanced database operations, but there are a few worth checking out.

Searching the Database

The search tab can be a handy way to find and edit records individually without using SQL commands. For example if you wanted to search for a particular user by email, you could just type the email address into the search box and select the wp_users database (you can also search the entire database).

Once the search is complete, click the “browse” link and click on results to edit them directly. You can also delete and copy records.

Before we go any further, it goes without saying (but I’m going to say it anyway) that you should back up your WordPress database before making any changes to it. A simple typo in an SQL command could mess up your entire site and it’s not always easy to fix mistakes, especially if you’re not sure how you broke things in the first place

How to Backup Your WordPress Database in PHPMyAdmin

There are various WordPress backup plugins that will backup your whole website, including the database, but as you’re already in PHPMyAdmin, it’s worth learning how to do manually.

Luckily this is really easy (unless you have a particularly large database or your WordPress site shares the database with other programs, in which case follow the custom backup procedure here).

All you need to do is go to the “Export” tab, choose “Quick” as the export method, choose “SQL” as the format, and click the Go button.

The file will download to your computer and you can move it to a safe place for storage.

If you do need to restore from backup, go to the “Import” tab, choose your backup file, and click Go (the default settings should be fine in most cases).

Running SQL Commands in PHP MyAdmin

Clicking the “SQL” tab will take you to a screen with a large text box. This is where you can type SQL commands to query and edit the database.

Useful SQL Commands for WordPress

Now you can see your database and know how to query it, you can start having some fun. There are some very useful things you can do with manual SQL commands that aren’t normally possible without the help of a third-party plugin.

Remember if your tables have a different prefix (they don’t start with “wp_”) you’ll have to edit the table names.

1. Change Your WordPress Password

Forgotten your password and locked yourself out of your WordPress site? No worries – you can reset it manually in the database with this SQL query:

UPDATE wp_users SET user_pass = MD5( '[new_password]' ) WHERE user_login = '[username]';

MD5 encrypts the new password so anyone gaining access to the database is not able to read the password.

2. Transfer Posts to a New Author

If you have multiple authors on a site or take over an existing site from another owner, you might want to transfer existing posts into your username. To do this you can use the following query:

UPDATE
  wp_posts
SET
  post_author = (SELECT ID FROM wp_users WHERE user_login = '[new_author_login]')
WHERE
  post_author = (SELECT ID FROM wp_users WHERE user_login = '[old_author_login]');

3. Bulk Delete Spam Comments

WordPress comes with the Akismet plugin installed as default, which is pretty good at flagging spam. However it’s up to you to regularly check and delete your spam messages.

If you’ve left it a while, you could have thousands of spam messages and it would take ages to delete them all from the dashboard one page at a time. Instead use the following SQL query to delete the lot in one go:

DELETE FROM wp_comments WHERE comment_approved = "spam";

If you have thousands of pending comments and don’t have time to renew them, you can just delete them with the following query (but be aware you may end up deleting some genuine comments):

DELETE FROM wp_comments WHERE comment_approved = "0";

If you don’t want to delete all your comments but have a problem with a spammer who keeps linking back to a certain website, you can use this query to get rid of all comments attributed to a certain URL:

DELETE from wp_comments WHERE comment_author_url LIKE "%spamurl%" ;

4. Update Links When You Move to a New Domain

Links in WordPress are absolute rather than relative, so you’ll have to change them all when you move your site to a new domain. There are plugins that will do this for you or your can use the following SQL query:

UPDATE wp_posts SET post_content = REPLACE (post_content, '[old_domain]', '[new_domain]');

This is basically a find and replace function for all the posts on your site so you may be able to use it for other purposes too.

5. Change Your WordPress Posts into Pages

Sometimes you create a post in WordPress and realize at a later date that it’s a resource that would probably be better as a page. Luckily this is really easy to change:

UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post';

You can also change pages into posts:

UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page';

6. Change your WordPress Username

You can’t change your WordPress username from within the dashboard, which is a bit of a pain. Thankfully it’s straightforward to do with SQL. You should seriously consider doing this if your admin username is “Admin”, to strengthen the security of your site.

UPDATE wp_users SET user_login = 'newusername' WHERE user_login = 'oldusername';

7. Delete Post Revisions

WordPress automatically saves different revisions of your posts as you edit them but these can take up a lot of space in your database. You can delete them with the following query:

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision';

8. Disable Comments on Old Posts

Spammers often target old posts. You can turn off comments on all posts published before a certain date with this query (edit the date to one of your choosing)

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';

9. Change URL of WordPress Images

If you’ve moved your WordPress site, you’ll need to change all your image URLs. You might also decide to host your images elsewhere to save on server space. To change your image URLs use the following query:

UPDATE wp_posts SET post_content = replace(post_content, 'Old URL', 'New URL');

10. Batch Disable Plugins

Most technical issues with your WordPress site can be traced back to a rogue plugin. If you can’t get into your admin panel, try disabling all plugins from your database first and you can then go in and re-enable them one by one.

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

11. Disable Comments on All Posts

Undergoing a spam attack? Or just had enough of other people’s opinions? You can turn off comments on all posts at once with this query:

UPDATE wp_posts SET comment_status = 'closed' where post_type ='post';

Summary

Getting to grips with the WordPress database and understanding how it works can give you more power over your own site and help you to work with your site data directly, avoiding the need to install lots of plugins.

However, messing with your site database can also be dangerous if you don’t know what you’re doing, so it’s vital to always make sure you have a backup before you start making any edits to your database.

In fact, I would recommend setting up a test site with sample data that you can have a play around with to get a better idea of what these different SQL queries do before trying them out on your live site. You can use the Duplicator plugin to create a copy of your site for testing. If you’ve been testing out data on your test site and want to get it to a blank slate again, there’s a plugin you can use to reset the WordPress database to its default state.