The WordPress Development Company have made a superb platform for developing the websites very easily and quickly with the help of the advanced features that it provides. The best part is that no coding knowledge is required to develop the website. It is possible to develop the website for our personal or professional purpose from scratch without having any programming knowledge with the help of this platform.
The themes developed by the WordPress Development Company make the website look professional and attractive. Different varieties of code are used for the development of the WordPress website and one can also manually change them. HTML and CSS are used to change the view of the website. PHP and JavaScript are used for dynamic programming and SQL is used for fetching the data from the various databases of the website.
Basics of WordPress Database
The database of the website stores the data of the website along with the information of the user.
Every time any user accesses your site, the data is retrieved from the WordPress database in the form of queries and served to them. The tables are used to store the data in the WordPress database. The core tables of the WordPress Development sites are as follows:
● wp_posts – it contains the data of media attachments, pages and posts
● wp_postmeta – it contains the metadata of every post
● wp_comments – the comments on the pages and posts including the date, author and email will be included here.
● wp_commentmeta – it will contain the metadata for the comments
● wp_users- all the usernames, passwords, and other data will be stored here
● wp_usermeta- the metadata for the users will be stored here.
● wp_options – general settings of WordPress will be stored here
● wp_links –Today, it is not used on the sites but it was used for blogroll links.
● wp_terms – This table will store the post categories
● wp_termmeta – it stores the metadata for tags and categories.
● wp_term_relationships – this table helps to connect the posts with tags and categories.
The themes and plugins that you install on the site will already add these WordPress tables. The prefix of the WordPress tables might be different. The default prefix is the “wp_” but for security purpose, it is changed sometimes.
Important SQL Commands for WordPress Database
The WordPress site contains the code written in PHP and the PHP code contains the SQL commands that are helpful to us for communicating with the WordPress database. MySQL is the dbms of WordPress and it is the most effective and easiest way of retrieving the data from the database. There is no graphical interface for MySQL and it has to be operated via the console. There are different graphical interface available for MySQL. The widely used one among all is the phpMyAdmin and it can be accessed from the admin panel.
You have to enter the credentials to enter into phpMyAdmin and it will show you the databases on the left side. The below given are some useful commands that can be really helpful for your WordPress site so have a look at them.
1. Use this SQL query for changing your WordPress Password
If you have forgotten the password of your website then you can modify it with the following SQL query.
UPDATE wp_users SET user_pass = MD5( ‘[new_password]’ ) WHERE user_login = ‘[username]’;
Here, MD5 is used for encryption so the attacker trying to enter the database would find it difficult to crack the password of the website.
2. For Transferring the Posts to the New Author
Suppose, you want to purchase an old site from the previous owner then you might actually need to transfer the existing posts of the site into your own username and for doing this the following SQL query can be used.
UPDATE wp_posts SET post_author = (SELECT ID FROM wp_users WHERE user_login = ‘[new_author_login]’) WHERE post_author = (SELECT ID FROM wp_users WHERE user_login = ‘[old_author_login]’);
3. Delete the Bulk Spam Comments
There is a plugin named Akismet that is very good at removing the bad spam messages but if you want to do it on your own then you can try the following query to remove all the spam messages in one trial.
DELETE FROM wp_comments WHERE comment_approved = “spam”;
Sometimes, you will be having a lot of comments that are pending and you want to delete them just because you don’t want it to get renewed then you can do so with this query.
DELETE FROM wp_comments WHERE comment_approved = “0”;
Suppose, you are not wishing to remove all the comments but you are having some trouble with the spammer who is posting the links to a particular website then you can get away from all those comments with the following query.
DELETE from wp_comments WHERE comment_author_url LIKE “%spamurl%” ;
4. Link Update When You Transfer to a Fresh, New Domain
The links of the website are absolute and not relative so they need to be changed when you transfer the site to completely fresh and new domain. The variety of plugins are available for helping you or you can try the following query.
UPDATE wp_posts SET post_content = REPLACE (post_content, ‘[old_domain]’, ‘[new_domain]’);
5. Change the Posts Of WordPress into Pages
At times, in your WordPress site you make a post and after that, you realize that it good in the form of a page then you can change it with the help of the following SQL query.
UPDATE wp_posts SET post_type = ‘page’ WHERE post_type = ‘post’;
The vice-versa is also possible:
UPDATE wp_posts SET post_type = ‘post’ WHERE post_type = ‘page’;
6. Query for changing the Username of WordPress
The SQL query can be used to change your username of WordPress also. By default “Admin” is the username, so try to change it immediately for securing the website.
UPDATE wp_users SET user_login = ‘new username’ WHERE user_login = ‘old username’;
7. Delete the Post Revisions from the Database
A huge space is taken in the database as it stores the revision of the posts so it is wise to delete them all.
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’;
8. Comments should be disabled on the Futile Old Posts
Try to disable the comments on the old posts with the help of this query otherwise the spammers may try to target your old posts.
UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date < ‘2016-01-01’ AND post_status = ‘publish’;
9. Alter the URLs of Images
At time when you move your site then all the URLs of the images need to be changed and this query can help you with this task.
UPDATE wp_posts SET post_content = replace(post_content, ‘Old URL’, ‘New URL’);
10. Disable the Plugins in Batch Process
Sometimes it may happen that you are not able to enter the admin panel. If this is the case then try to disable all the plugins from the database and then one after another enable them.
UPDATE wp_options SET option_value = ” WHERE option_name = ‘active_plugins’;
Thus, these were the 10 SQL queries that can help you a lot in your WordPress Development project so keep them handy while working with the WP site.
