How WordPress user data is stored in the database

Storage and retrieval of user data are one of the most important features of any blogging website. Over the course of years, WordPress has developed its own way of managing the data of its vast number of users. In this article, we will show you how WordPress stores this data in its database.

WordPress stores information about users in its database using a combination of database tables and associated fields. In WordPress, two tables in the database store information about users. The wp_users table stores the basic information of users, whereas the wp_usermeta table is used to store additional information about them.

Two other tables are closely linked to these tables. They are wp_posts and wp_comments, which store the data of each user’s posts and comments, respectively. You can take a quick look at how the WordPress user database tables are organized before proceeding here.

In the figure WP 4.4.2, you can see all the tables mentioned in the last paragraph. You can also see the attributes of each table and how each of them is linked to the WordPress user database. Do not let the symbols confuse you – they simply denote relationships between attributes of those tables.

Now let’s look at the WordPress user database tables in more detail.

Overview of the wp_users table

WordPress uses the wp_users table to store user data in its database. The “wp_users” table in WordPress is a fundamental component of its database structure, serving as the repository for user-related data. Only the most basic user information is stored here. This includes the username (user_login), password (user_pass), and email (user_email) of the users. This table is central to user management, authentication, and authorization processes within the WordPress site.

The following screenshot illustrates the structure of the wp_users table:

Here is also an overview of some of the other user table columns:

In the user_activation_key, when a user sends a password reset request to the website, a key is generated under the name check_password_reset_key, which is then sent in the email. Once they click the link in the email, it is then matched with the key stored in this field.

Now, if the key in the email directly matches this key, the request is considered invalid. The two keys should match only after the key from the email is hashed. This is how WordPress authenticates the password change requests of users.

Here is an example of how information is stored in the wp_users table:

Note that passwords are stored in a hash form in WordPress as shown in the example above to ensure safety.

You can load the data of any user, just by creating an instance of the WP_User class and passing the user ID as first parameter:

$user = new WP_User(1); // 1 is the ID of the user that we are loading
$user->user_email; // admin@example.com
$user->user_nicename; // John Doe

The wp_users table in the WordPress user database is designed to store only the basic information of users. To store additional WordPress user data, the wp_usermeta table is used. It is a database table storing user-specific metadata and custom key-value pairs associated with each registered user on a WordPress site. This metadata is often used for various purposes, including user preferences, user-specific settings, and integration with plugins and themes. The ID field from the users’ table and the user_id field from this table are used to link the record of the same user.

The following fields are present in this table:

The user meta table is often used by plugins to store custom user data. The meta value field does not apply any restrictions to the values that can be stored. This means that you can store any types of values, including regular strings, numbers, dates or even PHP objects in a serialized format.

Here is an example of how information is stored in the wp_usermeta table:

You can manipulate the records stored in this table in two different ways.

If the record is a core WordPress field, there will be most likely a form field which you can find on your profile page to update its value. The second way is to go deeper and do it programmatically. This means that you can use the functions that are already provided by WordPress, which can help you manage the metadata of users on your website (of course, you will need to have permission to do this).

add_user_meta() , update_user_meta() and delete_user_meta() are the functions used to add, update and delete the user metadata respectively.

To retrieve the user meta data, you can use the get_user_meta() function. Here is an example of retrieving the first_name meta data that WordPress stores by default:

$first_name = get_user_meta( 1, 'first_name', true ); // John

Posts from users

Any WordPress website out there is completely driven by its posts. This means the storage and retrieval of each and every user’s post is one of its most important aspects.

The posts from users are stored in wp_posts table. The way each post is linked to the user is by the post_author column, which stores the ID of the user who has created the post.

Here is an example of how information is stored in the wp_posts table:

To load all the posts created by a specific user, you can use the get_posts()  function, by applying the “author” parameter:

$user_posts = get_posts( array( 'author' => 1 ) );

User comments

You, as a blogger, may write numerous posts throughout the course of your lifetime, but you will never get better and grow as a writer if some form of feedback or criticism is not provided to you. Comments are the instruments that allow your readers with an opportunity to communicate with you and provide those much-needed criticisms. Because of this, comments have now become just as important for a blog as anything else.

WordPress stores those comments in the wp_comments table. Comments are linked to both users and posts.

When a registered user leaves a comment, the user ID is stored in the user_id column of the wp_comments table. However, in case the commenter is not a WordPress user, the user id field defaults to 0.

To load all the comments submitted by a specific user, you can use the get_comments()  function. You just need to specify the user ID as the “author__in” parameter.

$user_comments = get_comments( array( 'author__in' => 1 ) );

Searching through the WordPress database

You can use functions like WP_Query or get_posts to search through the WordPress user database tables and use your own filtering criteria. For this, WordPress has provided us with the $wpdb class. Using the $wpdb class can be easy and intuitive, especially if you have experience with SQL languages. The following is a simple example of using the $wpdb class to query the database.

Let’s say that you want to search the data of all your registered users on 24 January 2018. Here is the code to create the query:

global $wpdb;
$users_query = “SELECT * FROM $wpdb->users WHERE user_registered = ‘2018-01-24’ ”;
$users = $wpdb->get_results($users_query);

You can use other wpdb functions like query, get_var, get_row, and get_col to get your desired results as well. A complete description of the wpdb class can be found right here. If you are looking for an easier and less tech-savvy way of searching through the user database, you can also use WordPress plugins like Users Insights to search the database by using the built-in smart filters functionality.

Transform your WordPress user data into actionable insights

Get Users InsightsSee All Features

Conclusion

The WordPress user database is a MySQL-based repository that stores and manages user profiles, credentials, roles, and permissions for user authentication, authorization, and site access control within a WordPress website.

In this article, we learned how WordPress stores the records of its users. We also learned how you, as a user, are linked with the posts and comments that you write in your (or someone else’s) blog. Then we saw a list of the fields used to store all those records and gained an insight into how to manipulate them in WordPress. We saw how the data from the tables can be queried using the $wpdb class or using a WordPress plugin.

We hope you enjoyed this article and see you again next time!