0

I am new with php and word press. I have a question that I am using a sql query in my posts to get data from my data base. Now the problem is that I have huge data on my post and I do not have idea how to apply pagination on the data which has been fetched using the SQL query. Here is the code which I am using in my post.

<?php
global $wpdb;
// QUERY HERE TO COUNT TOTAL RECORDS FOR PAGINATION 
$total = $wpdb->get_var("SELECT COUNT(distinct hadith_no, content) FROM muslim");
$post_per_page = 10;
$page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;
$offset = ( $page * $post_per_page ) - $post_per_page;

// QUERY HERE TO GET OUR RESULTS 
$results = $wpdb->get_results("SELECT hadith_no, content FROM muslim");
// PHP FOR EACH LOOP HERE TO DISPLAY OUR RESULTS
foreach($results as $row)
 {
 echo $row->hadith_no."  ".$row->content."<br>"; 
 }
// END OUR FOR EACH LOOP
?>
// PAGINATION HERE IN NICE BOOTSTRAP STYLES
<?php 
echo '<div class="pagination">';
echo paginate_links( array(
'base' => add_query_arg( 'cpage', '%#%' ),
'format' => '',
'prev_text' => __('&laquo;'),
'next_text' => __('&raquo;'),
'total' => ceil($total / $post_per_page),
'current' => $page,
'type' => 'list'
));
echo '</div>';
?>

This give me a vertical pagination at the bottom of the page and still shows all the content on a single post instead of showing 10 posts. Any one who can help me using pagination on my post or edit this code so that I can have pagination. Thank you.

  • You're fetching custom records and not Posts in the WP sense of the word. Do you really need to use a separate table for this data, or would you be better off storing it as a custom post type? If you can store it as custom posts, then you get the benefit of WP's API including pagination of lists of posts. – Andy Macaulay-Brook Jul 26 '16 at 9:36
  • Thanks @AndyMacaulay-Brook for this response. There was a problem with my COUNT query which i have resolved and posted an answer for this question so that other users having this type of query can get help from here. – Salik Asad Jul 26 '16 at 9:42
  • Great - it's fine to post your own answer. Make sure you accept it too! – Andy Macaulay-Brook Jul 26 '16 at 9:43
1
<?php
global $wpdb;
// QUERY HERE TO COUNT TOTAL RECORDS FOR PAGINATION 
$total = $wpdb->get_var("SELECT COUNT(*) FROM (SELECT * FROM muslim LIMIT 0,431) AS a");
$post_per_page = 10;
$page = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1;
$offset = ( $page * $post_per_page ) - $post_per_page;

// QUERY HERE TO GET OUR RESULTS 
$results = $wpdb->get_results("SELECT hadith_no, content FROM muslim LIMIT $post_per_page OFFSET $offset");

// PHP FOR EACH LOOP HERE TO DISPLAY OUR RESULTS
foreach($results as $row)
 {
 echo $row->hadith_no."  ".$row->content."<br>"; 
 }
// END OUR FOR EACH LOOP

?>
<?php 
echo '<div class="pagination">';
echo paginate_links( array(
'base' => add_query_arg( 'cpage', '%#%' ),
'format' => '',
'prev_text' => __('&laquo;'),
'next_text' => __('&raquo;'),
'total' => ceil($total / $post_per_page),
'current' => $page,
'type' => 'list'
));
echo '</div>';
?>

Just edit the sql query according to your needs and this will work sweetly.

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.