Sort Memberlist by Last Name, First Name

I have the memberlist module working great. I even customized the page to display additional fields. However, the page is currently sorted by username. I would like to have it sorted by Last Name and the First Name. Any ideas?

Thanks,
Kayla M.

Unfortunately, I am not well versed enough to do this. Would you mind taking a look at the memberlist module and let me know what you think needs to be changed?

Code:

<?php

include '../../../config.inc.php';

$product_ids = array();
foreach ($db->get_products_list() as $pr){
    if ($pr['memberslist']) $product_ids[] = $pr['product_id'];
}

$vars = get_input_vars();
if (!$vars['count']) $vars['count'] = 20;

$all_count = $db->users_find_by_product_c($product_ids, 0);
$ul = $db->users_find_by_product($product_ids, 0, $vars['start'], $vars['count']);

//$all_count = $db->get_users_list_c('', '', 0);
//$ul = $db->get_users_list('', '', $vars['start'], $vars['count']);

$t = &new_smarty();
$t->assign('all_count', $all_count);
$t->assign('ul', $ul);

$t->display('templates/memberslist/index.html');

?>

I would also like to sort by last name in the "Browse Users" section of the admin panel. Right now, it sorts by username. So, if I click on any of the letters, it brings up a listing of usernames that begin with that letter whereas I would prefer it to sort by last name. I am thinking that if I can get it to work in that section, I just might be able to get it to work in the directory too.

Kayla

Memberslist Customization Also Wanted

I would also like to have the same customizations made to Memberslist, so that the list of members can be sorted not only by username but also by membership type, first name or last name etc. Has anyone done this yet?

Pat

You can edit /amember/plugins/db/mysql/mysql.inc.php and find this function:

Code:

    function users_find_by_product($product_id, $include_expired, $start=0, $count=-1){
//        settype($product_id, 'integer');
        settype($include_expired, 'integer');
        $limit_exp = $this->get_limit_exp($start, $count);
        $where_exp = ($include_expired) ? '' : ' AND p.expire_date >= NOW() ';
        if (is_array($product_id)) $product_id = join(',', $product_id);
        $q = $this->query($s = "SELECT DISTINCT u.*,
            SUM(if(p.completed, 1, 0)) AS count_of_completed,
            SUM(if(p.completed, p.amount,0)) as summa_of_completed
            FROM {$this->config['prefix']}members u
                LEFT JOIN {$this->config['prefix']}payments p
                ON (p.member_id=u.member_id)
            WHERE p.completed > 0 AND p.product_id IN (-111,$product_id) $where_exp
            GROUP BY u.member_id
            ORDER BY u.login
            $limit_exp
        ");
        $rows = array();
        while ($r = mysql_fetch_assoc($q)){
            if ($r['data'])
                $r['data'] = $this->decode_data($r['data']);
            $rows[] = $r;
        }
        return $rows;
    }

In that function change this line:

Code:

            ORDER BY u.login

To this:

Code:

            ORDER BY u.name_l, u.name_f

Hope this helps!

Ken

Thanks for the fast reply Ken, although I was wondering how to have the ability to sort any of the following fields: 1st name, last name or membership type by simply clicking on the field header within the memberslist html page. I would also have to add the membership field to the html page and I am not sure how to do it.

Thanks,

Patrick

Anyone else have luck with altering /plugins/db/mysql/mysql.inc.php ? I tried the change suggested but it didn't seem to make any difference on my Browse Members screen.

Sorting Member List by Last Name

Actually to sort the Member List you need to change a different section of the amember/plugins/db/mysl/mysql.inc.php

Find this section:

function get_users_list($pattern='%', $status=-1, $start=0, $limit=-1, $aff_id=0 ){
$where = $having = "";
if ($pattern)
$where = " AND u.login LIKE '" . $this->escape($pattern) . "' ";
if (strlen($status)){
$status = intval($status);
if ($status >= 0 && $status < 3)
$where .= " AND status = $status ";
else if($status == 3){
$where .= " AND is_affiliate>0 ";
}

}
if($aff_id ){
$where .= "AND u.aff_id = '$aff_id'";
}


$limit_exp = $this->get_limit_exp($start, $limit);
$q = $this->query($s = "SELECT u.*,
SUM(if(p.completed, 1, 0)) AS count_of_completed,
SUM(if(p.completed, p.amount,0)) as summa_of_completed
FROM {$this->config['prefix']}members u LEFT JOIN {$this->config['prefix']}payments p ON (p.member_id=u.member_id)
WHERE 1 $where
GROUP BY u.member_id
HAVING 1 $having
ORDER BY u.login
$limit_exp
");
$rows = array();
while ($r = mysql_fetch_assoc($q)){
if ($r['data'])
$r['data'] = $this->decode_data($r['data']);
$rows[] = $r;
}
return $rows;
}
-------------------------------------------------
Then, chage the:

ORDER BY u.login
to
ORDER BY u.name_l, u.name_f

Thanks for the suggestions here - they worked when selecting the "All" option to display users - unfortunately, when I select one of the letters it reverts back to showing users by their login id. Any suggestions?

Thanks.