Wordpress is completely mysql supported software. Unless you have to go to the amdin panel for every change or in case our wordpress site can't be accessed for some reason, we can use various sql queries to get problem fixed. Personally I reguarly use it to deal with spam and article revision. I will post some of the most frequently used queries in case you need it as well.

Where to run mysql query? Definitely from the phpmyadmin. Just login to your cpanel then navigate to database section then click "phpMyadmin". It will be redirected to phpmyadmin page. Now if you have multiple database created make sure to click on the right one from left navigation and it will display a list of functions on the top. We need to click on "SQL" tab and a mysql query window will be displayed. Now type into whatever queries you like and "Run", once it's excecuted succesfully it will show up the results in green words, or else red alarm is expected.

Here're the wordpress mysql queries I used often.

1. Change wordpress user password

UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin' LIMIT 1;

We can also use similar sql to change the default user "admin" to something else for security purpose:

UPDATE wp_users SET user_login = ‘newname’ WHERE user_login = ‘admin’;

2. Mass edit article author

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

3. Delete the revision and related info of article posting

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

4. Remove all unapproved comments

DELETE from wp_comments WHERE comment_approved = '0';

5. Delete the specific user's comment via several terms

(1) Check user's home URL and delete all it's comments:

DELETE FROM wp_comments WHERE comment_author_url LIKE '%www.example.com%';

(2) Check user's nick name and delete all it's comments:

DELETE FROM wp_comments WHERE comment_author = 'example';

(3) Check user's email and delete all it's comments:

DELETE FROM wp_comments WHERE comment_author_email = 'example@example.com';

Sometimes we need to filter some sensitive words or terms to avoid legal problems. It can be done via a simple sql query too. For example we need to replace the word "sex" to ** then we can do the following:

UPDATE wp_comments SET comment_content = REPLACE( comment_content, 'fuck', '**' );

6. Some times we might have post wrong stuff in article, if there're many places to check around, we can use the following for mass replacement.

UPDATE wp_posts SET post_content = replace( post_content, 'string_to_find', 'string_to_replace' ) ;

7. Get a list of all posters' email addresses:

SELECT DISTINCT comment_author_email FROM wp_comments;

8. Disable all wordpress plugins at one time:

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

9. Delete all wordpress tags:

DELETE a,b,cFROM database.prefix_terms AS a LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id LEFT JOIN
database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_idWHERE ( c.taxonomy = 'post_tag' AND c.count = 0 );

10. Disable comment for old posts:

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

11. Replace the comment poster's address:

UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://oldurl.com', 'http://newurl.com' );

12. Replace the comment poster's contact Email:

UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, 'old-email@address.com', 'new-email@address.com' );

13. Delete comments if there's specific term in author's URL:

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

14. Change the article author:

First we need to run following to get the IDs of all authors:

SELECT ID, display_name FROM wp_users;

For example we need to change the article auther ID to NEW_AUTHOR_ID from OLD_AUTHOR_ID:

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

15. Change wordpress website URL:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl.com', 'http://www.newurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Once done, we need to run the following to edit all posts' GUID:

UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl.com','http://www.newurl.com');