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.
WordPress Database Basics
DELETE FROM wp_comments WHERE comment_approved = “spam”;
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.
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.
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.
UPDATE wp_posts SET comment_status = ‘closed’ where post_type =’post’;
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:
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.
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
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.
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 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).