Blog

Data Basics – An intro to the WordPress database and MySQL

One of the biggest strengths of WordPress as a content management system is its simple and robust database structure. All of the users, posts, and comments on your WordPress site are stored in a database powered by a popular query language called MySQL, and nearly every page on your WordPress dashboard interacts with the database in some way. While you don’t need to know MySQL in order to handle the basics of maintaining your WordPress site, a simple understanding of it can be invaluable in understanding how your web developer diagnoses issues and makes large-scale changes to your site.  It’s best to leave these kinds of things to a skilled web development team (like us!), but it’s helpful background information to have when communicating with your web developer on an issue with your site.

Getting connected

In order to connect to your MySQL database, you’ll need a MySQL database tool. A database tool allows you to run queries and show the results in ordered columns, making working with the results much easier. If your WordPress site is hosted with cPanel, then you already have one of these. It’s called phpMyAdmin, and it’s a MySQL database tool that comes built in with cPanel and allows you to run queries directly from your browser. To access phpMyAdmin, log into your cPanel and click the phpMyAdmin link in the Databases section. Once you’re in phpMyAdmin, you can find your server on the left side of the screen. Click the plus sign button to expand it, then select the database that corresponds to your WordPress site. (If you don’t know which database this is, it can be found> in wp-config.php, a base WordPress file located in your public_html folder. The DB_NAME defined in this file indicates the name of the database your WordPress site uses.)

If you don’t use cPanel, or if you simply don’t want to use phpMyAdmin, there are other tools available. A good free database tool to use is MySQL Workbench. To log into your database with one of these tools, you’ll need your username and password, which can be found in wp-config.php under the names DB_USER and DB_PASSWORD respectively.

The ten tables of WordPress

Once you’ve logged into your WordPress database with the tool of your choice, you’ll be able to access almost all of the data stored in your site. By default, WordPress uses ten tables to store all of the users, posts, comments, categories, and configuration options for your site. It’s worth understanding how all of these tables are laid out, so let’s go over all of them.

  • wp_users

    The wp_users table contains information on all of the user accounts on your site. Each user has a unique ID that’s used to identify that user in other tables. The rest of the columns in the table are self-explanatory, containing the user’s login ID, email address, password, and so forth. However, it should be noted that the user’s password is stored in an encrypted state and shouldn’t be modified in the database directly.

  • wp_usermeta

    The wp_usermeta table contains all the user meta information on your site. Meta information is information that’s associated with specific users, posts, etc., but won’t fit in their respective tables for various reasons. Each line in the table has a unique umeta_id, as well as a user_id field indicating the ID of the user it’s associated with. Each line also has a meta_key serving as a label for what type of content it contains, as well as a meta_value containing the content itself. Meta fields are used extensively by plugins, but several user options are stored in the wp_usermeta table by default, including first and last names, nicknames, descriptions, and user capabilities.

  • wp_posts

    The wp_posts table contains information on all of the posts on your site. This includes posts, pages, revisions, uploaded media, and any custom post types from plugins you’ve installed. Each post has a unique ID which is used to identify it in other tables, as well as a post_author indicating the ID of the user who created it. The rest of the data is self-explanatory, containing the post title, content, posting date, and so forth.

  • wp_postmeta

    The wp_postmeta table contains all the post meta information on your site. The way this works is similar to the wp_usermeta table, with each line having a unique meta_id, a post_id indicating the ID of the post it’s associated with, and an identical meta_key and meta_value structure. The wp_postmeta table is mostly used by plugins, but some base functionality uses the table as well, such as featured images for posts, which are stored under the meta_key “_thumbnail_id” with the meta_value indicating the ID of the thumbnail post.

  • wp_comments

    The wp_comments table contains information on all of the comments on your site. Each comment has a unique comment_ID, as well as a comment_post_ID indicating the ID of the post on which the comment was made. The other columns contain the rest of the information about the comment, including the content, the poster, the date it was made, and so forth.

  • wp_commentmeta

    The wp_commentmeta table contains all the comment meta information on your site. This works just like the other meta tables, but with a comment_id indicating the comment_ID of the comment with which the meta is associated.

  • wp_terms

    The wp_terms table contains information on all of the terms on your site. Terms are various sets of identifiers used to organize posts on your site. By default, this only includes post categories and post tags, but plugins can add other kinds of terms to your site. Each term has a unique term_id, as well as a name and a slug (a name using a restricted set of URL-friendly characters). The last column, term_group, isn’t used by default and can usually be safely ignored.

  • wp_term_taxonomy

    The wp_term_taxonomy table contains taxonomies for all of the terms in your site. Each taxonomy has a unique term_taxonomy_id, as well as a term_id indicating the term it describes. The taxonomy contains further information on a term, including the type of term it is in the taxonomy column, as well as its description. The parent column contains the term_id of the term’s parent, if it has one, and the count column contains a count of how many posts use the term.

  • wp_term_relationships

    The wp_term_relationships table contains a list of all relations between posts and terms on your site. Each line contains simply an object_id indicating the ID of the post that has a term and a term_taxonomy_id of the term which it uses. Like wp_terms, it has another column term_order which isn’t used by default.

  • wp_options

    The wp_options table contains all of the option configuration settings on your site. This is used extensively by plugins, and also used for many configuration settings by default. The wp_options table functions somewhat like a meta table for your entire site, with each line having a unique option_id as well as an option_name and an option_value. The last column, autoload, is used by WordPress to determine which options should be loaded on certain pages and generally shouldn’t be modified.


Putting it all together

Now that we know how the data is structured in our WordPress database, how can we put it to use? Well, we can use this data to run queries on any set of data from our site. Queries are sentences of MySQL code that we can use to select certain subsets of data from a MySQL database. In order to run a query, you’ll need to have a query prompt open. To open the query prompt in phpMyAdmin, just click on the “SQL” tab in the main window after you’ve selected your database. To execute a query, just enter it in the text box and click “Go”.

NOTE: You should always be careful when running queries and should not attempt any of the following unless you are an experienced web developer.  A poorly formatted query could potentially cause unwanted changes to your data. The queries in this article only select data for viewing purposes and don’t modify anything, but other queries that insert, edit, or delete data can cause issues if written incorrectly.

So just what can we do with queries? Well, for an example, say we wanted to select all the posts by our admin Jeff, who has a user_id of 4, that are drafts that have yet to be published. To find these, we could just enter the following query:

SELECT * FROM wp_posts WHERE post_author = 4 AND post_status = ‘draft’

Or if we want a list of all users who haven’t entered a user URL in their profile. We could run the following query to find them:

SELECT * FROM wp_users WHERE user_url = ”

These are all very elementary operations, but there are more advanced operations that can be done. For example, say we wanted to get all posts created by Jeff, who still has a user_id of 4 that are in the “Entertainment” category, which has a term_taxonomy_id of 17. This information isn’t all in one table, but we can use a database operation to essentially put it all in one table for the purposes of our query:

SELECT * FROM wp_posts
INNER JOIN wp_term_relationships
ON wp_posts.ID = wp_term_relationships.object_id
WHERE wp_posts.post_author = 4 AND wp_term_relationships.term_taxonomy_id = 17

This creates a list of all the data from wp_posts and wp_term_relationships where the ID and object_id match, and selects all the rows where the post_author is 4 and the term_taxonomy_id is 17.

As you can see, MySQL gives us a lot of power for gathering and organizing data from our database, but this is just a small fraction of what can be done with it. To learn more about MySQL, you can try reading the MySQL Reference Manual, or look into the Database Description and related articles from the WordPress Codex.

Leave a reply