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

DELETE FROM wp_comments WHERE comment_approved = “0”;

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.
phpmyadmin sql

WordPress Database Basics

DELETE FROM wp_comments WHERE comment_approved = “spam”;

phpmyadmin home
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
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:

  • 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.

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

The core WordPress tables are:
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.

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

Once logged in you should see a screen like this:

How to Use SQL Commands With the WordPress Database

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.
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.

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

UPDATE wp_options SET option_value = ” WHERE option_name = ‘active_plugins’;

11. Disable Comments on All Posts

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.
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:
UPDATE wp_posts SET post_content = replace(post_content, ‘Old URL’, ‘New URL’);

10. Batch Disable Plugins

These are the basic WordPress tables but you probably have more that are added by plugins and themes you install.
phpmyadmin wordpress database
UPDATE wp_posts SET comment_status = ‘closed’ where post_type =’post’;

Summary

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:

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

DELETE from wp_comments WHERE comment_author_url LIKE “%spamurl%” ;

4. Update Links When You Move to a New Domain

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.
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:
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:
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.

How to Backup Your WordPress Database in PHPMyAdmin

Every time someone accesses your WordPress site, the content is fetched from the database using a series of SQL queries.
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.
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.
UPDATE wp_posts SET post_type = ‘page’ WHERE post_type = ‘post’;

The file will download to your computer and you can move it to a safe place for storage.
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.

Running SQL Commands in PHP MyAdmin

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.
phpmyadmin database search

Useful SQL Commands for WordPress

UPDATE wp_users SET user_login = ‘newusername’ WHERE user_login = ‘oldusername’;

7. Delete Post Revisions

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:

1. Change Your WordPress Password

Remember if your tables have a different prefix (they don’t start with “wp_”) you’ll have to edit the table names.
UPDATE wp_users SET user_pass = MD5( ‘[new_password]’ ) WHERE user_login = ‘[username]’;

Your WordPress tables may also have a different prefix. “wp_” is the default but it is often changed for security reasons.
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.
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).
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.
UPDATE wp_posts SET post_type = ‘post’ WHERE post_type = ‘page’;

6. Change your WordPress Username

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:
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

Each table in the database has several fields. For example wp_users has:
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).
UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date < ‘2016-01-01’ AND post_status = ‘publish’;

9. Change URL of WordPress Images

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.
Once the search is complete, click the “browse” link and click on results to edit them directly. You can also delete and copy records.
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.
phpmyadmin
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.
phpmyadmin database backup
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.
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).

Posted by WordPress Guru