Authors list Pagination : result set from $wpdb->get_results()

Question

Using this solution : Paginate result set from $wpdb->get_results()

I did make my code for Authors list page template as follow:

<div>    
<table id="mytable"><tbody>

<?php
 // Get the authors from the database ordered by user's display name
 global $wpdb;
 $query = "SELECT ID, display_name from $wpdb->users ORDER BY display_name LIMIT 10";
 $author_ids = $wpdb->get_results($query);
$authcount = -1;

$total = $wpdb->get_var("
    SELECT COUNT(ID)
    FROM $wpdb->users
");
$authors_per_page = 10;
$page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;

echo paginate_links( array(
    'base' => add_query_arg( 'cpage', '%#%' ),
    'format' => '',
    'prev_text' => __('&laquo;'),
    'next_text' => __('&raquo;'),
    'total' => ceil($total / $authors_per_page),
    'current' => $page
));

 // Loop through each author
 foreach($author_ids as $author) :
 // Get user data
 $curauth = get_userdata($author->ID);

 // If user_ID is 1 or 24, remove them from list
 if( ! in_array($curauth->ID, array(1, 24 )) ) :
 // Get link to author page
 $user_link = get_author_posts_url($curauth->ID);
    $post_count = get_usernumposts($curauth->ID);
 ?>
<tr<?php echo $authcount % 2 ? ' class="odd"' : ''; ?>>
<td>
<?php echo $authcount; ?>
</td><th scope="row">
<a href="<?php echo $user_link; ?>" title="<?php echo $curauth->display_name; ?>"><?php echo $curauth->display_name; ?></a>
</th><td>
( <?php echo $post_count; ?> )
</td></tr>

<?php endif; ?>
<?php $authcount = $authcount + 1; ?>
<?php endforeach; ?>

</tbody></table><br /></div>

But every page-number link display only the authors list from 1 to 10, whereas the database have a list of atleast 50-authors. Please help me to find out the error and its rectification. Thanks a lot.

Answer 1

Your Query is

"SELECT ID, display_name from $wpdb->users ORDER BY display_name **LIMIT 10**";

This Sql statement requests the first 10 authors

Answer 2

Like edelwater already wrote, you're missing to properly specify the LIMIT clause for your needs:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

12.2.8. SELECT Syntax (MySQL Manual)

So for a 1 based $page number, it's (literally) LIMIT ($page-1) * $pagesize, $pagesize where $pagesize is 10 in your case.

Answer 3

I wrote a solution to this by hacking together some pagination code with a modified WP_LIST_AUTHORS function. It seems to require a Cache plugin otherwise this page can load slowly, but it does work.

The code as well as further discussions on its formatting is located over in this thread: Modifying WP_LIST_AUTHOR Functions to output all users in a grid (and Paginate)

hope that helps!

PS -- If you just want to look at a properly formatted SQL query with a LIMIT and OFFSET for pagination, you can go here: http://www.phpfreaks.com/tutorial/basic-pagination