I am working in MYSQL and need to extract user data to be pulled into a view. I will be using the data in an email client, so I cannot do this in the app layer.
The problem is that each data field for the user is contained in separate rows (this is how Wordpress sets up the data structure).
For example, wp_usermeta has multiple rows of data for each user like this:
user_id meta_key meta_data
2 first_name Jane
2 last_name Austin
2 email [email protected]
3 first_name Jack
3 last_name Palmer
3 email [email protected]
I need the data to be combined into one row, separate fields, like this:
user_id first_name last_name email
2 Jane Austin [email protected]
3 Paul Parker [email protected]
I have searched around and cannot find this exact problem anywhere (I found a lot of concatenation, but that is not what I need).
If these are the only columns you are concerned with, this will work for you:
SELECT um.user_id
, fn.meta_data AS first_name
, ln.meta_data AS last_name
, e.meta_data AS email
FROM wp_userMeta AS um
LEFT JOIN wp_user_Meta AS fn ON um.user_id = fn.user_id
AND fn.meta_key = 'first_name'
LEFT JOIN wp_user_Meta AS ln ON um.user_id = ln.user_id
AND ln.meta_key = 'last_name'
LEFT JOIN wp_user_Meta AS e ON um.user_id = e.user_id
AND e.meta_key = 'email'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With