· 8 years ago · Mar 16, 2018, 10:00 PM
1Let’s take a tour of the WordPress Database – Watch the Video
2
3A fresh installation of WordPress that’s properly connected to a database, will auto create 12 tables which are like sections of the database that hold specific pieces of your website’s information and data.
4The 12 Tables of the WordPress Database
5
6These are the Default Tables that are created when you install WordPress.
7
8 wp_users
9 wp_usersmeta
10 wp_options
11 wp_posts
12 wp_postmeta
13 wp_terms
14 wp_termmeta
15 wp_term_relationships
16 wp_term_taxonomy
17 wp_comments
18 wp_commentmeta
19 wp_links
20
21But why does my database have more than 12 tables?
22
23When you install plugins, other tables can also be created to house the data needed for the plugin to function.
24wp_users
25
26User management is an important part of WordPress. This table has various sections that hold unique identifying information about the users of your website. There are 5 user roles in a standard installation of WordPress and 6 user roles for a MultiSite Installation of WordPress.
27
28 Super Admin is specific to WordPress Multisite which is a network of WordPress Sites managed by one WordPress installation.
29 Admin is the highest level user role of a standard installation of WordPress. This role needs to be protected at the highest levels since they can do virtually anything on a site.
30 Editor somebody who can publish and manage posts including the posts of other users.
31 Author somebody who can publish and manage their own posts
32 Contributor somebody who can write and manage their own posts but cannot publish them.
33 Subscriber somebody who can only manage their profile.
34
35In the wp_users table you have 10 fields that hold the information of the user.
36
37 ID – unique number assigned to each user.
38 user_login – unique username for the user.
39 user_pass – hash of the user’s password.
40 user_nicename – display name for the user.
41 user_email – email address of the user.
42 user_url – URL of the user, a URL is a website address aka domain name.
43 user_registered – time and date the user registered.
44 user_activation_key – used for resetting passwords.
45 user_status – was used in Multisite pre WordPress 3.0 to indicate a spam user.
46 display_name – the name to be used publicly on the site, can be user_login, user_nicename, first name or last name defined in wp_usermeta.
47
48wp_usermeta
49
50This table stores additional information related to the users. You will see other user profile fields for a user in the dashboard that are stored here.
51
52 umeta_id – unique number assigned to each row of the table.
53 user_id – ID of the related user. (Reference to the wp_users table.)
54 meta_key – an identifying key for the piece of data.
55 meta_value – the actual piece of data.
56
57wp_options
58
59The options table is where all of the websites settings are stored. Examples of things you’ll find here are the theme being used, active plugins, widgets, and temporarily cached data. Other plugins and themes typically store their settings here unless they create separate tables specifically for storing their information.
60
61 option_id – unique number assigned to each row of the table.
62 option_name – an identifying key for the piece of data.
63 option_value – the actual piece of data. The data is often serialized so must be handled carefully.
64 autoload – controls if the option is automatically loaded by the function wp_load_alloptions() (puts options into object cache on each page load).
65
66wp_posts
67
68The posts table is considered one of the most important tables in the WordPress database. It’s an extremely powerful table that stores various types of content including posts, pages, menu items, media attachments and any custom post types that a website uses.
69
70Basically, this table contains rows of content objects with different types.
71
72 ID – unique number assigned to each post.
73 post_author – the user ID who created it. (Reference to the wp_users table.)
74 post_date – time and date of creation.
75 post_date_gmt – GMT time and date of creation. The GMT time and date is stored so there is no dependency on a site’s timezone in the future.
76 post_content – holds all the content for the post, including HTML, links, shortcodes and other content.
77 post_title – title of the post.
78 post_excerpt – a short intro paragraph of the post.
79 post_status – status of the post, e.g. ‘draft’, ‘pending’, ‘private’, ‘publish’.
80 comment_status – if comments are allowed.
81 ping_status – if the post allows ping and trackbacks.
82 post_password – optional password used to view the post.
83 post_name – URL friendly slug of the post title.
84 to_ping – a list of URLs WordPress should send pingbacks to when updated.
85 pinged – a list of URLs WordPress has sent pingbacks to when updated.
86 post_modified – time and date the post was last modified on.
87 post_modified_gmt – GMT time and date the post was last modified on.
88 post_content_filtered – used by plugins to cache a version of the post_content typically passed through the ‘the_content’ filter. Not used by WordPress core itself.
89 post_parent – used to create a relationship between the post and another when the post is a revision, attachment or another type.
90 guid – Global Unique Identifier, the permanent URL to the post, not the permalink version.
91 menu_order – holds the display number for pages and other non-post types.
92 post_type – identifies the type of content it is.
93 post_mime_type – only used for attachments, the MIME type of the uploaded file.
94 comment_count – total number of comments, pingbacks and trackbacks.
95
96wp_postmeta
97
98This table holds additional information about individual posts. It is a vertical table using key/value pairs to store its data. WordPress uses this technique on a number of tables throughout the database allowing the WordPress core, plugins and themes to store virtually an unlimited amount of data.
99
100 meta_id – unique number assigned to each row of the table.
101 post_id – the ID of the post the data relates to. (Reference to the wp_posts table.)
102 meta_key – an identifying key for the piece of data.
103 meta_value – the actual piece of data.
104
105wp_comments
106
107This table stores comments and specific data about them. Additional information can be stored in the wp_commentmeta table.
108
109 comment_ID – unique number assigned to each comment.
110 comment_post_ID – ID of the post this comment relates to. (Reference to the wp_posts table.)
111 comment_author – Name of the comment author.
112 comment_author_email – Email of the comment author.
113 comment_author_url – URL for the comment author.
114 comment_author_IP – IP Address of the comment author.
115 comment_date – Time and data the comment was posted.
116 comment_date_gmt – GMT time and data the comment was posted.
117 comment_content – the actual comment text.
118 comment_karma – not used by the WordPress core, can be used by plugins to help manage comments.
119 comment_approved – if the comment has been approved.
120 comment_agent – where the comment was posted from, eg. browser, operating system etc.
121 comment_type – type of comment: comment, pingback or trackback.
122 comment_parent – refers to another comment when this comment is a reply.
123 user_id – ID of the comment author if they are a registered user on the site. (Reference to the wp_users table.)
124
125wp_commentmeta
126
127This table stores additional information related to a comment.
128
129 meta_id – unique number assigned to each row of the table.
130 comment_id – the ID of the post the data relates to. (Reference to the wp_comments table.)
131 meta_key – an identifying key for the piece of data.
132 meta_value – the actual piece of data.
133
134wp_terms
135
136WordPress allows things like posts and custom post types to be classified in various ways. When creating a post in WordPress, you can add a category and tags to it. Both ‘Category’ and ‘Tag’ are examples of a taxonomy, which is a way to group things together.
137
138 term_id – unique number assigned to each term.
139 name – the name of the term.
140 slug – the URL friendly slug of the name.
141 term_group – ability for themes or plugins to group terms together to use aliases. Not populated by WordPress core itself.
142
143wp_term_taxonomy
144
145Each term is assigned a taxonomy using this table. The structure of this table allows you to use the same term across different taxonomies.
146
147 term_taxonomy_id – unique number assigned to each row of the table.
148 term_id – the ID of the related term. (Reference to the wp_terms table.)
149 taxonomy – the slug of the taxonomy. This can be the built in taxonomies or any taxonomy registered using the register_taxonomy() function.
150 description – description of the term in this taxonomy.
151 parent – ID of a parent term. Used for hierarchical taxonomies like Categories.
152 count – number of post objects assigned the term for this taxonomy.
153
154wp_term_relationships
155
156This post exists in the wp_posts table and when we assign the category and tags through the WordPress dashboard this is the junction table that records that information. Each row defines a relationship between a post in wp_posts table and a term of a taxonomy in wp_term_taxonomy table.
157
158 object_id – the ID of the post object. (Reference to the wp_posts table.)
159 term_taxonomy_id – the ID of the term / taxonomy pair. (Reference to the wp_term_taxonomy table.)
160 term_order – allow ordering of terms for an object, not fully used.
161
162wp_termmeta
163
164Term meta data was introduced in the release of WordPress 4.4. It allows you to save meta values for terms in a similar way to post meta data.
165
166 meta_id – An auto increments ID
167 term_id – ID of the term you want to save this metadata to
168 meta_key – Key name of the metadata. This is how you will reference the data
169 meta_value – The data itself (remember to sanitize.
170
171wp_links
172
173A feature called the Blogroll used to exist in WordPress. It basically was a way for you to manage all links you wanted to have on your website that pointed to other sites. You won’t find a way to administer this table in your dashboard but it still exists for backwards compatibility. You can always use a plugin to manage it.
174
175 link_id – unique number assigned to each row of the table.
176 link_url – URL of the link.
177 link_name – name of the link.
178 link_image – URL of an image related to the link.
179 link_target – the target frame for the link. e.g. _blank, _top, _none.
180 link_description – description of the link.
181 link_visible – control if the link is public or private.
182 link_owner – ID of user who created the link.
183 link_rating – add a rating between 0-10 for the link.
184 link_updated – time and date of link update.
185 link_rel – relationship of link.
186 link_notes – notes about the link.
187 link_rss – RSS address for the link.
188
189Useful Database Code Snippets
190
191Now that we took a general tour of the WordPress Database, let’s look at how we can control our website directly using the database. This is powerful and one of the reasons why you should secure your Web Host User Credentials and all credentials associated with your website from the server, ftp, database etc.
192
193If you want to quickly make some changes or search for information on your website using the database, that can be done using SQL Commands. The code snippets below are examples of just a few of the things you can accomplish without being logged into your website. Hence why it’s so important to secure access to your database.
194Change the Active Theme
195UPDATE wp_options SET option_value = replace(option_value, 'evo-pro', 'devwp');
196UPDATE wp_options SET option_value = replace(option_value, 'devwp', 'evo-pro');
197
198
199Make sure you change the values ‘evo-pro’ and ‘devwp’ to the names of the themes you have.
200Also make sure to use the correct table prefix. In the examples below I use wp_ but yours might be different.
201Adding content to a post
202
203Note: I’m using ‘wordpress’ as the database name. Make sure to swap that out for your database name.
204UPDATE `wordpress`.`wp_posts` SET `post_content` = 'Welcome to WordPress. This is your first post. Edit or delete it, then start writing! <a href="http://localhost/wptest/">http://localhost/wptest/</a>' WHERE `wp_posts`.`ID` = 1;
205
206
207Changing a URL from http to https
208UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://localhost/wptest/', 'https://localhost/wptest/');
209
210Changing a URL from https to http
211UPDATE wp_posts SET post_content = REPLACE (post_content, 'https://localhost/wptest/', 'http://localhost/wptest/');
212Remove Shortcodes that aren’t being used.
213
214First thing you need to do is find out what shortcodes you currently have on your website. Place this code snippet in a new Page Template or temporarily in the header or footer of your theme.
215global $shortcode_tags;
216echo '<pre>';
217print_r($shortcode_tags);
218echo '</pre>';
219
220Then we are going to use the code snippet below as a demonstration. Note: use the shortcode information you get back in the snippet below. Notice it’s for contact form 7.
221UPDATE wp_posts SET post_content = REPLACE(post_content, '[contact-form-7 id=\"48\" title=\"Contact form 1\"]', '' ) ;
222
223Deactivating Active Plugins
224UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';
225
226Inserting another Admin Account to your Website
227Note: I’m using the database name ‘wordpress’. Make sure to use your correct database name. Also, I’m giving the user id the value of 2. Make sure this isn’t already used in your database.
228INSERT INTO `wordpress`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES ('2', 'demo2', MD5('password'), 'John Smith', 'demo@example.com', 'http://www.example.com/', '2011-06-07 00:00:00', '', '0', 'John Smith');
229
230INSERT INTO `wordpress`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '2', 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
231
232INSERT INTO `wordpress`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '2', 'wp_user_level', '10');