Cacher is the code snippet organizer for pro developers

!!.Disable All Plugins

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

!.Change URL after migrate

set @oldurl = 'http://oldwp-url.com', @newurl = 'http://newwp-url.com';

UPDATE wp_options SET option_value = replace(option_value, @oldurl, @newurl) WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = REPLACE (guid, @oldurl, @newurl);
UPDATE wp_posts SET post_content = REPLACE (post_content, @oldurl, @newurl);
UPDATE wp_posts SET post_content = REPLACE (post_content, CONCAT('src="', @oldurl), CONCAT('src="', @newurl));
UPDATE wp_posts SET guid = REPLACE (guid, @oldurl, @newurl) WHERE post_type = 'attachment';
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, @oldurl, @newurl);

1.CHANGE SITEURL & HOMEURL

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


2.CHANGE GUID After migrate

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


3.CHANGE URL IN CONTENT

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


4.CHANGE IMAGE PATH ONLY

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


5.UPDATE POST META

UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldsiteurl.com','http://www.newsiteurl.com');


6.CHANGE DEFAULT "ADMIN" USERNAME

UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';


7.RESET PASSWORD

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


8.ASSIGN ALL ARTICLES BY AUTHOR B TO AUTHOR A

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';


9.DELETE REVISION

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'


10.DELETE POST META

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


11.EXPORT ALL COMMENT EMAILS WITH NO DUPLICATE

SELECT DISTINCT comment_author_email FROM wp_comments;


12.DELETE ALL PINGBACK

DELETE FROM wp_comments WHERE comment_type = 'pingback';


13.DELETE ALL SPAM COMMENTS

DELETE FROM wp_comments WHERE comment_approved = 'spam';


14.IDENTIFY UNUSED TAGS

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;


15.Get all spam IPs

SELECT DISTINCT comment_author_IP FROM wp_comments WHERE comment_approved="spam";


16.TURN ON ALL COMMENTS ON EVERY POST

UPDATE wp_posts SET comment_status='open' WHERE post_status = 'publish' AND post_type = 'post';