I am using a custom $wpdb
query to get all comments from my database.
<?php
$querystr = " SELECT comment_content, commentmeta1.meta_value
AS comment_name, commentmeta2.meta_value
AS comment_country FROM $wpdb->comments, $wpdb->commentmeta
AS commentmeta1, $wpdb->commentmeta
AS commentmeta2
WHERE $wpdb->comments.comment_ID = commentmeta1.comment_id
AND $wpdb->comments.comment_ID = commentmeta2.comment_id
AND commentmeta1.meta_key = 'comment_name'
AND commentmeta2.meta_key = 'comment_country'
AND $wpdb->comments.comment_approved = 1 O
RDER BY $wpdb->comments.comment_date DESC";
$comment_info = $wpdb->get_results($querystr, OBJECT);
echo '<ul>';
// display the results
foreach($comment_info as $info) {
echo '<li class="commentBox"><p>"' . $info->comment_content . '"</p><h6>' . $info->comment_name . ', ' . $info->comment_country . '</h6></li>';
}
echo '</ul>';
?>
This works great and pulls in and displays the data exactly as I want, however, I also need to add pagination to the comments. The code I am currently using:
<?php
$total = $wpdb->get_var(" SELECT COUNT(comment_ID)
FROM $wpdb->comments WHERE comment_approved = 1 ");
$comments_per_page = 4;
$page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;
echo paginate_links( array(
'base' => add_query_arg( 'cpage', '%#%' ),
'format' => '',
'prev_text' => __('«'),
'next_text' => __('»'),
'total' => ceil($total / $comments_per_page),
'current' => $page
));
?>
Although the pagination links do appear as they should, the $comments_per_page
variable set has no effect because all comments are still displayed on one page. I am running this code on a custom page template.
Where am I going wrong?
So with a bit of research and a lot of trial and error I managed to sort it. With a bit of a code re-shuffle I also had to set an offset variable and pass that into my database query with
LIMIT
. Final working code is below. Hopefully this can also help someone else.