However, if you have hundreds of articles on your WordPress site, and you need to make a full-site change, it is time-consuming and laborious to edit from the background, and the odds of making a mistake will improve. The best way is to go to the WordPress MySQL database to perform the necessary queries (changes). The above tasks can be done quickly with MySQL, saving you more time.
Here are some of the saving time and effort of the WordPress SQL Query method.
Pre-backup
The WordPress database stores all your carefully published articles, all comments from your readers, and all of your personalized settings for your site. So, no matter how confident you are with yourself, keep in mind that you must back up your WordPress database beforehand. You can back up by backing up the plugin.
Add a custom field for all articles and pages
This code can add a custom field to all the articles and pages in the WordPress database. All you need to do is replace the ' Universalcutomfield ' in the code with the text you need, and then change the ' myvalue ' to the desired value.
Copy CodeThe code is as follows:
INSERT into Wp_postmeta (post_id, Meta_key, Meta_value)
SELECT ID as post_id, ' Universalcustomfield '
As Meta_key ' myvalue as Meta_value from Wp_postswhere ID not in (SELECT post_id from Wp_postmeta WHERE meta_key = ' Univers Alcustomfield ');
If you just need to add a custom field to your story, you can use this code:
Copy CodeThe code is as follows:
INSERT into Wp_postmeta (post_id, Meta_key, Meta_value)
SELECT ID as post_id, ' Universalcustomfield '
As Meta_key ' myvalue as Meta_value
From Wp_posts WHERE ID not in
(SELECT post_id from Wp_postmeta WHERE meta_key = ' Universalcustomfield ') ' and Post_type = ' post ';
If you just need to add a custom field to a page, you can use this code:
Copy CodeThe code is as follows:
INSERT into Wp_postmeta (post_id, Meta_key, Meta_value)
SELECT ID as post_id, ' Universalcustomfield '
As Meta_key ' myvalue as Meta_value
From Wp_posts WHERE ID not in
(SELECT post_id from Wp_postmeta WHERE meta_key = ' Universalcustomfield ') and ' post_type ' = ' page ';
Delete Article Meta data
When you install or remove a plugin, the system stores the data via the article Meta tag. After the plugin is deleted, the data will remain in the Post_meta table, of course, you no longer need the data, you can delete it completely. Remember to replace the ' Yourmetakey ' in your code with the corresponding value you need before running the query.
Copy CodeThe code is as follows:
DELETE from Wp_postmeta WHERE meta_key = ' Yourmetakey ';
Find useless tags
If you execute a query in the WordPress database to delete old articles, as in the previous removal of the plugin, the article belongs to the label will remain in the database, and will also appear in the tag list/tag cloud. The following query can help you find useless tags.
Copy CodeThe code is as follows:
SELECT * from wp_terms wtinner JOIN wp_term_taxonomy wtt on wt.term_id=wtt.term_id WHERE wtt.taxonomy= ' Post_tag ' and wtt.c ount=0;
Bulk Delete spam comments
Execute the following SQL command:
Copy CodeThe code is as follows:
DELETE from wp_comments WHERE wp_comments.comment_approved = ' spam ';
Bulk Delete all non-approved comments
This SQL query will delete all the non-audited comments on your site without affecting the reviewed comments.
Copy CodeThe code is as follows:
DELETE from wp_comments WHERE comment_approved = 0
Prohibit comments on earlier articles
Specifies that the value of Comment_status is open, closed, or registered_only.
You will also need to set the date (2010-01-01 in the modified code):
Copy CodeThe code is as follows:
UPDATE wp_posts SET comment_status = ' closed ' WHERE post_date < ' 2010-01-01 ' and post_status = ' publish ';
Deactivate/activate Trackback and Pingback
Specifies that the value of Comment_status is open, closed, or registered_only.
To activate Pingbacks/trackbacks to all users:
Copy CodeThe code is as follows:
UPDATE wp_posts SET ping_status = ' open ';
To disable pingbacks/trackbacks for all users:
Copy CodeThe code is as follows:
UPDATE wp_posts SET ping_status = ' closed ';
Activate/deactivate Pingbacks & Trackbacks before a certain date
Specifies that the value of Ping_status is open, closed, or registered_only.
You will also need to set the date (2010-01-01 in the modified code):
Copy CodeThe code is as follows:
UPDATE wp_posts SET ping_status = ' closed ' WHERE post_date < ' 2010-01-01 ' and post_status = ' publish ';
Delete a comment for a specific URL
When you find a lot of spam comments with the same URL link, you can use the following query to delete these comments at once. % indicates that all URLs that contain strings within the "%" symbol will be deleted.
Copy CodeThe code is as follows:
DELETE from wp_comments WHERE comment_author_url like "%nastyspamurl%";
Identify and delete articles that were "X" days ago
Look for all the articles "X" days ago (note that X is replaced with the corresponding value):
Copy CodeThe code is as follows:
SELECT * from ' wp_posts ' WHERE ' post_type ' = ' post ' and DATEDIFF (now (), ' post_date ') > X
Delete all articles before the "X" Day:
Copy CodeThe code is as follows:
DELETE from ' wp_posts ' WHERE ' post_type ' = ' post ' and DATEDIFF (now (), ' post_date ') > X
Remove unwanted Short Code
When you decide to stop using short code, they don't automatically disappear. You can use a simple SQL query command to delete all the unnecessary short code. Replace "tweet" with the corresponding short code name:
Copy CodeThe code is as follows:
UPDATE wp_post SET post_content = replace (post_content, ' [tweet] ', ');
Convert an article to a page
Still, just run a SQL query through phpMyAdmin to get it done:
Copy CodeThe code is as follows:
UPDATE wp_posts SET post_type = ' page ' WHERE post_type = ' post '
Convert a page into an article:
Copy CodeThe code is as follows:
UPDATE wp_posts SET post_type = ' Post ' WHERE post_type = ' page '
Change author properties on all articles
First, retrieve the author's ID by using the following SQL command:
Copy CodeThe code is as follows:
SELECT ID, display_name from Wp_users;
After successfully obtaining the new and old ID for the author, insert the following command, remembering to replace new_author_id with the new author ID, replacing the old author ID with old_author_id.
Copy CodeThe code is as follows:
UPDATE wp_posts SET post_author=new_author_id WHERE post_author=old_author_id;
Bulk Delete Article revision history
The article Revision history preservation can be very practical, also can be very annoying. You can delete the revision history manually, or you can save time by using SQL queries.
Copy CodeThe code is as follows:
DELETE from wp_posts WHERE post_type = "revision";
Deactivate/activate all WordPress plugins
After activating a plugin found unable to login to the WordPress admin panel, try the following query command bar, it will immediately disable all plugins, let you log back in.
Copy CodeThe code is as follows:
UPDATE wp_options SET option_value = ' a:0:{} ' WHERE option_name = ' active_plugins ';
Change the destination URL of a WordPress site
After you move the WordPress blog (template file, upload content & database) from one server to another, you need to tell WordPress your new blog address.
When using the following commands, be careful to replace http://www.old-site.com with your original url,http://blog.doucube.com for the new URL address.
First of all:
Copy CodeThe code is as follows:
UPDATE wp_options
SET option_value = replace (option_value, ' http://www.old-site.com ', ' http://blog.doucube.com ')
WHERE option_name = ' home ' OR option_name = ' SiteURL ';
Then use the following command to change the URL in wp_posts:
Copy CodeThe code is as follows:
UPDATE wp_posts SET GUID = replace (GUID, ' http://www.old-site.com ', ' http://blog.doucube.com);
Finally, search for the article content to make sure that the new URL link is not confused with the original link:
Copy CodeThe code is as follows:
UPDATE wp_posts SET post_content = replace (post_content, ' http://www.ancien-site.com ', ' http://blog.doucube.com ');
Change the default user name admin
Replace the Yournewusername with the new username.
Copy CodeThe code is as follows:
UPDATE wp_users SET user_login = ' yournewusername ' WHERE user_login = ' Admin ';
Manually reset the wordpress password
If you are the only author on your WordPress site and you have not modified your default username, you can use the following SQL query to reset your password (replace the password with a new one):
Copy CodeThe code is as follows:
UPDATE ' WordPress '. ' Wp_users ' SET ' user_pass ' = MD5 (' PASSWORD ')
WHERE ' wp_users '. ' User_login ' = ' admin ' LIMIT 1;
Search and replace article content
Originaltext replaced content, Replacedtext to target content:
Copy CodeThe code is as follows:
UPDATE wp_posts SET ' post_content ' = REPLACE (' post_content ', ' originaltext ', ' replacedtext ');
Change Picture URL
The following SQL command can help you modify the image path:
Copy CodeThe code is as follows:
UPDATE Wp_postsset post_content = REPLACE (post_content, ' src= ' http://www.myoldurl.com ', ' src= ' http://blog.doucube.com ');
http://www.bkjia.com/PHPjc/326378.html www.bkjia.com true http://www.bkjia.com/PHPjc/326378.html techarticle but suppose your WordPress site has hundreds of articles, and you need to make a full-site change, then from the background to edit a bit time-consuming laborious, and the odds of making mistakes ...
