title: Paginating a dynamic SQL result
body: <p>i have a multiple checkboxes that filter an SQL result. I will be expecting with alot of records returned so i will like to paginate it. my current pagination code only works for simple SQL statements like <code>SELECT * FROM movies LIMIT {$startpoint} , {$per_page}</code> The issue is that i cant get it to work with my current jquery filter for my SQL result.</p>
  
  <p><strong>Pagination function</strong></p>
  
  <pre><code>   // Pagination Function
     function pagination($query,$per_page=10,$page=1,$url='?'){   
  global $conDB; 
  $query = "SELECT COUNT(*) as `num` FROM movies";
  $row = mysqli_fetch_array(mysqli_query($conDB,$query));
  $total = $row['num'];
  $adjacents = "2"; 
  
  $prevlabel = "&amp;lsaquo; Prev";
  $nextlabel = "Next &amp;rsaquo;";
  $lastlabel = "Last &amp;rsaquo;&amp;rsaquo;";
  
  $page = ($page == 0 ? 1 : $page);  
  $start = ($page - 1) * $per_page;                               
  
  $prev = $page - 1;                          
  $next = $page + 1;
  
  $lastpage = round($total/$per_page);
  
  $lpm1 = $lastpage - 1; // //last page minus 1
  
  $pagination = "";
  if($lastpage &gt; 1){   
      $pagination .= "&lt;ul class='pagination'&gt;";
  
  
          if ($page &gt; 1) $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$prev}'&gt;
    {$prevlabel}&lt;/a&gt;&lt;/li&gt;";
  
      if ($lastpage &lt; 7 + ($adjacents * 2)){   
          for ($counter = 1; $counter &lt;= $lastpage; $counter++){
              if ($counter == $page)
                  $pagination.= "&lt;li&gt;&lt;a class='current'&gt;{$counter}&lt;/a&gt; 
      &lt;/li&gt;";
              else
                  $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$counter}'&gt;
      {$counter}&lt;/a&gt;&lt;/li&gt;";                    
          }
  
      } elseif($lastpage &gt; 5 + ($adjacents * 2)){
  
          if($page &lt; 1 + ($adjacents * 2)) {
  
              for ($counter = 1; $counter &lt; 4 + ($adjacents * 2); 
          $counter++){
                  if ($counter == $page)
                      $pagination.= "&lt;li&gt;&lt;a class='current'&gt;{$counter}
             &lt;/a&gt;&lt;/li&gt;";
                  else
                      $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$counter}'&gt;
        {$counter}&lt;/a&gt;&lt;/li&gt;";                    
              }
              $pagination.= "&lt;li class='dot'&gt;...&lt;/li&gt;";
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$lpm1}'&gt;{$lpm1}&lt;/a&gt;
        &lt;/li&gt;";
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$lastpage}'&gt;
        {$lastpage}&lt;/a&gt;&lt;/li&gt;";  
  
          } elseif($lastpage - ($adjacents * 2) &gt; $page &amp;&amp; $page &gt; 
        ($adjacents * 2)) {
  
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page=1'&gt;1&lt;/a&gt;&lt;/li&gt;";
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page=2'&gt;2&lt;/a&gt;&lt;/li&gt;";
              $pagination.= "&lt;li class='dot'&gt;...&lt;/li&gt;";
              for ($counter = $page - $adjacents; $counter &lt;= $page +
          $adjacents; $counter++) {
                  if ($counter == $page)
                      $pagination.= "&lt;li&gt;&lt;a class='current'&gt;{$counter}
           &lt;/a&gt;&lt;/li&gt;";
                  else
                      $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$counter}'&gt;
            {$counter}&lt;/a&gt;&lt;/li&gt;";                    
              }
              $pagination.= "&lt;li class='dot'&gt;..&lt;/li&gt;";
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$lpm1}'&gt;{$lpm1}&lt;/a&gt;
          &lt;/li&gt;";
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$lastpage}'&gt;
         {$lastpage}&lt;/a&gt;&lt;/li&gt;";      
  
          } else {
  
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page=1'&gt;1&lt;/a&gt;&lt;/li&gt;";
              $pagination.= "&lt;li&gt;&lt;a href='{$url}page=2'&gt;2&lt;/a&gt;&lt;/li&gt;";
              $pagination.= "&lt;li class='dot'&gt;..&lt;/li&gt;";
              for ($counter = $lastpage - (2 + ($adjacents * 2));
             $counter &lt;= $lastpage; $counter++) {
                  if ($counter == $page)
                      $pagination.= "&lt;li&gt;&lt;a class='current'&gt;{$counter}
          &lt;/a&gt;&lt;/li&gt;";
                  else
                      $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$counter}'&gt;
         {$counter}&lt;/a&gt;&lt;/li&gt;";                    
              }
          }
      }
  
          if ($page &lt; $counter - 1) {
      $pagination.= "&lt;li&gt;&lt;a href='{$url}page={$next}'&gt;{$nextlabel}&lt;/a&gt;
       &lt;/li&gt;";
      $pagination.= "&lt;li&gt;&lt;a href='{$url}page=$lastpage'&gt;{$lastlabel}&lt;/a&gt;
        &lt;/li&gt;";
        }
  
      $pagination.= "&lt;/ul&gt;";        
         }
  
  return $pagination;
  }//
  </code></pre>
  
  <p><strong>Latest.php</strong></p>
  
  <pre><code> $page = (int)(!isset($_GET["page"]) ? 1 : $_GET["page"]);
   if ($page &lt;= 0) $page = 1;
  
   $per_page = 2; // Set how many records do you want to display per page.
  
  $startpoint = ($page * $per_page) - $per_page;
  
  $statement = "`movies` ORDER BY `m_id` ASC";  
  
              $con = mysqli_connect("localhost","root","","blabla");
         $mysqli = new mysqli("localhost", "root", "", "blabla");
  
  
           $db_username = 'root'; 
           $db_password = ''; 
           $db_name = 'blabla'; .
           $db_host = 'localhost';
  
           $conDB = mysqli_connect($db_host, $db_username, 
            $db_password,$db_name)or die('Error: Could not connect to
            database.');
  
  
           if (mysqli_connect_errno())
          {
  
           echo "Failed to Connect to MySQL:" . mysqli_connect_error();
           }
  
  
          $results2 = $mysqli-&gt;query( "select distinct(m_genre) 
          from movies where movie_status = '1'");  
  
           while($genre_data = mysqli_fetch_assoc($results2)){
  
              ?&gt;
  
                  &lt;a href="javascript:void(0);" class="list-group-item"&gt; 
                  &lt;label&gt; 
                  &lt;input type="checkbox" class="item_filter genre" 
              value="&lt;?php echo $genre_data['m_genre']; ?&gt;" &lt;?php
              if(in_array($genre_data['m_genre'],$genre)){ echo"checked";
               } ?&gt; &gt;
           &lt;span class="check-box-effect"&gt;&lt;/span&gt;
                  &amp;nbsp;&amp;nbsp; &lt;?php echo $genre_data['m_genre']; ?&gt;
         &lt;/label&gt;&lt;/a&gt;
  
              &lt;?php } ?&gt;  
              &lt;/div&gt;
  
  
  
              &lt;div class="col-xs-4"&gt;
              &lt;h3&gt;Country&lt;/h3&gt;
              &lt;?php 
                  $rs = $mysqli-&gt;query("select distinct(m_country) 
              from movies where movie_status = '1'");  
  
                  while($country_data = mysqli_fetch_assoc($rs)){
  
              ?&gt;
                  &lt;a href="javascript:void(0);" class="list-group-item"&gt; 
                  &lt;input type="checkbox" class="item_filter country"
          value="&lt;?php echo $country_data['m_country']; ?&gt;" &lt;?php 
          if(in_array($country_data['m_country'],$country)){ 
          echo"checked"; } ?&gt; &gt;
                  &amp;nbsp;&amp;nbsp; &lt;?php echo $country_data['m_country']; 
               ?&gt;     &lt;/a&gt;
              &lt;?php } ?&gt;  
              &lt;/div&gt;
  
              &lt;div class="col-xs-4"&gt;
              &lt;h3&gt;Year&lt;/h3&gt;
              &lt;?php 
                  $rs = $mysqli-&gt;query( "select distinct(m_year) 
                 from movies where movie_status = '1'");  
  
                  while($year_data = mysqli_fetch_assoc($rs)){
  
              ?&gt;
                  &lt;a href="javascript:void(0);" class="list-group-item"&gt; 
                  &lt;input type="checkbox" class="item_filter year" 
                  value="     &lt;?php echo $year_data['m_year']; ?&gt;"
                &lt;?php if(in_array($year_data['m_year'],$year))
                 { echo"checked"; } ?&gt; &gt;
                  &amp;nbsp;&amp;nbsp; &lt;?php echo $year_data['m_year']; ?&gt;&lt;/a&gt;
              &lt;?php } ?&gt;  
              &lt;/div&gt;
  
              &lt;/div&gt;
            &lt;/div&gt;
            &lt;/div&gt;
  
  
  
              &lt;div class="row"&gt;
                 &lt;div class="col-xs-12"&gt;
                &lt;?php 
  
  
                   $query = "SELECT * FROM movies 
            WHERE movie_status = '1'"; 
                    //filter query start 
                    if(!empty($genre)){
                        $genredata =implode("','",$genre);
                        $query  .= " and m_genre in('$genredata') LIMIT {$startpoint} , {$per_page} "; 
                    }
  
                     if(!empty($country)){
                        $countrydata =implode("','",$country);
                        $query  .= " and m_country in('$countrydata') LIMIT {$startpoint} , {$per_page} "; 
                    }
  
                    if(!empty($year)){
                        $yeardata =implode("','",$year);
                        $query  .= " and m_year in('$yeardata') LIMIT 
              {$startpoint} , {$per_page} "; 
                    }
                  //filter query end 
  
                   $rs = mysqli_query($mysqli,$query) or die("Error : 
     ".mysqli_error($mysqli));
    while(){
  
       ............................
              }
     echo '&lt;div class= "wrap"&gt;';
     // displaying paginaiton.
  
     echo pagination($statement,$per_page,$page,$url='?');
  
     echo "&lt;/div&gt;";
  </code></pre>
  
  <p>The current code as it is doesn't <code>Limit</code> the result and <code>displays all results</code>, if a <code>checkbox is checked</code>, the filter works but the pagination doesnt, <code>i.e no limit and link to next page gives the same result as the first</code>. Is there an easier way to paginate my filtered result or how can i fix my current code. i am open to new pagination solutions.</p>