Support » Fixing WordPress » 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

    $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.


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.

    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

    $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'): ?>
    		<td><?php echo $user->user_login; ?></td>
    		<td><?php echo get_cimyFieldValue($user->ID,'ACCOUNT_AFFILIATION'); ?></td>
    		<td><?php echo $user->user_email; ?></td>
    	<?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.