Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

3 Answers 3

Your Query is

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

This Sql statement requests the first 10 authors

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
@ 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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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