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 statements 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.
SET option_value = REPLACE(option_value, 'http://www.esoftload.com', 'https://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.
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.
SET guid = REPLACE (guid, 'https://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.
SET post_content = REPLACE (post_content, 'src=”https://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:
SET guid = REPLACE (guid, 'https://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.
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.
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.
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.
SET post_author = 'id_isha'
WHERE post_author = 'id_cyber';
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
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.