2 Useful SQL Queries For WordPress

Once I moved site and I needed to update URLs in new installation as I moved old database too. First I used one plugin, forget it’s name and made complete mess. But when I found this query, everything was smooth. Just open phpmyadmin for your database and open tab SQL. Pay attention on database prefix. For this example we have default “wp” prefix, but your database may have different one, Source for this code is wpbeaches.com.

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

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

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

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');

Another common situationĀ is thatĀ I have to work on site but I don’t have username and password for WordPress admin panel but I have cPanel details. Than I open phpmyadmin and I add new administrator user running this script (source inmotionhosting.com).

INSERT INTO `wp_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`)
VALUES ('newadmin', MD5('pass123'), 'firstname lastname', 'email@example.com', '0');

INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`)
VALUES (NULL, (Select max(id) FROM wp_users), 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');

INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`)
VALUES (NULL, (Select max(id) FROM wp_users), 'wp_user_level', '10');

Image author: 4leafcloverVN

Share on Facebook0Share on Google+1Tweet about this on Twitter