10 SQL Statements for WordPress

by Anand Singh in Wordpress

As you have noticed we have migrated from blogger to wordpress as wordpress have more flexiblity and features than blogspot. so, we discussed to shift to wordpress.

Now comming to topic, so, i am presenting some SQL commands essential for people using WordPress and want to get the most from your CMS, but remember above all that you should make a full backup of your database and use the commands is at your own risk.

The commands must be run via phpMyAdmin or the tool you use to administer your database. In PhpMyAdmin just select the database and paste the command in the SQL tab, as shown in the figure below.

Note: The SQL commands for WordPress that follow are the default prefix for the WordPress “wp_. If the prefix of your tables is different (which is recommended for security), make the appropriate supplements necessary to queries.

Change Site URL and Home URL

When you set up WordPress is written to an absolute path of the URL for the site (siteurl) and a URL for the homepage, making it difficult to transfer your database to another location, such as a local installation. To resolve this problem you can run a simple SQL command to solve this problem and make your database work in the new address.

UPDATE wp_options
SET option_value = REPLACE(option_value, 'http://www.esoftload.com', 'http://www.esoftload.info')
WHERE option_name = 'home' WHERE option_name = 'home'
OR option_name = 'siteurl'; OR option_name = 'siteurl';

Important: Change the required data in the SQL command so that it works correctly.

Changing GUID

After migrating your blog from, for example, localhost for the server or another domain to a new domain, you will have to fix the URLs to the GUID field in the wp_posts table. This is crucial because the GUID is used to mount the slug from his post of the absolute path of the correct item.

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

Just change the path of images

If you need to change the paths of their images, for whatever reason is possible through the SQL command below.

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'src=”http://www.esoftload.info', 'src=”http://www.esoftload.com');

You will also need to update the GUID for the type “attachment” with the following SQL statement:

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

Change the default user name “admin”

Changing the name of the Admin user is required to increase the security of your WordPress. In version 3.0 of the system we have no more to his presence, but for now you’ll have to do it manually.

UPDATE wp_users
SET user_login = 'cyber world'
WHERE user_login = 'Admin';

This tip also serves to change any other user, just put your username in place of Admin.

Reset password

This tip is for when you lose the password of the user who is administrator of WordPress and could not recover it by I forgot my password.

UPDATE wp_users
SET user_pass = MD5('password')
WHERE user_login = 'login';

Download articles from one author to another

To transfer items from one author to another, you spend a lot of time if you do it manually. With the SQL command below, you can easily do so. For a hint, you need to know the ID of the authors.

UPDATE wp_posts
SET post_author = 'id_isha'
WHERE post_author = 'id_cyber';

Delete review

Every time you edit an article in WordPress and it is saved, the system saves the old revision in the database as backup. This feature is so that if you repent of any changes, have the option to revert to an old revision.

But with time and increasing the quantity of articles, reviews records are enough to occupy space in the database and thus compromise the performance of WordPress. To delete all revisions of articles, give the following SQL command:

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

Export all emails comments

A list with all e-mails from readers who commented on your blog can be achieved through an SQL command. This function is very useful for campaigns via e-mail marketing or some communication is important that one should make pro email to readers.

SELECT DISTINCT comment_author_email
FROM wp_comments;

Once the listing is complete, export it via phpMyAdmin.

Delete all pingbacks

As the site / blog is in the air – and if you have good content to offer – the number of pingbacks begins to influence the quality of the database. To delete all pingbacks, proceed as follows:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Delete all spam comments

Without further explanation, here’s how to delete all the comments marked as SPAM:

DELETE FROM wp_comments
WHERE comment_approved = 'spam';

If you know more SQL command please do share with us.



6 Comments

  1. Blinkky

    Very complicated. But you explain very good =)

  2. Sathish | TechieMania

    Its nice to see you moving from Blogger to WordPress. Actually I have contacted you for this migration before, but I think you forget that. Anyways welcome to the world of WordPress. And thanks for those SQL statements.

    • Isha Singh

      sorry sathish i checked your mail today… well thanks for commenting here… cheer!

  3. TechToom

    thanks for your post , i guess we will surely require it someday

  4. John Peterson

    These are very simple and yet very important sql commands to change the different parameter and thanks for sharing this commands here…

Leave a comment



Copyright © 2014 eSoftload.