Simple PHP MySQL Pagination Script

Pagination is a frequent requirement part for listing large number records from the database. In this post we will see how to implement pagination for your web development projects.

Consider a student profile database with three columns (Register Number, Name and Class).  We will display only 10 records per page.

The pre requirements for Pagination are

  • Total number of records
  • Number records per page
  • Current page number
  • Current page URL
  • URL Query String

At first we will get total number of records in the student profile, using select query. Assign number records per page as 10, current page number using $_REQUEST[‘page’], current page URL and URL query string (optional).

$sql_query = mysqli_query($con,"select id from student_profile");  
$total_records = mysqli_num_rows( $sql_query);
$limit=10;
$current_page=$_REQUEST['page'];
$page_url='page.php';
$query_string='';

Call the Pagination function and we will get the array of output. In output array key ‘0’ has MySQL search query limit starting details and ‘1’ has pagination content in html format.

$pagination=Pagination($total_records,$current_page,$limit,$page_url,$query_string);
$sql_query =mysqli_query($con,"SELECT * FROM student_profile LIMIT $pagination[0], $limit");
echo "<table cellpadding='0' cellspacing='0' border='0'>
<tr bgcolor='#CCC'>
<td width='200'>Reg No.</td>
<td width='250'>Name</td>
<td width='200'>Class</td>
</tr>";
while($sql_row=mysqli_fetch_assoc($sql_query))
{
echo "<tr>
<td>".$sql_row['reg_no']."</td>
<td>".$sql_row['name']."</td>
<td>".$sql_row['class']."</td>
</tr>";
}
echo " </table>";
echo $pagination[1];

Pagination function

function Pagination($total_records,$current_page,$limit,$page_url,$query_string)
{
    $adjacents = 1;
    if($current_page)
        $start = ($current_page - 1) * $limit;
    else
        $start = 0;

    if ($current_page == 0) $current_page = 1;
    $prev = $current_page - 1;
    $next = $current_page + 1;
    $lastpage = ceil($total_records/$limit);
    $lpm1 = $lastpage - 1;
  if($query_string)
  $query_string="&".$query_string;
  $pagination = "";
    if($lastpage > 1)
    {
        $pagination .= "<div class='pagination' >";
        //previous button
        if ($current_page > 1)
            $pagination.= "<a href='".$page_url."?page=".$prev.$query_string."'>&laquo; previous</a>";
        else
            $pagination.= "<span class='disabled'>&laquo; previous</span>";
        //pages
        if ($lastpage < 5 + ($adjacents * 2))
        {
            for ($counter = 1; $counter <= $lastpage; $counter++)
            {
                if ($counter == $current_page)
                    $pagination.= "<span class='current'>$counter</span>";
                else
          $pagination.= "<a href='".$page_url."?page=".$counter.$query_string."'>$counter</a>";
            }
        }
        elseif($lastpage > 3 + ($adjacents * 2))
        {
            //close to beginning
            if($current_page < 1 + ($adjacents * 2))
            {
                for ($counter = 1; $counter < 2 + ($adjacents * 2); $counter++)
                {
                    if ($counter == $current_page)
                        $pagination.= "<span class='current'>$counter</span>";
                    else
                        $pagination.= "<a href='".$page_url."?page=".$counter.$query_string."'>$counter</a>";
                }
                $pagination.= "...";
                $pagination.= "<a href='".$page_url."?page=".$lpm1.$query_string."'>$lpm1</a>";
                $pagination.= "<a href='".$page_url."?page=".$lastpage.$query_string."'>$lastpage</a>";
            }
            //in middle
            elseif($lastpage - ($adjacents * 2) > $current_page && $current_page > ($adjacents * 2))
            {
                $pagination.= "<a href='".$page_url."?page=1".$query_string."'>1</a>";
                $pagination.= "<a href='".$page_url."?page=2".$query_string."'>2</a>";
                $pagination.= "...";
                for ($counter = $current_page - $adjacents; $counter <= $current_page + $adjacents; $counter++)
                {
                    if ($counter == $current_page)
                        $pagination.= "<span class='current'>$counter</span>";
                    else
                        $pagination.= "<a href='".$page_url."?page=".$counter.$query_string."'>$counter</a>";
                }
                $pagination.= "...";
                $pagination.= "<a href='".$page_url."?page=".$lpm1.$query_string."'>$lpm1</a>";
                $pagination.= "<a href='".$page_url."?page=".$lastpage.$query_string."'>$lastpage</a>";
            }
            //close to end
            else
            {
                $pagination.= "<a href='".$page_url."?page=1".$query_string."'>1</a>";
                $pagination.= "<a href='".$page_url."?page=2".$query_string."'>2</a>";
                $pagination.= "...";
                for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
                {
                    if ($counter == $current_page)
                        $pagination.= "<span class='current'>$counter</span>";
                    else
                        $pagination.= "<a href='".$page_url."?page=".$counter.$query_string."'>$counter</a>";
                }
            }
        }

        //next button
        if ($current_page < $counter - 1)
            $pagination.= "<a href='".$page_url."?page=".$next.$query_string."'>next &raquo;</a>";
        else
            $pagination.= "<span class='disabled'>next &raquo;</span>";
        $pagination.= "</div>n";
    }

    $results[0]=$start;
    $results[1]=$pagination;
    return $results;
}

Style

<style>
.pagination {
  text-align:center;
  margin: 10px;
  padding: 3px;
}

table td {
    padding: 3px 7px;
    border-bottom: 1px solid #D6D6D6;
    color: #143D55;
    font-size: 1em;
}

.pagination a, .pagination span{
    margin: 2px;
    padding: 3px 7px;
    background-color: #F5F5F5;
    border: 1px solid #D6D6D6;
    text-decoration: none;
    color: #143D55;
    font-size: 0.8em;
}
.pagination a:hover, .pagination a:active {
    border: 1px solid #575757;
    color: #000;
}
.pagination .current {
    font-weight: bold;
    background-color: #0079AE;
    color: #FFF;
    }
    .pagination .disabled {
    background-color: #FBFCFD;
    border: 1px solid #EEE;
    color: #DDD;
    }
</style>

Output

 

Download Sample / Source Code

Leave a Reply

Top