6

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; ?>
6

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
));
  • 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
1

for those looking for complete answer based on the question and @sorich87 answer.

I thought I should post solution here, for people like me (wordpress beginner).

//use in your custom page or custom post template 
global $wpdb;
$per_page = 5;
$page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;
if ($page > 1) {
    $offset = $page * $per_page - $per_page;
} else {
    $offset = $page;
}
$the_post = "select id,post_title from $wpdb->posts where post_type in ('buy','rent') and post_status='publish' "
        . "order by id desc limit $per_page offset $offset";
$details = $wpdb->get_results($the_post, OBJECT);
//do foreach to display your post details.


//paste this, where you want to display the pagination
$total = $wpdb->get_var("SELECT count(id) from $wpdb->posts where post_type in ('buy','rent') and post_status='publish' order by id desc");
echo paginate_links(array(
    'base' => add_query_arg('cpage', '%#%'),
    'format' => '',
    'prev_text' => __('&laquo;'),
    'next_text' => __('&raquo;'),
    'total' => ceil($total / $per_page),
    'current' => $page
));

this is how I got it working for my WordPress Website

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.