Home
Pagination In Php Using Mysql

Pagination In Php Using Mysql

July 21, 2016 Mysql, Php No Comments

What is pagination:

In php, if we want to see many database records on one page but at a time only few records can be viewed and after clicking  few links like page1,page2…etc we will be able to see other records on the same page  from the database then it can be achieved from pagination.

Requrements:

1-A database such as mysql.

2-A texteditor

3-A web browser such as firefox or chrome.

First of all create a database such as pagination and make a table like pagination_record in it.

Here is the create database query:

Create database pagination;

Insert Query:

INSERT INTO `pagination_record`(`Id`, `Name`) VALUES ([value-1],[value-2]);

Atleast we have to create 50 to 100 records for this process.

As we want to achieve pagination through our php script we have to know first that how many records we want to view at first.

Lets suppose If there are 100 records in the database and we want to view just ten records at once then first we have to define a variable which gets the value of the desired records to show on per page.Through that variable we will achieve our desired goal.

Lets  write and understand the php script for pagination and see what is going on in this script.

First we write a file name Connection.php
<?php
$servername="localhost";
$username="root";
$password="";
$dbname ="pagination";

mysql_connect($servername,$username,$password);
mysql_select_db($dbname);
?>
then,our next file name is pagination.php
<!doctype>
<html>
<head>
  <meta charset="UTF-8">
  <title>PAGINATION</title>
</head>
<body>
<center>
<?php
include('Connection.php');
$per_page = 10;
 echo $pages_query = mysql_query("Select count(Id) From pagination_record");

  $pages = ceil( mysql_result($pages_query , 0) / $per_page );
  $page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1 ;
  $start = ( $page - 1) * $per_page;
$query = mysql_query("Select * from pagination_record LIMIT $start,$per_page");

if($pages > 1  && $page <= $pages ) {
    for ($i = 1; $i <= $pages; $i++) {
        echo ($i == $page) ? '<strong style="font-weight: bold ; font-size: 24px"><a href="?page=' . $i . '">' . $i . '</a></strong> ' : '<a href="?page=' . $i . '">' . $i . '</a> ';
    }
}

echo "<br><br><br><br>";
?>
<table border="solid 1px" >

<?php
while($query_row = mysql_fetch_assoc($query)){
    echo "<tr><td width='35px' align='center'> ". $query_row['Id'] ." </td>" .
         "<td width='75px' align='center'> ". $query_row['Name']."</td></tr>";
}
?>

</table>
</center>
</body>
</html>

In this code snippet we are using different variables like $pages,$page and ceil function to minimize the error chance.

First we are calculating no. of records from a basic select query such as

Query: 
Select count(Id) From pagination_record;

This query will result in total results found in the database.

Then another query for selecting per page records is written below:

Query:
Select * from pagination_record LIMIT $start,$per_page;

Here the limit define the start page and per page no of records.

And after that we are checking if the page variable value is greater than 1 and if there are more pages then show our results in the form of table.

In this way, by using some simple and efficient coding techniques we have achieved our pagination.

 

 

 

About The Author

zeeshankhan