By Holidaymaine


2011-04-23 05:11:07 8 Comments

I have a site with many users and many of them have the same last name. I want to get the emails of all the users with the same last name (IE: Smith) that has a post related to a particular taxonomy term (IE: Baseball).

So far I have this code that works great in getting all the users with the same last name ( thanks to Mike Schinkel ). I suck at using the JOIN function but I am learning and I really need this sooner than later, so I need help.

$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); 

Your time is greatly appreciated and I will pay it forward. Thanks.

1 comments

@MikeSchinkel 2011-04-23 07:42:31

Hi @Holidaymaine:

Here's the query you are looking for:

<?php

include( '../wp-load.php' );

$sql =<<<SQL
SELECT DISTINCT
  u.user_email AS user_email,
  um.meta_value AS user_lastname
FROM
  {$wpdb->users} AS u
  LEFT JOIN {$wpdb->usermeta} AS um ON u.ID = um.user_id
  LEFT JOIN {$wpdb->posts} AS p ON u.ID = p.post_author
  LEFT JOIN {$wpdb->term_relationships} AS tr ON p.ID = tr.object_id
  LEFT JOIN {$wpdb->term_taxonomy} AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
  LEFT JOIN {$wpdb->terms} AS t ON tt.term_id = t.term_id
WHERE 1=1
  AND u.user_status = '0'
  AND um.meta_key = 'last_name'
  AND um.meta_value = '%s'
  AND t.slug = '%s'
SQL;
  $sql = $wpdb->prepare( $sql, 'Smith', 'baseball' );
  $usersemails = $wpdb->get_results( $sql );
  header( 'Content-type:text/plain' );
  print_r( $usersemails );

Related Questions

Sponsored Content

2 Answered Questions

WP_User_Query not returning users with meta - what am I missing?

1 Answered Questions

Pull MySQL data from multiple tables and merge into 1 PHP array

  • 2018-05-23 16:30:53
  • Marek Ojciec Dyrektor Malinows
  • 272 View
  • 0 Score
  • 1 Answer
  • Tags:   php mysql sql

1 Answered Questions

Modify Database in Multi-Site wp_usermeta table

1 Answered Questions

[SOLVED] List of users with email and role

  • 2014-11-12 15:52:56
  • Rizzo
  • 480 View
  • 0 Score
  • 1 Answer
  • Tags:   user-roles users

3 Answered Questions

[SOLVED] List users by last name in WP_User_Query

1 Answered Questions

[SOLVED] $wpdb - joining shows no result

  • 2013-10-08 16:41:33
  • Mayeenul Islam
  • 621 View
  • 0 Score
  • 1 Answer
  • Tags:   wpdb join-tables

1 Answered Questions

[SOLVED] Select User by Joining Multiple Meta Value Results

1 Answered Questions

[SOLVED] trying to list users & display first - last name

  • 2011-09-22 06:58:37
  • ingvi
  • 4579 View
  • 1 Score
  • 1 Answer
  • Tags:   users

3 Answered Questions

[SOLVED] LEFT JOIN, INNER OUTER JOIN, LEFT OUTER JOIN is driving me crazy. Please help?

  • 2011-04-22 10:03:39
  • Holidaymaine
  • 3293 View
  • 3 Score
  • 3 Answer
  • Tags:   users wpdb user-meta

Sponsored Content