Simple Pagination Script using PHP and MySQL

In PHP  |  Last Updated on Sat, 02 Jul 2016

Hello friends, this post which I going to write is very basic and simple script and it is easy to understand. So this post is about simple pagination script using PHP and MySQL.

Pagination is a technique to display a large number of list item into several groups with separate URL. You can click on the page number (Pagination) to go different list item into several groups with separate URL. You can see the live demo below.

It is very easy to apply pagination to the web page using PHP and MySQL. Hope this tutorial will help you to understand.

index.php

To display the limited list item per page, you have to set start and limit syntax. Using two variable you can define them.

“Select * from table Limit {starting Point}, Limit”
For example  “Select * from pagination_data Limit 0, 10”

<?php
include_once "conn.php"; 
include_once "function.php";

if(isset($_GET["page"]))
$page = (int)$_GET["page"];
else
$page = 1;
$setLimit = 10;
$pageLimit = ($page * $setLimit) - $setLimit;
?>

<div class="navi">
<?php
// Your SQL query execute here. This query will display all record by setting the Limit.
$sql = "SELECT * FROM pagination_data LIMIT ".$pageLimit." , ".$setLimit;
$query = mysql_query($sql);
while ($rec = mysql_fetch_array($query)) {
?>
    <div class="view"><a href="http://www.uandblog.com/" target="_blank"><?php echo $rec['title'];?></a></div>
<?php }    ?>
</div>

<?php
     echo displayPaginationHere($setLimit,$page); // Call the Pagination Function to display Pagination.
?>

function.php

Main script to generate pagination and return the value to the function. Which is called from the home page.
 

<?php

   function displayPaginationHere($per_page,$page){
       $page_url="?";
        $query = "SELECT COUNT(*) as totalCount FROM pagination_data";
        $rec = mysql_fetch_array(mysql_query($query));
        $total = $rec['totalCount'];
        $adjacents = "2"; 

        $page = ($page == 0 ? 1 : $page);  
        $start = ($page - 1) * $per_page;                                
        
        $prev = $page - 1;                            
        $next = $page + 1;
        $setLastpage = ceil($total/$per_page);
        $lpm1 = $setLastpage - 1;
        
        $paging = "";
        if ($setLastpage > 1)
        {    
            $paging .= "<ul class='paging'>";
                    $paging .= "<li class='setPage'>Page $page of $setLastpage</li>";
            if ($setLastpage < 7 + ($adjacents * 2))
             {   
                for ($counter = 1; $counter <= $setLastpage; $counter++)
                {
                    if ($counter == $page)
                        $paging.= "<li><a class='current_page'>$counter</a></li>";
                    else
                        $paging.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";                    
                }
            }
            elseif($setLastpage > 5 + ($adjacents * 2))
              {
                if($page < 1 + ($adjacents * 2))        
                 {
                    for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
                     {
                        if ($counter == $page)
                            $paging.= "<li><a class='current_page'>$counter</a></li>";
                        else
                            $paging.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";                    
                    }
                    $paging.= "<li class='dot'>...</li>";
                    $paging.= "<li><a href='{$page_url}page=$lpm1'>$lpm1</a></li>";
                    $paging.= "<li><a href='{$page_url}page=$setLastpage'>$setLastpage</a></li>";        
                }
                elseif($setLastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
                 {
                    $paging.= "<li><a href='{$page_url}page=1'>1</a></li>";
                    $paging.= "<li><a href='{$page_url}page=2'>2</a></li>";
                    $paging.= "<li class='dot'>...</li>";
                    for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
                     {
                        if ($counter == $page)
                            $paging.= "<li><a class='current_page'>$counter</a></li>";
                        else
                            $paging.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";                    
                    }
                    $paging.= "<li class='dot'>..</li>";
                    $paging.= "<li><a href='{$page_url}page=$lpm1'>$lpm1</a></li>";
                    $paging.= "<li><a href='{$page_url}page=$setLastpage'>$setLastpage</a></li>";        
                }
                else
                 {
                    $paging.= "<li><a href='{$page_url}page=1'>1</a></li>";
                    $paging.= "<li><a href='{$page_url}page=2'>2</a></li>";
                    $paging.= "<li class='dot'>..</li>";
                    for ($counter = $setLastpage - (2 + ($adjacents * 2)); $counter <= $setLastpage; $counter++)
                     {
                        if ($counter == $page)
                            $paging.= "<li><a class='current_page'>$counter</a></li>";
                        else
                            $paging.= "<li><a href='{$page_url}page=$counter'>$counter</a></li>";                    
                    }
                }
            }
            
            if ($page < $counter - 1){ 
                $paging.= "<li><a href='{$page_url}page=$next'>Next</a></li>";
                $paging.= "<li><a href='{$page_url}page=$setLastpage'>Last</a></li>";
            } else  {
                $paging.= "<li><a class='current_page'>Next</a></li>";
                $paging.= "<li><a class='current_page'>Last</a></li>";
            }

            $paging.= "</ul>\n";        
        }
    
        return $paging;
    } 
?>

conn.php

<?php
error_reporting(E_ALL && ~E_NOTICE);
define ("DB_HOST", "localhost"); // Your database host name
define ("DB_USER", "root"); // Your database user
define ("DB_PASS",""); // Your database password
define ("DB_NAME","pagination"); // Your database name

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");
?>