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.

Is there an easy way to paginate the result set coming from $wpdb->get_results()?

I want to get a paginated list of a user's comments on the author_archive page - some of the users of the community have > 500 comments, so pagination is important.

Is there an in-built way of doing this with WordPress, or do I need to build it myself?

[Updated to add code]

<!-- Show comments -->
<?php 
$querystr = "
    SELECT comment_ID, comment_post_ID, post_title, LEFT(comment_content,100) as comment_content, comment_date_gmt
    FROM $wpdb->comments, $wpdb->posts
    WHERE user_id = $thisauthor->ID
    AND comment_post_id = ID
    AND comment_approved = 1
    ORDER BY comment_ID DESC
    LIMIT 100
 ";


$comments_array = $wpdb->get_results($querystr, OBJECT);

if ($comments_array): ?>
    <div id="author-comments-list">
        <h2>Recent Comments </h2>
        <ul>
            <? 
                $odd_even = "odd";
                foreach ($comments_array as $comment):
                    echo "<li class='$odd_even'><a href='". get_bloginfo('url') ."/?p=".$comment->comment_post_ID."/#comment-". $comment->comment_ID ."'>". $comment->post_title ."</a> : ".mysql2date('l jS F, Y, g:ia',$comment->comment_date_gmt);
                    echo "<div class='author-comments-content'>".$comment->comment_content."</li>";
                    $odd_even = ($odd_even == "odd" ? "even" : "odd");
               endforeach; ?>
        </ul>
    </div> 
<? endif; ?>
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

You can use the function paginate_links() for any pagination.

For your specific case:

$total = $wpdb->get_var("
    SELECT COUNT(comment_ID)
    FROM $wpdb->comments
    WHERE user_id = $thisauthor->ID
    AND comment_post_id = ID
    AND comment_approved = 1
");
$comments_per_page = 100;
$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 / $comments_per_page),
    'current' => $page
));
share|improve this answer
    
I thought paginate_links() was used to paginate post objects - it uses the global $wp_query. Is there some way of passing it an array of comments? –  anu Oct 26 '10 at 17:32
    
Please see the reference page: codex.wordpress.org/Function_Reference/paginate_links. "The function can be used to create paginated link list for any area." All the parameters are explained there. You can also post your code here so that we give you a more specific answer. –  sorich87 Oct 26 '10 at 18:16
    
Yes - I did look at the reference, and googled for other references - still can't see how to pass a set of comments into it for pagination though. –  anu Oct 27 '10 at 0:26
    
I modified my answer. –  sorich87 Oct 27 '10 at 22:35
1  
OK - a bit of a misunderstanding on my part - I thought there would be something to handle the retrieval of comments as well. But with a bit of tinkering, and a tiny bit of code to set the LIMIT start and end from the generated page number, that works fine. –  anu Oct 28 '10 at 9:44
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.