TL;DR: Here’s how you can use SQL to find WordPress users by metadata. This is useful when you have information such as their first name and last name and want to retrieve the full
WordPress provides a number of functions that make it easy to retrieve a user based on certain information. One of the most popular functions (or most useful, maybe?) is
However, what if you’re working on a system that maintains the user’s first name and last name or some other type of data point in the
usermeta table and you want to use that to retrieve the user’s information?
Find WordPress Users by Metadata
First, let’s assume that you want to retrieve the user information based on their first name and last name as it’s stored in the
usermeta table. To do that, the following SQL can do the job:
SELECT ID, first_meta.meta_value, last_meta.meta_value FROM wp_users AS users LEFT JOIN wp_usermeta AS first_meta ON users.ID = first_meta.user_id LEFT JOIN wp_usermeta AS last_meta ON users.ID = last_meta.user_id WHERE first_meta.meta_key = 'first_name' AND first_meta.meta_value = 'tom' AND last_meta.meta_key = 'last_name' AND last_meta.meta_value = 'mcfarlin'
This will then return a row of information that contains my user ID and the meta values that contain my first and last name.
Second, if you want to generalize this to retrieve information based on the meta data, the basic structure for the query would look something like this:
SELECT ID, metadata.meta_value FROM wp_users AS users LEFT JOIN wp_usermeta AS metadata ON users.ID = metadata.user_id WHERE first_meta.meta_key = 'your_meta_key' AND first_meta.meta_value = 'your_meta_value'
Obviously, you’ll need to customize that values are actually part of your
SELECT clause and you’ll need to determine just how many meta keys you’re going to need, but using the above as a basic template is a good start.
I’m Not a Database Administrator
This is nothing more than a short tip for how to look up a user based on a couple of pieces of metadata.
If you end up keying off of a lot of information, it’s likely better to find a more performant query than continuing to join the same tables to together over and over.
I’m not a database administrator, don’t claim to be, and think that if you know of a better way to do something, then please do so (or if you know someone who’s able to to tackle something you need, don’t hesitate to ask them).