Left Join Users and Usermeta

  • Hey

    I’m trying to output a list of all users (with preference “visible”) for my site in a table with a few columns, but I need to draw information from both the users and usermeta table.

    I am using this foreach

    <?php
$order = '$wpdb->users.user_nicename';
$users = $wpdb->get_results(
	"SELECT * FROM $wpdb->users
	LEFT JOIN $wpdb->usermeta ON($wpdb->wp_users.ID = $wpdb->usermeta.umeta_id)
        WHERE $wpdb->usermeta.account_status = 'Visible'
	ORDER BY $order");
foreach($users as $user) :
?>

    But nothing returns.

    Can anyone help or help me perhaps make use of wp_list_authors instead.

    Thanks

Viewing 3 replies - 1 through 3 (of 3 total)

  • account_status is not a field (row) in usermeta so please restate what you are trying to accomplish.

    If you are using a plugin that adds field to usermeta, a link to that plugin would be useful.

    Thread Starter boon_

    (@boon_)

    I am using the cimy user extra fields plugin and I have set up a field called ACCOUNT_STATUS with a drop down box of “Visible” or “Invisible” for user profile status. I am only trying to list users who have the preference “Visible”.

    I’ve just realised I can accomplish what I’ve been trying using this

    <?php
$order = 'user_nicename';
$users = $wpdb->get_results("SELECT * FROM $wpdb->users ORDER BY $order");
foreach($users as $user) :
?>
	<?php if (get_cimyFieldValue($user->ID,'ACCOUNT_STATUS')=='Visible'): ?>
	<tr>
		<td><?php echo $user->user_login; ?></td>
		<td><?php echo get_cimyFieldValue($user->ID,'ACCOUNT_AFFILIATION'); ?></td>
		<td><?php echo $user->user_email; ?></td>
	</tr>
	<?php endif ;?>
<?php endforeach; ?>

    But the query here is still pulling out ALL users, then checking them. I was trying to streamline it with a combined query so that it doesn’t pull out needless entries. So that if there are say 1000 users, load time isn’t compromised.

    Thanks a lot

    How about this for a select statement:

    $querystr = "
    SELECT wpusers.*
    FROM $wpdb->users wpusers, $wpdb->usermeta wpusermeta
    WHERE wpusers.ID = wpusermeta.user_id
    AND wpusermeta.meta_key = 'account_status'
    AND wpusermeta.meta_value = 'Visible'
    ORDER BY wpusers.user_nicename ASC
 ";

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Left Join Users and Usermeta’ is closed to new replies.