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

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

<table id="mytable"><tbody>

 // 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("
    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"' : ''; ?>>
<?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>
( <?php echo $post_count; ?> )

<?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.

  • no need for the sql part, you can get a list of author IDs with get_users_of_blog() – onetrickpony Dec 17 '10 at 19:55

Your Query is

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

This Sql statement requests the first 10 authors

  • but when I removed this LIMIT 10, then it is displaying all the authors on one page. what is the solution then? – syed Nov 21 '10 at 9:36

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.

  • @ hakre, but Even after setting the LIMIT as LIMIT $page, 10 I couldn't able to get proper paginated links, All the page links displays only from Author ID's no:1 to no:9 – syed Nov 22 '10 at 6:32
  • @syed: Please var_dump($query); and paste it here. – hakre Nov 22 '10 at 14:04
  • @syed: also, verify you put the line $page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1; before the query. – sorich87 Nov 23 '10 at 8:33
  • @sorich87: and abs() is most certainly not what you want, consider max(): ? max(0, (int) $_GET['cpage'] - php.net/manual/en/function.max.php – hakre Nov 23 '10 at 14:48
  • hello hakre i am facing the [same problem][1] as you have solved? [1]: xrl.in/7ge8 – Fask Mar 14 '11 at 11:41

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.