Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

fellow coders! I an having a hard time JOIN(ing) tables.

I have a a lot of users on my site and many of them have the same last names. I am trying to get their emails by their last name. WP stores emails in the users table and user names in the usermeta and I have been trying to use JOIN to get what I want, but I don't understand how it works and I am about to give up for the day. This is what i have after 4.5 hours.

$usersemails = $wpdb->get_results("SELECT users.user_email, usermeta.meta_value 
FROM $wpdb->users
LEFT JOIN $wpdb->usermeta ON $wpdb->users.ID = $wpdb->usermeta.user_id 
    WHERE user_status = '0' 
    AND meta_value = 'Smith'");

Any help is appreciated, so can you please help? Thanks.

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

Hi @Holidaymaine:

Not sure where you are doing wrong, but try the following instead which is a self contained test.php file you can drop into the root of your website and load in your browser with http://yoursite.com/test.php (assuming you replace yoursite.com with your site's domain! Also, note how I adding in a WHERE {$wpdb->usermeta}.meta_key = 'last_name' to ensure you did not get records you were not expecting):

<?php
/*
Filename: test.php
*/

include('wp-load.php');

$sql =<<<SQL
SELECT
  {$wpdb->users}.user_email,
  {$wpdb->usermeta}.meta_value
FROM
  {$wpdb->users}
  LEFT JOIN {$wpdb->usermeta} ON {$wpdb->users}.ID = {$wpdb->usermeta}.user_id
WHERE 1=1
  AND {$wpdb->users}.user_status = '0'
  AND {$wpdb->usermeta}.meta_key = 'last_name'
  AND {$wpdb->usermeta}.meta_value = 'Smith'
SQL;
  $usersemails = $wpdb->get_results($sql);
  header('Content-type:text/plain');
  print_r($usersemails);
share|improve this answer
    
Thank you Mike! This worked perfectly. I will have to compare it with what I was doing to learn a more about JOIN. Thank you for your help. –  Holidaymaine Apr 22 '11 at 12:34
    
@Holidaymaine - Don't forget to select a best answer; mine, or one of the others. We work for reputation here! :) –  MikeSchinkel Apr 22 '11 at 17:35
    
Still learning how this site works :) Can you help me join another table? I just realized I need to know if their post is related to a taxonomy term, so I can get the email if their posts have the term in it. –  Holidaymaine Apr 22 '11 at 21:19
    
@Holidaymaine - Actually, for new questions the correct protocol for the site is to ask another question. –  MikeSchinkel Apr 23 '11 at 3:21
    
Thanks I did not know that, I will read the FAQ again. I did and here is the link if you would like to help. wordpress.stackexchange.com/questions/15434/… –  Holidaymaine Apr 23 '11 at 5:16
add comment

You can do this via API, user search got improved in 3.1

$search = new WP_User_Query(array(
             'meta_value' => 'Lastname',
             'fields' => array('user_email'),
                ));

var_dump($search->get_results());
share|improve this answer
    
This did not work, but thanks for letting me know about the improved search. I will be looking into it for future use. Thanks. –  Holidaymaine Apr 22 '11 at 12:33
    
I didn't vote this up. –  Holidaymaine Apr 22 '11 at 12:35
    
@Holidaymaine I tested the snippet so it should work... What exactly not worked for you? Also don't forget to change Lastname to actual last name you are searching for. –  Rarst Apr 22 '11 at 12:35
1  
I have to apologize Rarst, after trying a few WP_User_Query myself I realized it wasn't your code, but another code before it that was causing the problem. It was returning an array with everything about the users except the emails and user IDs. Thank you for letting me know about this function and your code does work. I have no idea how to remove my previous comment. –  Holidaymaine Apr 23 '11 at 3:42
add comment

SELECT users.user_email, usermeta.meta_value

Should be:

SELECT $wpdb->users.user_email, $wpdb->usermeta.meta_value

Prefixes matter.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.