Wordpress users and usermeta - joining multiple rows in one table to one row in another table
Solution 1
I found that Toote's solution works but is a performance hog on a MySQL database taking about 17 seconds to execute a "select * from users_with_meta_view".
I got this down to 0.0054 sec with a view structured like this instead:
CREATE OR REPLACE VIEW users_with_meta_view AS
SELECT
u.id,
u.user_login AS login,
u.user_pass AS password,
u.user_email AS email,
(select meta_value from wp_usermeta where user_id = u.id and meta_key = 'first_name' limit 1) as first_name,
(select meta_value from wp_usermeta where user_id = u.id and meta_key = 'last_name' limit 1) as last_name,
(select meta_value from wp_usermeta where user_id = u.id and meta_key = 'country' limit 1) as country
FROM wp_users u
Solution 2
As far as I know, you are doing it the right way and just need to put them all together:
SELECT
u1.id,
u1.login,
u1.password,
u1.email,
m1.meta_value AS firstname,
m2.meta_value AS lastname,
m3.meta_value AS country
FROM wp_users u1
JOIN wp_usermeta m1 ON (m1.user_id = u1.id AND m1.meta_key = 'first_name')
JOIN wp_usermeta m2 ON (m2.user_id = u1.id AND m2.meta_key = 'last_name')
JOIN wp_usermeta m3 ON (m3.user_id = u1.id AND m3.meta_key = 'country')
WHERE
-- CONDITIONS ON the user you want to select based any field
Solution 3
$sql= "SELECT
u1.parent_id,
m1.meta_value AS headline,
m2.meta_value AS profilephoto
FROM wp_vandor u1
JOIN wp_usermeta m1 ON (m1.user_id = u1.child_id AND m1.meta_key = 'headline')
JOIN wp_usermeta m2 ON (m2.user_id = u1.child_id AND m2.meta_key = 'profilephoto')
WHERE m1.user_id != $currentuser_id";
$classifieds = $wpdb->get_results($sql);
foreach ($classifieds as $classified) { ?>
<p><?php echo $classified->profilephoto; ?></p>
<h3><?php echo $classified->headline; ?></h3>
<?php } ?>
frumbert
Web engineer, UX designer, front-end programmer, database administrator and optimiser, e-learning administration and consulting. I do browser compatibility issues, jquery, html, javascript, php and wordpress stuff. I have done classic asp and asp.net web (vb, c#), some winforms, lots of vb6 back in the day; have since moved on.
Updated on June 19, 2022Comments
-
frumbert almost 2 years
I want to create a view from both the wp_users and wp_usermeta tables so that I can query rows in the view from an external application. Basic auth details are stored in wp_users (e.g. username, password, email, id) and other fields are stored in key/value pairs in the wp_usermeta table.
the wp_users table is structured like this:
id | login | password | email ----------------------------- 1 | bsmith| abc123 | [email protected] 2 | jjones| def456 | [email protected]
the wp_usermeta table is structured like this:
id | user_id | meta_key | meta_value ------------------------------------ 1 | 1 | firstname| bob 2 | 1 | lastname | smith 3 | 1 | country | denmark 4 | 2 | firstname| jan 5 | 2 | lastname | jones 6 | 2 | country | germany
I want to end up with data like this as a MYSQL VIEW:
id | login | password | email | firstname | lastname | country ----------------------------------------------------------------- 1 | bsmith| abc123 | [email protected]| bob | smith | denmark 2 | jjones| def456 | [email protected]| jan | jones | germany
I know I need to do at least an inner join between wp_users and wp_usermeta, however I also need to perform a sub query inside wp_usermeta to join those rows together.
The query I have for getting the user row is of course dead easy:
select u1.id, u1.login, u1.password, u1.email from wp_users u1
The query I have for getting the meta as a row this is:
select m1.user_id, m1.meta_value as firstname, m2.meta_value as lastname, m3.meta_value as country from wp_usermeta m1 join wp_usermeta m2 on (m1.user_id = m2.user_id and m2.meta_key = 'last_name') join wp_usermeta m3 on (m2.user_id = m3.user_id and m3.meta_key = 'country') where m1.meta_key = 'first_name'
So how do I join these two queries together - u1's data and the row where u1.id = m1.userid?
-
frumbert almost 12 yearsgar, you're right of course i was seeing the complicated solution! cheers
-
Jake over 11 yearsThis was very helpful. For other users, if you are doing it within wordpress (I don't think the OP was), you would want to replace wp_usermeta and wp_users with $wpdb->usermeta & $wpdb->users respectively. Just FYI for posterity. Pretty useful answer.
-
Scorpius over 10 yearsThis really saved me a ton of time!
-
Vinith about 9 yearssorry Toote stackoverflow doesn't allow me morethan one upvote for your answer.. This is old but powerful query.. Thanks
-
MANJEET almost 9 yearsthanks its worked for me.. before it my query was taking approx 65 sec to executes now its executing in 3 seconds