7 Best Useful SQL Queries for WordPress

| October 29, 2010

WordPress is the most powerful and widely used blogging platform. WordPress uses MySQL database at backend for its operations and importantly every WordPress user must know this fact before adopting WordPress as a tool for blogging. But, if you have come across this knowledge now, then you must know that MySQL is basically a free RDBMS (rational database management system) used in most web hosting services and so does in WordPress. All of the WordPress data including the posts, comments, categories, and settings are stored within the MySQL database. You can refer to WordPress’s Database Scheme for in-depth knowledge on MySQL, but here are 13 best useful SQL queries for WordPress starters. All the SQL queries provided below can be executed using phpMyAdmin which is one of the best database look-up and editing utility or you can use built-in WordPress SQL Executioner to run your SQL query. You can also learn how to use phpMyAdmin Tool in WordPress.

1- Change Site-Url & Home-Url:

Whenever your attempt to transfer your WordPress website from local host to server the absolute path URL will still point at your local host and thus the website will not load online. Use this SQL query to change the site URL and the home URL.

“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:

In the same scenario as discussed in 1st case, you also required to fix the URLs for the GUID field in wp-posts table. The GUID is used to interpret your post to the right article absolute path.

“UPDATE wp_posts SET guid = REPLACE (guid, ‘http://www.oldsiteurl.com’, ‘http://www.newsiteurl.com’);”

3- Change URL in Content:

Use this query to change all the URLs stored in the database to the new domain.

“UPDATE wp_posts SET post_content = REPLACE (post_content, ‘http://www.oldsiteurl.com’, ‘http://www.newsiteurl.com’);”

4- Change Image Path Only:

You can use this useful query to change the paths of the images to load from Amazon CloudFront.

“UPDATE wp_posts SET post_content = REPLACE (post_content, ‘src=”http://www.oldsiteurl.com’, ‘src=”http://yourcdn.newsiteurl.com’);”

5- Update GUID for Image:

Update the GUID for image attachment by using following SQL query.

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

6- Update Post Meta

If you’ve stored additional URL data for each post, you can use this SQL query to change all of them.

“UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, ‘http://www.oldsiteurl.com’,’http://www.newsiteurl.com’);”

7- Change Default “Admin” Username:

By default a new WordPress installation will make an account with a default Admin username which is very important information. Nevertheless, this could follow a security issue as a hacker can hack your WordPress admin panel. If you are able to change your default “Admin” username, you’ll give your WordPress admin panel an extra security.

“UPDATE wp_users SET user_login = ‘Your New Username’ WHERE user_login = ‘Admin’;”


Category: WordPress

Comments are closed.