If you have a 10+ SQL Query WordPress, I regret to say that you have a lot of work to do in organizing and periodic maintenance. And in some cases, you may have to be quick for specific problems that you encounter. In such cases, working directly on the database can really save lives. In this article you can see 10 + handy SQL query for WordPress. These quick and practical queries will certainly work for you when your WordPress is in a difficult state.
SQL query how to…
Let me briefly speak for those who do not know yet. SQL queries run in the MySQL command-line interpreter. Or it can be executed using a popular web interface, such as phpMyAdmin, which most of us use. I strongly recommend that you use the user-friendly interface and the SQL Executionner wordpress plugin, which allows direct interpretation of SQL queries.
Before queries…
Even though these SQL queries have been tested, don’t forget to make a backup of the database before you perform any queries.
Manually Changing your password
It could happen to you someday. The only solution if you forget your admin password is to create a new password in MySQL database. This is the only task of this interrogation. I would also like to draw your attention to the use of the MD5 function for password conversion only.
1 | UPDATE ‘wp_users’ SET ‘user_pass’ = MD5(‘PASSWORD’) WHERE ‘user_login’ =‘admin’ LIMIT 1; |
Transferring articles from one user to another
New WordPress users may not know or forget to write their articles with the name “admin” in terms of security. If you have made this error, you can transfer your old posts to the user you just created with this query. When using this query, you will need the old and new user name.
1 | UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID; |
Delete Update information in posts
Update information can sometimes be useful. Especially in multi-user blogs. However, these update information is more than the records in the database appears. For example There are 100 pieces of articles on your blog and each one has been updated by editing it 10 times. All these transactions mean that the wp_posts table in the database has full 1000 records. However, it is enough to record 100 pieces.
This query helps to instantly delete the post update information and related text that we are talking about. It’s a very useful query to remove and relieve bulge in the database.
1 | DELETE a,b,c FROM wp_posts a WHERE a.post_type = ‘revision’ LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id); |
Deleting Spam comments in bulk
No need to say much, actually, the headline tells everything. It allows you to bulk delete spam comments accumulated in the checklist.
1 | DELETE from wp_comments WHERE comment_approved = ‘0’; |
Find unused tags
tags are saved in the wp_terms table of the database. And sometimes there may be tags that have not been used since the day it was created. If you do not want these unused tags to occupy more space in the database, you can safely delete them with this query.
1 | SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy=‘post_tag’ AND wtt.count=0; |
List unused text text
The meta information of the posts you write on your blog is created through plug-ins and custom fields. Most of the time, it causes the database to grow birdenire. You can list the meta information that occupies the database even though it is not used with this query.
1 | SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL; |
Remove comments from old posts
For more than 1 year, the blog owner will know that the comments accumulated beneath the old posts are often unreadable, so they can take up unnecessary space. In the following query, you can instantly delete comments for posts before the date you specify by typing the date you want instead of 2009-01-01 . I’m guessing most people would be useful.
1 | UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date < ‘2009-01-01’ AND post_status = ‘publish’; |
Change the Site links of reviewers
I mentioned how the replace () function in MySQL worked. The following query definitely proves it. In particular, it may be useful to change the URL addresses written for backlink.
1 | UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, ‘http://oldurl.com’, ‘http://newurl.com’ ); |
Change the Email addresses of the reviewers
With this query, you can change the email addresses of the people who wrote the comments.
1 | UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, ‘old-email@address.com’, ‘new-email@address.com’ ); |
Delete comments found in Spam Link
Finally, a SQL query that is useful for you to avoid spam Links added in the form of comments to your blog. The “% wpbeginner%” section in the query means deleting all comments with the URL address between the “%” sign.
1 | DELETE from wp_comments WHERE comment_author_url = “%wpbeginner%” ; |
