Tour of the WordPress Database

The WordPress Database is an integral part of the software that powers over 28% of the modern web. The database that’s used is MySQL. You can also use MariaDB.

If you’ve never directly interacted with a MySQL Database, think of it like Microsoft Excel, just significantly more powerful and capable of scaling to the point of powering websites as popular as facebook.

MySQL is the world’s most popular open source database. With its proven performance, reliability and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more. Source: MySQL About Page

MySQL is the Database that works in conjunction with PHP which is also the most widely used web coding language for websites both small and large.

When you first install WordPress, you get the WordPress Core which is comprised of the files and folders that contain what at first seem as cryptic code, that makes the software work. You also need to connect that software with the database.

The process is easy, especially when a hosting service provides the one click installation feature. It’s because of this that most people never directly interact with the actual database and just start creating their website.

This is fine since WordPress is a mature Content Management System which is well maintained but there’s value in learning the basics of how the database works and taking a look under the data hood.

Let’s take a tour of the WordPress Database – Watch the Video

A 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.

The 12 Tables of the WordPress Database

These are the Default Tables that are created when you install WordPress.

• wp_users
• wp_usersmeta
• wp_options
• wp_posts
• wp_postmeta
• wp_terms
• wp_termmeta
• wp_term_relationships
• wp_term_taxonomy
• wp_comments
• wp_commentmeta
• wp_links

But why does my database have more than 12 tables?

When you install plugins, other tables can also be created to house the data needed for the plugin to function.

wp_users

User 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.

• Super Admin is specific to WordPress Multisite which is a network of WordPress Sites managed by one WordPress installation.
• 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.
• Editor  somebody who can publish and manage posts including the posts of other users.
• Author somebody who can publish and manage their own posts
• Contributor somebody who can write and manage their own posts but cannot publish them.
• Subscriber somebody who can only manage their profile.

In the wp_users table you have 10 fields that hold the information of the user.

• ID – unique number assigned to each user.
• user_login – unique username for the user.
• user_pass – hash of the user’s password.
• user_nicename – display name for the user.
• user_email – email address of the user.
• user_url – URL of the user, a URL is a website address aka domain name.
• user_registered – time and date the user registered.
• user_activation_key – used for resetting passwords.
• user_status – was used in Multisite pre WordPress 3.0 to indicate a spam user.
• 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.

wp_usermeta

This 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.

• umeta_id – unique number assigned to each row of the table.
• user_id – ID of the related user. (Reference to the wp_users table.)
• meta_key – an identifying key for the piece of data.
• meta_value – the actual piece of data.

wp_options

The 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.

• option_id – unique number assigned to each row of the table.
• option_name – an identifying key for the piece of data.
• option_value – the actual piece of data. The data is often serialized so must be handled carefully.
• autoload – controls if the option is automatically loaded by the function wp_load_alloptions() (puts options into object cache on each page load).

wp_posts

The 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.

Basically, this table contains rows of content objects with different types.

• ID – unique number assigned to each post.
• post_author – the user ID who created it. (Reference to the wp_users table.)
• post_date – time and date of creation.
• 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.
• post_content – holds all the content for the post, including HTML, links, shortcodes and other content.
• post_title – title of the post.
• post_excerpt – a short intro paragraph of the post.
• post_status – status of the post, e.g. ‘draft’, ‘pending’, ‘private’, ‘publish’.
• comment_status – if comments are allowed.
• ping_status – if the post allows ping and trackbacks.
• post_password – optional password used to view the post.
• post_name – URL friendly slug of the post title.
• to_ping – a list of URLs WordPress should send pingbacks to when updated.
• pinged – a list of URLs WordPress has sent pingbacks to when updated.
• post_modified – time and date the post was last modified on.
• post_modified_gmt – GMT time and date the post was last modified on.
• 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.
• post_parent – used to create a relationship between the post and another when the post is a revision, attachment or another type.
• guid – Global Unique Identifier, the permanent URL to the post, not the permalink version.
• menu_order – holds the display number for pages and other non-post types.
• post_type – identifies the type of content it is.
• post_mime_type – only used for attachments, the MIME type of the uploaded file.
• comment_count – total number of comments, pingbacks and trackbacks.

wp_postmeta

This 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.

• meta_id – unique number assigned to each row of the table.
• post_id – the ID of the post the data relates to. (Reference to the wp_posts table.)
• meta_key – an identifying key for the piece of data.
• meta_value – the actual piece of data.

wp_comments

This table stores comments and specific data about them. Additional information can be stored in the wp_commentmeta table.

• comment_ID – unique number assigned to each comment.
• comment_post_ID – ID of the post this comment relates to. (Reference to the wp_posts table.)
• comment_author – Name of the comment author.
• comment_author_email – Email of the comment author.
• comment_author_url – URL for the comment author.
• comment_author_IP – IP Address of the comment author.
• comment_date – Time and data the comment was posted.
• comment_date_gmt – GMT time and data the comment was posted.
• comment_content – the actual comment text.
• comment_karma – not used by the WordPress core, can be used by plugins to help manage comments.
• comment_approved – if the comment has been approved.
• comment_agent – where the comment was posted from, eg. browser, operating system etc.
• comment_type – type of comment: comment, pingback or trackback.
• comment_parent – refers to another comment when this comment is a reply.
  user_id – ID of the comment author if they are a registered user on the site. (Reference to the wp_users table.)

wp_commentmeta

This table stores additional information related to a comment.

• meta_id – unique number assigned to each row of the table.
• comment_id – the ID of the post the data relates to. (Reference to the wp_comments table.)
• meta_key – an identifying key for the piece of data.
• meta_value – the actual piece of data.

wp_terms

WordPress 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.

• term_id – unique number assigned to each term.
• name – the name of the term.
• slug – the URL friendly slug of the name.
• term_group – ability for themes or plugins to group terms together to use aliases. Not populated by WordPress core itself.

wp_term_taxonomy

Each term is assigned a taxonomy using this table. The structure of this table allows you to use the same term across different taxonomies.

• term_taxonomy_id – unique number assigned to each row of the table.
• term_id – the ID of the related term. (Reference to the wp_terms table.)
• taxonomy – the slug of the taxonomy. This can be the built in taxonomies or any taxonomy registered using the register_taxonomy() function.
• description – description of the term in this taxonomy.
• parent – ID of a parent term. Used for hierarchical taxonomies like Categories.
• count – number of post objects assigned the term for this taxonomy.

wp_term_relationships

This 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.

• object_id – the ID of the post object. (Reference to the wp_posts table.)
• term_taxonomy_id – the ID of the term / taxonomy pair. (Reference to the wp_term_taxonomy table.)
• term_order – allow ordering of terms for an object, not fully used.

wp_termmeta

Term 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.

• meta_id – An auto increments ID
• term_id – ID of the term you want to save this metadata to
• meta_key – Key name of the metadata. This is how you will reference the data
• meta_value – The data itself (remember to sanitize.

wp_links

A 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.

1. link_id – unique number assigned to each row of the table.
2. link_url – URL of the link.
3. link_name – name of the link.
4. link_image – URL of an image related to the link.
5. link_target – the target frame for the link. e.g. _blank, _top, _none.
6. link_description – description of the link.
7. link_visible – control if the link is public or private.
8. link_owner – ID of user who created the link.
9. link_rating – add a rating between 0-10 for the link.
10. link_updated – time and date of link update.
11. link_rel – relationship of link.
12. link_notes – notes about the link.
13. link_rss – RSS address for the link.

Useful Database Code Snippets

Now 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.

If 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.

Change the Active Theme

UPDATE wp_options SET option_value = replace(option_value, 'evo-pro', 'devwp');
UPDATE wp_options SET option_value = replace(option_value, 'devwp', 'evo-pro');

Make sure you change the values ‘evo-pro’ and ‘devwp’ to the names of the themes you have.
Also make sure to use the correct table prefix. In the examples below I use wp_ but yours might be different.

Adding content to a post

Note: I’m using ‘wordpress’ as the database name. Make sure to swap that out for your database name.

UPDATE `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;

Changing a URL from http to https

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://localhost/wptest/', 'https://localhost/wptest/');

Changing a URL from https to http

UPDATE wp_posts SET post_content = REPLACE (post_content, 'https://localhost/wptest/', 'http://localhost/wptest/');

Remove Shortcodes that aren’t being used.

First 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.

global $shortcode_tags; 
echo '<pre>'; 
print_r($shortcode_tags); 
echo '</pre>';

Then 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.

UPDATE wp_posts SET post_content = REPLACE(post_content, '[contact-form-7 id=\"48\" title=\"Contact form 1\"]', '' ) ;

Deactivating Active Plugins

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

Inserting another Admin Account to your Website

Note: 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.

INSERT 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');

INSERT 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";}');

INSERT INTO `wordpress`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '2', 'wp_user_level', '10');

Final Thoughts

As you can see, the WordPress Database is extremely powerful. You can effectively administer your website via the database by running SQL Commands and Queries but you also have to secure it as well since it can be a serious point of attack.
Hopefully you found this article helpful, feel free to share your thoughts and checkout the video that goes with it.

Share This On: