Left Join Users and Usermeta
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.
account_statusis not a field (row) in
usermetaso 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.
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 ";
- The topic ‘Left Join Users and Usermeta’ is closed to new replies.