15 SQL queries very useful for WordPress

WordPress works with a MySQL database. WordPress all data such as messages, comments, categories and parameters are stored in the MySQL database.

Discover the diagram WordPress database on codex.wordpress.org.

Save Your Base From WordPress Basic data

In the preamble, before making any changes, remember to back up your data! You can download the plugin WP-DBManager to save your database using WordPress.

Here is the manual backup process via phpMyAdmin:

1. Login to phpMyAdmin
2. Select your WordPress database
3. At the top, click Export
4. Select all the tables
5. Select to export to SQL format. Sql
6. Check "Save as file"
7. Choose the type of gzip
8. Finally, click OK

How To Make A SQL Queries?

The easiest way to execute a query below is to use phpMyAdmin. The SQL Executioner plugin also allows you to run queries directly from the WordPress admin.

How to use phpMyAdmin

1. Connect to the phpMyAdmin interface
2. Select your WordPress database data
3. Click the SQL tab
4. Copy/Paste the applications you want to run

ATTENTION: All queries used in this article assume that your table prefix is "wp_", namely, the standard prefix for the WP tables in the WordPress installation. If you use a different table prefix (and this is recommended in terms of security;), consider changing the query accordingly.

WordPress SQL queries Practical & Useful

Change The Way Of Images

You have decided to use a CDN such as Amazon, after creating the CNAME, use the following query to change the path of your images to the CDN.

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsiteurl.com', 'src="http://yourcdn.newsiteurl.com');

You will also need to change the GUID of attached images:

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://yourcdn.newsiteurl.com') WHERE post_type = 'attachment';

Change The Name Of The User Default "Admin"

Each WordPress default installation creates an account with a user "Admin" default name. This can be a security problem because a hacker can easily force your WordPress admin.

UPDATE wp_users SET user_login = 'Your-New-Name' WHERE user_login = 'Admin';

The Change Password

Need to change or reset your password in WordPress, or even that of other users?

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';

Assign All Items From The author A TO B author

If you want to transfer items from the author A to B the author, it would be time to do it section by section. Use the following SQL query. You must first get the ID of two authors from the user profiles from your WordPress admin panel. Click on authors' names to view their profile. In the address bar, search for "user_id".

UPDATE wp_posts SET post_author = 'new-author-ID' WHERE post_author = 'ancient-author-ID';

Clear The Revisions

Any change or modification in WordPress causing a record in the database, here is an easy way to remove them.

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'

Delete Items From The Metas

When you delete a plugin, some information remains in the database (post_meta_table). Reduce the size of your database with this SQL query:

DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';

Export Emails Some commentators

Get the email addresses of the commentators of your blog:

SELECT DISTINCT comment_author_email FROM wp_comments;

Clear Pingback

Pingback too? Here's how to remove them:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Delete All Comments Spam

You have many classified spam comments? Delete them at once:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

• 0 = Waiting for moderation
• 1 = Approved
• Spam = Marked as spam 

Identify Unused Tags

Here's how to identify unused tags on your WordPress:

SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Disable The Comments To The Old Articles

If you do not want to moderate the comments of your old items, you can disable these comments based on a date, use the following query:

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2012-01-01'

Remove The Illegible Characters

You have character encoding problems? Instead of cleaning your articles and comments one-to-one, here are a series of queries to run to replace them

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, '”', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '“', '“');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '”', '”');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '’', '’');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '‘', '‘');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '—', '–');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '–', '—');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '•', '-');
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '…', '…');

Disable All Your WordPress Plugins

A request to disable all your plugins:

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

Search And Replace From Content In Your Articles

To easily replace content by another, use this SQL query:

UPDATE wp_posts SET 'post_content' = REPLACE ('post_content', 'Old_Text', 'New_Text');

Delete And / Or Replace A Shortcode In Your Articles

WordPress shortcodes that is always handy but when you change the WordPress theme, sometimes they are more compatible. Same SQL query as above, it can remove or replace your obsolete shortcodes.

UPDATE wp_post SET 'post_content' = REPLACE ('post_content', '[OldShortcode]', 'Anything or NewShortcode' ) ;

Share it Please

Tweet