pagination on data fetched using SQL query

Question

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.

Answer 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.