PHP pagination script - duplicate

Question

I'm selecting data from database and I want to limit result by X amount and then show the number of rows left and showing button below to navigate to next page of the result (continues until there's no more result in database).

How do i do this. my code is only fetching it...but how do I add the page 1,2,3,4,5

something like stackOverflow tags page

my code:

<?PHP 
          $SQL = "SELECT * FROM $tags_table LIMIT 20";
          $Q   = queryDB($SQL);
          if (mysqli_num_rows($Q)) {
            while ($row = mysqli_fetch_assoc($Q)) {
            $tag_id= $row['tag_id'];
            $details = $row['details'];
            $tagName= $row['tag_name'];

            $HTML_temp = "<div class='grid-view page-cell'>
              <div class='page-header'><a href='#' class='post-page'>{$tagName}</a></div>
              <div class='details'>{$details}</div>
            </div>";
            ECHO $HTML_temp;
            }
          }
          ?>

Two options: either you load the whole set of hits into the page and use a javascript based solution to "paginate" your visualization or you introduce an additional HTTP GET parameter that indicates which page to load for each individual request. You can then use mysql's `OFFS feature in combination with the `LIMIT` directive to fetch exactly the slice of results the client requests. — arkascha, Feb 03 '19 at 10:15
Sorry, a typo on my side, I wanted to write about the "OFFSET" feature. Seems I made a knot in my fingers trying to close the backtick bracket. — arkascha, Feb 03 '19 at 10:47

score 0 · Accepted Answer · answered Feb 03 '19 at 22:36

I created an answer to this feature. Below are my code for future use.

PHP:

<?PHP
          //count the number of rows in db
          function countTags(){
          global $conn, $tags_table;
          $SQL = "SELECT COUNT(tag_id) FROM $tags_table";
          return mysqli_fetch_array(queryDB($SQL))[0];
          }

          //Pager:
          //Show next pages box if $options["NumOfRecords"] is greater than 20
          //We fetching 20 records per page -- Adjust to your needs

          function pager(array $options = array()){
          $options = array_merge(array(
            'NumOfRecords'      => 0, //number of records from database 
            'maxPager'          => 5, //number pager to show eg. 1-5
            'maxPageRecords'    => 20 // since I need 20 records maximum per/page
          ),$options);

          $rows = (int) $options['NumOfRecords'];
          $maxPager = (int) $options['maxPager'];
          $maxPageRecords = (int) $options['maxPageRecords'];


          //offset records
          $offset  = $rows - $maxPageRecords; //since our max records per page is 20
          $totalPages = ceil($rows/$maxPageRecords); //move it to neart heigher number
          $numOfPagesLeft = $totalPages-1; //pages left

          if ($numOfPagesLeft) {
            //check if tab_id is set
          $tab_id = $activePage = isset($_GET['tab_id']) && (int)$_GET['tab_id'] ? (int)$_GET['tab_id']:1;

          //store pager here
          $pagerArray = array();

          //url to page we working on
          $URL = DOMAIN().'/'.(IsadminLogin() ? ("apps/tags/") :"tags/");
          $i = 0; //pager counter
          for ($row=0; $row < $numOfPagesLeft; $row++ ) { 
          $i++;
          //show only five pager
          if ($offset && $i <= $maxPager && $activePage !== $totalPages && $totalPages != $tab_id) {

            $page_id  = $tab_id++;
            $href     = $URL .$page_id;
            $active   = ($activePage == $page_id ? "current":"random");
            $ellipse  = ($i== $maxPager && $tab_id !== ($offset/$maxPageRecords) ? "<span class='page-numbers dots'>...</span>":"");
            $pagerArray[] = "<a class='page-numbers {$active}' href='{$href}/'>{$page_id}</a>{$ellipse}";
            }

          }

            //add previous page
            if ($activePage > 1) {
            //previous pagers
            $prev = $activePage -1;
            $prevHref = $URL .$prev;
            $prevPagers = $activePage > 2 ? "<a class='page-numbers prv' href='{$URL}1'>1</a><span class='page-numbers dots'>...</span>":""; 
            array_unshift($pagerArray , "<a class='page-numbers prev' href='{$prevHref}/'>prev</a>{$prevPagers}");
            }

            //show next button if offset is true

            if ($numOfPagesLeft) {
            //next pagers
            $next       = $activePage +1;
            $nextHref   = $URL.$next;
            $lastPager  = $totalPages;
            $PagerLast  = $URL.$lastPager;
            $active     = ($activePage == $lastPager ? "current":"random");
            $pagerS     = $lastPager ? "<a href='{$PagerLast}' class='page-numbers {$active}'>{$lastPager}</a>":"";
            array_push($pagerArray , $totalPages != $tab_id ? "{$pagerS} <a class='page-numbers prev' href='{$nextHref}/'>next</a>":"{$pagerS}");

            }

            //implode pager
            return  join('',$pagerArray);
            }

          }

          $maxPageRecords = 20; //since we fetching 20 records/page
          $tab_id = isset($_GET['tab_id']) && (int)$_GET['tab_id']  > 0 ? (int)$_GET['tab_id']:1;
          $offset = $tab_id == 1 ? 0:($tab_id > 1 ? ($tab_id-1) *$maxPageRecords:0);//calculate offset for SQL
          $SQL    = "SELECT * FROM $tags_table LIMIT {$maxPageRecords} OFFSET {$offset}";

          $Q    = queryDB($SQL);
          $NR   = mysqli_num_rows($Q);// number of rows returned
          if ($NR) {
            while ($row = mysqli_fetch_assoc($Q)) {
            $tag_id  = $row['tag_id'];
            $details = $row['details'];
            $TagName = $row['tag_name'];

            $HTML_temp = "<div class='grid-view tag-cell'>
            <div class='excerpt-header'><a href='#' class='post-tag'>{$TagName}</a></div>
            <div class='excerpt'>{$details}</div>
            </div>";
            ECHO $HTML_temp;
            }
          }else{
          ECHO "No tags found in database";
          }

          //echo $offset;

          //GET current domain name
          $LOCALIP     = array('127.0.0.1','::1');
          $sitename    = "school";
          $HostStatus   = !in_array(GET_IP(), $LOCALIP)? TRUE:FALSE; //hosting local or remote server
          function DOMAIN(){global $HostStatus,$sitename;if (isset($_SERVER['HTTPS']) && ($_SERVER['HTTPS'] == 'on' || $_SERVER['HTTPS'] == 1) ||isset($_SERVER['HTTP_X_FORWARDED_PROTO']) &&$_SERVER['HTTP_X_FORWARDED_PROTO'] == 'https') { $domain = 'https://'.str_replace(array('https://www.','http://www.','www.'), '', ($_SERVER['SERVER_NAME'])).($HostStatus ? '':'/'.$sitename);}else {$domain = 'http://'.str_replace(array('https://www.','http://www.','www.'), '', ($_SERVER['SERVER_NAME'])).($HostStatus ? '':'/'.$sitename);} return $domain;}function GET_IP(){if (!empty($_SERVER['HTTP_CLIENT_IP']))  {$ip=$_SERVER['HTTP_CLIENT_IP'];}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {$ip=$_SERVER['HTTP_X_FORWARDED_FOR'];}else{$ip=$_SERVER['REMOTE_ADDR'];}return $ip;}
          ?>

HTML: Lets call pager() function

<div class='pager'>
<div class='col-lg-12 no-padding'>
<div class='col-lg-2'></div>
<div class='col-lg-4'></div>
<div class='col-lg-6 no-padding'>
<div class="pager rfloat">

<?PHP 
//call pager() on the button of the page u want to show pagwer boxes
ECHO pager(array(
    'maxPageRecords'=>20,
    'NumOfRecords'=>countTags(), //records could be any number e.g. 400,200,52,12..make sure its coming from DB
    'maxPager'=>5));
?>

</div>
</div>
</div>
</div>

CSS:

.pager a{
    display: inline-block;
    padding: 4px 8px;
    font-size: 12px;
    color: #848d95;
    border: 1px solid #e4e6e8;
    border-radius: 3px;
    background-color: transparent;
    margin-left: 5px;
}

.page-numbers.current,.page-numbers:hover{
  transition: all ease-in-out .2s;
  color: #FFF;
  background-color: #ff7308;
  border-color: transparent;
}
.page-numbers.dots{
    color: #848d95;
    background-color: transparent;
    border-color: transparent;
    box-shadow: none;
    padding:0 15px;
    letter-spacing: 3px;
}

PHP pagination script - duplicate

1 Answers1