Practical WordPressSQL query method

This code adds a custom field to all articles and pages in the WordPress database. What you need to do is to replace 'universalcutomfield' in the code with the text you need, and then change 'myvalue' to the desired value.

Add custom fields for all articles and pages

This code adds a custom field to all articles and pages in the WordPress database. What you need to do is to replace 'universalcutomfield' in the code with the text you need, and then change 'myvalue' to the desired value.

1 insert into wp_postmeta (post_id, meta_key, meta_value) select id as post_id, 'universalmmfield 'AS meta_key' MyValue AS meta_value FROM wp_postsWHERE id not in (SELECT post_id FROM wp_postmeta WHERE meta_key = 'universalcustomfield ')

If you only need to add a custom field for the article, you can use the following code:

1 insert into wp_postmeta (post_id, meta_key, meta_value) select id as post_id, 'universalmmfield '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 only need to add custom fields to the page, you can use the following code:

1 insert into wp_postmeta (post_id, meta_key, meta_value) select id as post_id, 'universalmmfield '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 delete a plug-in, the system uses the meta tag to store data. After the plug-in is deleted, the data will remain in the post_meta table. of course, you no longer need the data and can delete it. Remember to replace 'yourmetakey 'in the code with the desired value before running the query.

1 delete from wp_postmeta WHERE meta_key = 'yourmetakey ';

Search for useless tags

If you perform a query in the WordPress database to delete the old article, the tag of the article will remain in the database and appear in the tag list/tag cloud as before when you delete the plug-in. The following query can help you find useless tags.

1 SELECT * From wp_terms wtINNER JOIN wp_term_taxonomy wtt ON wt. term_id = wtt. term_idWHERE wtt. taxonomy = 'post _ tag' AND wtt. count = 0;

Batch delete spam comments

Run the following SQL command:

1 delete from wp_comments WHERE wp_comments.comment_approved = 'spam ';

Batch delete all unreviewed comments

This SQL query will delete all unreviewed comments on your website and will not affect the reviewed comments.

1 delete from wp_comments WHERE comment_approved = 0

Forbid comment on earlier articles

The value of comment_status is open, closed, or registered_only. You also need to set the date (in the code ):

1 UPDATE wp_posts SET comment_status = 'closed 'WHERE post_date <'2017-01-01' ANDpost_status = 'Publish ';

Disable/activate trackback and pingback

The value of comment_status is open, closed, or registered_only.

Activate pingbacks/trackbacks to all users:

1 UPDATE wp_posts SET ping_status = 'open ';

Disable pingbacks/trackbacks for all users:

1 UPDATE wp_posts SET ping_status = 'closed ';

Activate/disable Pingbacks & Trackbacks before a certain date

The value of ping_status is open, closed, or registered_only. You also need to set the date (in the code ):

1 UPDATE wp_posts SET ping_status = 'closed 'WHERE post_date <'2017-01-01' ANDpost_status = 'Publish ';

Delete a comment from a specific URL

When you find that many spam comments have the same URL link, you can use the following query to delete these comments at one time. % Indicates that all URLs containing strings in the "%" symbol will be deleted.

1 DELETE from wp_comments WHERE comment_author_url LIKE "% nastyspamurl % ";

Do not delete the article "X" days ago

Search for all the articles from "X" days ago (replace X with the corresponding value ):

1 SELECT * FROM 'WP _ posts' WHERE 'post _ type' = 'post' and datediff (NOW (), 'post _ date')> X

Delete all articles from "X" days ago:

1 delete from 'WP _ posts' WHERE 'post _ type' = 'post' and datediff (NOW (), 'post _ date')> X

 

Delete unnecessary short code

When you decide not to use short code, they will not disappear automatically. You can use a simple SQL query command to delete all unneeded short code. Replace "tweet" with the corresponding short code name:

1 UPDATE wp_post SET post_content = replace (post_content, '[tweet]', '');

Convert an article to a page

You can still run an SQL query using PHPMyAdmin:

1 UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'

Convert a page to an article

1 UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'

 

Change Author attributes of all articles

First, use the following SQL command to retrieve the author's ID:

1 select id, display_name FROM wp_users;

After obtaining the new and old IDs of the Author, insert the following command, remember to replace NEW_AUTHOR_ID with the new author ID, and replace OLD_AUTHOR_ID with the old Author ID.

1 UPDATE wp_posts SET post_author = NEW_AUTHOR_ID WHERE post_author = OLD_AUTHOR_ID;

 

Batch delete Document Revision history

Saving revision history can be very practical and annoying. You can manually delete the revision history or use SQL queries to save time.

1 delete from wp_posts WHERE post_type = "revision ";

Disable/activate all WordPress plug-ins

After activating a plug-in, you cannot log on to the WordPress management panel. try the following query command. it immediately disables all plug-ins and allows you to log on again.

1 UPDATE wp_options SET option_value = 'A: 0 :{} 'WHERE option_name = 'active _ ins ';

Change the target URL of a WordPress website

After you move a WordPress blog (Template File, uploaded content & database) from one server to another, you need to tell WordPress your new blog address.

When using the following command, note that you replace the http://www.old-site.com with your original URL, and the http://www.new-site.com with a new URL address. First:

1
2
3 UPDATE wp_options
SET option_value = replace (option_value, 'http: // www.old-site.com ', 'http: // www.new-site.com ')
WHERE option_name = 'home' OR option_name = 'siteurl ';

Then, use the following command to change the URL in wp_posts:

1 UPDATE wp_posts SET guid = replace (guid, 'http: // www.old-site.com ', 'http: // www.new-site.com );

Finally, search for the article content to ensure that the new URL link is not mixed with the original link:

1
2 UPDATE wp_posts
SET post_content = replace (post_content, 'http://www.hiuff.com ', 'http://www.nouveau-site.com ');

 

Change the default user name Admin

Replace YourNewUsername with the new user name.

1 UPDATE wp_users SET user_login = 'yourusername' WHERE user_login = 'admin ';

Manually reset the WordPress password

If you are the only author on your WordPress website and you have not modified the default username, you can reset the PASSWORD using the following SQL query (replace the PASSWORD with the new PASSWORD ):

1
2 UPDATE 'wordpress '. 'WP _ users' SET 'user _ pass' = MD5 ('password ')
WHERE 'WP _ users'. 'user _ login' = 'admin' LIMIT 1;

Search and replace the document content

Replace OriginalText with Replaced content, and ReplacedText with the target content:

1 UPDATE wp_posts SET 'post _ content' = REPLACE ('post _ content', 'originaltext', 'replace text ');

Change Image URL

The following SQL command can help you modify the image path:

1 UPDATE wp_postsSET post_content = REPLACE (post_content, 'src = "http: // www. ***. com ', 'src =" http://www.huiff.com ');