Sort Memberlist by Last Name, First Name

Discussion in 'Customization & add-ons' started by littlefishweb, Sep 22, 2009.

  1. littlefishweb

    littlefishweb New Member

    Joined:
    Jul 24, 2009
    Messages:
    4
    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.
  2. skippybosco

    skippybosco CGI-Central Partner Staff Member

    Joined:
    Aug 22, 2006
    Messages:
    2,526
    Have not seen the memberlist module, but I would suspect you would want to adjust one of two things..

    1) The SQL Query to include the sort by Last Name then First Name

    -or-

    2) The HTML Template to do the same sorting

    My preference would be to do it as part of SQL since it is a much less expensive transaction at that point.
  3. littlefishweb

    littlefishweb New Member

    Joined:
    Jul 24, 2009
    Messages:
    4
    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');
    
    ?>
  4. littlefishweb

    littlefishweb New Member

    Joined:
    Jul 24, 2009
    Messages:
    4
    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
  5. PKtimservice

    PKtimservice New Member

    Joined:
    Sep 16, 2007
    Messages:
    6
    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
  6. kengary

    kengary aMember Pro Customer

    Joined:
    Nov 13, 2008
    Messages:
    231
    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
  7. PKtimservice

    PKtimservice New Member

    Joined:
    Sep 16, 2007
    Messages:
    6
    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
  8. koedel

    koedel aMember Pro Customer

    Joined:
    Jan 14, 2011
    Messages:
    16
    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.
  9. joybooster

    joybooster New Member

    Joined:
    Jun 6, 2011
    Messages:
    1
    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
  10. mcadmin

    mcadmin New Member

    Joined:
    Nov 9, 2011
    Messages:
    1
    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.
  11. alexander

    alexander Administrator Staff Member

    Joined:
    Jan 8, 2003
    Messages:
    6,274
    Change this code as well:
    PHP:
    if ($pattern)
    $where " AND u.login LIKE '" $this->escape($pattern) . "' ";
    to
    PHP:
    if ($pattern)
    $where " AND u.name_l LIKE '" $this->escape($pattern) . "' ";

Share This Page