Wordpress database overview and useful SQL Queries for Wordpress sites

Wordpress-Database-Overview.md

Wordpress Database

Overview of Wordpress database tables:

• wp_commentmeta: contains information about comments posted on the Wordpress site, it has four fields: meta_id, comment_id, meta_key, and meta_value. Each meta_id is related to a comment_id. Example of meta information would be the status of a comment.
• wp_comments: it contains comment author name, url, email, comment, etc.
• wp_links: used to manage blogrolls
• wp_options: contains most of Wordpress settings such as site url, admin email, default category, posts per page, etc. This table is also used by numerous plugins to store plugins settings.
• wp_postmeta: contains meta information about posts, pages, and custom post types. Examples of meta information would be which template to use to display a page, custom fields, and so on. Some plugins also store plugin data in this table.
• wp_posts: contains all posts, pages, revisions, and custom post types.
• wp_terms: categories and tags are taxonomies, so each category and tag inside them is a term.
• wp_term_relationships: this table manages the relationship of post types with terms in the wp_terms table. It helps determine that post X is in Y category.
• wp_term_taxonomy: helps differentiate which terms is a category, which term is a tag, etc.
• wp_usermeta: contains meta information about Users on your website.
• wp_users: contains user information like username, password, user email, etc.

wordpress-sql-queries.SQL

/*
	Useful WordPress SQL Queries
*/

/*
	Add a Custom Field to All Posts & Pages
	---
	This snippet will add a custom field to every post and page found in your
	WP database. All you have to do is replace the UniversalCutomField to
	whatever Custom Field name you like to create, and then change
	MyCustomFieldValue to the value of your choice.
*/

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyCustomFieldValue' AS meta_value FROM wp_posts
WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField');


/*
	Add a Custom Field to Posts Only
*/

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyCustomFieldValue' AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
'' AND post_type = 'post';


/*
	Add a Custom Field to Pages Only
*/

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyCustomFieldValue' 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 Post Meta
	---
	After you have removed a plugin, the data will remain in the post_meta
	table, which of course, is no longer needed. Remember and change YourMetaKey
	to your own value before running this query.
*/

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';


/*
	Identify Unused Tags
	---
	If you run a query to delete old posts, the old tags will remain.
	This query allows you to identify all of the unused tags.
*/

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;


/*
	Batch Deleting Spam Comments
*/

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';


/*
	Batch Deleting All Unapproved Comments
*/

DELETE FROM wp_comments WHERE comment_approved = 0;

/*
	Batch Deleting All Comments
*/

DELETE FROM wp_comments WHERE comment_approved = '1';

/*
	Disable Comments on Older Posts
	---
	For this query, specify the comment_status as either open,
	closed, or registered_only. Also, specify the date
	by editing the 2016-01-01 to suit your needs.
*/

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';


/*
	Disabling and Enabling Trackbacks & Pingbacks
	---
	For this query, specify the comment_status
	as either open, closed, or registered_only.
*/

# Globally enable pingbacks/trackbacks for all users
UPDATE wp_posts SET ping_status = 'open';

# Globally disable pingbacks/trackbacks for all users
UPDATE wp_posts SET ping_status = 'closed';

# specify the ping_status as either open or closed.
# Also, specify the date by editing the 2016-01-01 to suit your needs.
UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';


/*
	Delete Comments With a Specific URL
	---
	The following query will delete all comments with a particular URL.
*/

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


/*
	Identify & Delete Posts that are over 'X' Days Old
	---
	If you ever need to identify and delete posts
	that are over a certain amount of days old
*/

SELECT * FROM 'wp_posts'
WHERE 'post_type' = 'post'
AND DATEDIFF(NOW(), 'post_date') > X # Replace the X with the number of days


/*
	Removing Unwanted Shortcodes
	---
	Here is a simple SQL query to run on your database to get rid of any
	unwanted shortcodes. Replace unusedshortcodes with your own shortcode name.
*/

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


/*
	Change Your WordPress Posts Into Pages and Vice-Versa
*/

# Posts to pages
UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post';

# Pages to posts
UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page';


/*
	Change Author Attribution On All Posts
	---
	The first thing you will need to do for this snippet
	is retrieve the IDs of the WordPress author.
*/

# Retrieve the author ID
SELECT ID, display_name FROM wp_users;

# Replace NEW_AUTHOR_ID with new authors ID and OLD_AUTHOR_ID with the old
UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;


/*
	Batch Deleting Post Revisions
	---
	Post revisions can be very useful, but they also considerably
	increase the size of your MySQL database. You could manually delete posts
	revisions, but a much quicker method would be to use this SQL query.
*/

DELETE FROM wp_posts WHERE post_type = "revision";


/*
	Disable or Enable All WordPress Plugins
*/

UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';


/*
	Changing the Destination URL of a WordPress Site
*/

# Tell Wordpress of your new address
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';

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

# Search through the content of your posts
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-site.com', 'http://www.new-site.com');


/*
	Change the Default 'Admin' Username
	---
	This will give your WordPress admin panel additional security
*/

UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin';


/*
	Manually Reset your WordPress Password
*/

UPDATE 'wordpress'.'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'wp_users'.'user_login' ='admin' LIMIT 1;


/*
	Search and Replace Post Content
	---
	Replace OriginalText with the current text
	and replace NewText with your new text.
*/

UPDATE wp_posts SET 'post_content'
= REPLACE ('post_content',
'OriginalText',
'NewText');


/*
	Changing the URL of Images
	---
	To change the paths of your images use this SQL command
*/

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'src="http://www.myoldurl.com"', 'src="http://www.mynewurl.com"');