I wish to produce a (fairly large) Wordpress user index using the customers categorized alphabetically, such as this:

A

  • Amy
  • Adam

B

  • Bernard
  • Bianca

and so forth.

I have produced a custom Wordpress query which works acceptable for this, aside from one problem: Additionally, it shows "empty" letters, letters where there isn't any customers whose title starts with this letter. I'd be happy should you may help me fix this code to ensure that it only shows the letter if there's really a person having a title of this letter :) I have attempted my luck by checking the number of results you will find for your letter, but in some way that isn't working.

(FYI, I personally use the consumer photo wordpress plugin and simply want to show customers within the list who've an approved picture, hence the stuff within the SQL query).

<?php
    $alphabet = range('A', 'Z');
    foreach ($alphabet as $letter) {
      $user_count = $wpdb->get_results("SELECT COUNT(*) FROM wp_users WHERE display_name LIKE '".$letter."%' ORDER BY display_name ASC");
      if ($user_count > 0) {
        $user_row = $wpdb->get_results("SELECT wp_users.user_login, wp_users.display_name 
        FROM wp_users, wp_usermeta
        WHERE wp_users.display_name LIKE '".$letter."%' 
        AND wp_usermeta.meta_key = 'userphoto_approvalstatus'
        AND wp_usermeta.meta_value = '2'
        AND wp_usermeta.user_id = wp_users.ID
        ORDER BY wp_users.display_name ASC");
        echo '<li class="letter">'.$letter.'';
        echo '<ul>';
        foreach ($user_row as $user) { 
          echo '<li><a href="/author/'.$user->user_login.'">'.$user->display_name.'</a></li>';
        }
      echo '</ul></li>';
      }                     
    }
    ?>

Thanks ahead of time!


Update:

I edited the SQL into this, however I've the issue it spits out an </ul> prior to the first li, which screws in the layout, such as this:

</ul>
</li>
<li class="letter">
    <div class="letter_head">A</div>
  <ul>
    <li>Abigail</li>
  </ul>
</li>

<li class="letter">
<div class="letter_head">B</div>
<ul>
  <li>Bernard</li>      
  <li>Bianca</li>       
</li>
</ul>

It's generally pretty wonky concerning the ul and li nesting. How do i repair it?

            <?php 
    $qry = "SELECT DISTINCT wp_users.user_login, wp_users.display_name, 
            LEFT(UPPER(wp_users.display_name), 1) AS first_char 
            FROM wp_users, wp_usermeta
            WHERE UPPER(wp_users.display_name) BETWEEN 'A' AND 'Z'
            OR wp_users.display_name BETWEEN '0' AND '9'  
            AND wp_usermeta.meta_key = 'userphoto_approvalstatus'
            AND wp_usermeta.meta_value = '2'
            AND wp_usermeta.user_id = wp_users.ID
            ORDER BY wp_users.display_name ASC";
            $result = mysql_query($qry);
            $current_char = '';
            while ($row = mysql_fetch_assoc($result)) {
                if (!isset($current_char)) {
                        echo '<li class="letter"><div class="letter_head">'.$current_char.'</div>';
                        echo '<ul>';
                } elseif ($row['first_char'] != $current_char) {
                        echo '</ul>';
                        $current_char = $row['first_char'];
                        echo '<li class="letter"><div class="letter_head">'.$current_char.'</div>';
                        echo '<ul>';
                }
                echo '<li><a href="/member/'.$row['user_login'].'">'.$row['display_name'].'</a></li>';
            }  
    ?>

A much better solution is always to loop through all customers from a to z as well as for every record see if the very first letter is different from those of the final record. If different, you echo the new letter.

You do 52 database calls. May be the user table too large to seize these in a single query? Also, In my opinion an order by around the count is unnecessary. I don't determine if MySQL ignores it or otherwise.

Your condition is get_results returns an single element array each time for that count query. So $user_count > is definitely coming back true. (I do not understand PHP logic more often than not. Is definitely an array more than zero?). You must do something similar to $user_count[] to access the particular number.