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