Simple PHP pagination Example using MySQL records

Learn PHP with Examples
May 15, 2016
4 (1 votes)
Simple PHP pagination Example using MySQL records Simple PHP pagination Example using MySQL records
5 5 72
40 lifelong SEO backlinks to boost your Google Ranking

During Web Application designing to display bulk of records in distributed manner we use pagination. Assume in your Customers table you have more than 600 records. While displaying those records in a tabular view it is much better to use pagination. The key advantage of using pagination is we can do partial loading from the database. Which saves user time.

Simple PHP pagination Example

In this example I am with a PHP pagination script which fetch data from MySQL. To start with Create a table using the following “CREATE TABLE…” query. Keep your database name “empdb”.

CREATE TABLE IF NOT EXISTS EmpDetails (
Emp_ID varchar(10) NOT NULL DEFAULT '', 
Emp_Name varchar(200) NOT NULL DEFAULT '', 
Emp_Designation varchar(200) NOT NULL DEFAULT '', 
Emp_Salary varchar(20) NOT NULL DEFAULT '0', 
PRIMARY KEY (Emp_ID) 
)

Once you successfully created the "EmpDetails" table. Insert some sample records using the following INSERT STATEMENT.

INSERT INTO EmpDetails (Emp_ID, Emp_Name, Emp_Designation, Emp_Salary) VALUES
(1, 'Mounika Readdy', 'UI Developer', '23000'),
(2, 'Vishval Chohhan', 'Graphics Engineer', '12000'),
(3, 'Biswabhusan Panda', 'Sr. Software Engineer', '45000'),
(4, 'Satyabrat Panda', 'Program Manager', '60000'),
(5, 'Nilima Kapoor', 'JS Developer', '30000'),
(6, 'Megha Roy', 'Software Engineer', '12000'),
(7, 'Manamohan Maharana', 'Team Lead', '25000'),
(8, 'Rupak Srivastab', 'UI Developer', '32000'),
(9, 'Ramkumar Ojha', 'QA Engineer', '10000'),
(10, 'Mittali Roy', 'Graphics Engineer', '15000'),
(11, 'Ravina Mohapatra', 'Team Lead', '25000'),
(12, 'Raghav Malhotra', 'Sr. Software Engineer', '42000');

pagination.php is the File where I am binding data to a HTML table and at the same time implemented pagination. This is a very simple working example of pagination. You can easily customize it as per your requirements. To decide how many records you want to display in a page here I declared 2 variable $begin=0; $end=4;. By updating the second variable $end you can change the number of records per page.

PHP pagination Example

<?php
/*Establishing Database Connection*/
$sql_query=mysql_connect("localhost", "root");
mysql_select_db("empdb", $sql_query);

/*Defining number of records in a page*/
$begin=0;
$end=4;

if(isset($_GET['id']))
{
$id=$_GET['id'];
$begin=($id-1)*$end;
} else {
$id = 0;
}

/*Executing & fetching data from SQL table*/
$emp_query=mysql_query("SELECT * FROM EmpDetails LIMIT $begin, $end");

$rows=mysql_num_rows(mysql_query("SELECT * FROM EmpDetails"));
$total=ceil($rows/$end);

/*Creating table dynamically*/
echo "<table>";

/*Defining Table header*/
echo "<tr><th>Employee Name</th><th>Designation</th><th>Salary</th></tr>";

/*Generating rows with data*/
while($data=mysql_fetch_array($emp_query)) {
echo "<tr>";
echo "<td>". $data['Emp_Name'] ."</td>";
echo "<td>". $data['Emp_Designation'] ."</td>";
echo "<td>". $data['Emp_Salary'] ."</td>";
echo "</tr>";
}
echo "</table>";

/*Implemented next and previous buttons*/
if($id>1)
{
echo "<div class='parallalpre'><a href='?id=".($id-1)."' class='pagButton'>PREVIOUS</a>&nbsp;</div>";
}
if($id!=$total)
{
?>

<div class='parallal'>

<?php
/*Generating Pagination Tabs*/
echo "<ul class='pagination'>";
for($i=1;$i<=$total;$i++)
{
if($i==$id) { echo "<li>".$i."</li>"; }

else { echo "<li><a href='?id=".$i."'>".$i."</a></li>"; }
}
echo "</ul>";
?>

</div>

<?php
echo "<div class='parallalnex'>&nbsp;<a href='?id=".($id+1)."' class='pagButton'>NEXT</a></div>";
}
?>

Using the above code you can have a PHP pagination with table of data. To make the pagination better in below I am with some CSS classes. These classes provides style to pagination.

Required CSS Classes

/*Style for Table*/
table, th , td {
border: 1px solid grey;
border-collapse: collapse;
padding: 4px;
font-family: arial;
}
/*Style for Table Header*/
th {
background: darkblue;
color: white;
text-align: left;
}
/*Style for Alternate Rows*/
table tr:nth-child(odd) {
background-color: #C2EBC3;
}
table tr:nth-child(even) {
background-color: #FFFFFF;
}

/*Style for Pagination*/
.parallal { float:left; }
.parallalnex, .parallalpre { float:left; margin-top: 16px; }
.pagination { margin-top: 16px; padding:0; }

.pagination li {
display: inline-block;
padding: 0px 9px;
margin-right: 4px;
border-radius: 3px;
border: solid 1px #c0c0c0;
background: #e9e9e9;
box-shadow: inset 0px 1px 0px rgba(255,255,255, .8), 0px 1px 3px rgba(0,0,0, .1);
font-size: .875em;
font-weight: bold;
text-decoration: none;
color: #717171;
text-shadow: 0px 1px 0px rgba(255,255,255, 1);
}

.pagination li:hover, .pagination.gradient:hover {
background: #fefefe;
background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#FEFEFE), to(#f0f0f0));
background: -moz-linear-gradient(0% 0% 270deg,#FEFEFE, #f0f0f0);
}

Tags:, , , ,