WordPress.org

Ready to get started?Download WordPress

Forums

Sorting authors by last name (6 posts)

  1. microkid
    Member
    Posted 4 years ago #

    I'm trying to figure out how to create a complete list of all users, ordered by last name. Since I have around 800 users in the DB, I'm looking for a way to JOIN the wp_users and wp_usermeta tables and hopefully, but I'm not sure how to work this with the meta key/value structure.

    Does anyone know how I could JOIN the two in a way that I can either sort the users in the SQL or in PHP withouth having to do an extra query for each user seperately?

    Thanks in advance!

  2. tugbucket
    Member
    Posted 4 years ago #

    not sure how users get set up in the DB as there is a seperate first/last name feild by default but maybe this can help:

    http://wpengineer.com/list-all-users-in-wordpress/

  3. MichaelH
    Member
    Posted 4 years ago #

    This plugin could help you:
    http://wordpress.org/extend/plugins/members-list/

    But this will kind of get you there:

    <?php
    $lastnames = $wpdb->get_col("SELECT user_id FROM $wpdb->usermeta WHERE $wpdb->usermeta.meta_key = 'last_name' ORDER BY $wpdb->usermeta.meta_value ASC");
      foreach ($lastnames as $userid) {
        $user = get_userdata($userid);
        $post_count = get_usernumposts($user->ID);
        $author_posts_url = get_author_posts_url($user->ID);
        echo '<li><a href="' . $author_posts_url . '">' . $user->user_firstname . ' ' . $user->user_lastname . '</a> (' . $post_count . ') </li>';
      }
    ?>
  4. microkid
    Member
    Posted 4 years ago #

    Thanks for your suggestions.

    @tugbucket
    The function you refer to organises the users by role but doesn't sort them by last name.

    @MichaelH
    This is indeed a solution but I'm trying to avoid the get_userdata function because it means another database query for each individual member. This would make the page very slow. The plugin you refer to has the same problem; I implemented it and it needed over 1300 queries.

    That's why I'm trying to bring everything together into 1 or 2 queries.

  5. MichaelH
    Member
    Posted 4 years ago #

    Not sure how to get you there since multiple pieces of user information are kept in usermeta (e.g. last name, first name, and nickname) so I'm guessing you will need more than one query not matter what. I believe WordPress caches user queries.

    But here's a JOIN you might use:

    $ud = $wpdb->get_results("SELECT * FROM $wpdb->users INNER JOIN $wpdb->usermeta ON ($wpdb->users.ID = $wpdb->usermeta.user_id) WHERE $wpdb->usermeta.meta_key = 'last_name' ORDER BY $wpdb->usermeta.meta_value ASC");
    echo "<pre>"; print_r($ud); echo "</pre>";
  6. Ben Dunkle
    Member
    Posted 4 years ago #

    Can you get authors by wp_category and sort by last_name?
    Like:
    SELECT user_id FROM $wpdb->usermeta WHERE meta_key = 'wp_capabilities' AND meta_value RLIKE $this_role) ORDER BY last_name ASC
    which doesn't work, and neither does
    SELECT user_id FROM $wpdb->usermeta WHERE meta_key = 'wp_capabilities' AND meta_value RLIKE $this_role) ORDER BY $wpdb.usermeta.last_name ASC

Topic Closed

This topic has been closed to new replies.

About this Topic