Simple Pagination With PHP and MySQL

Simple Pagination With PHP and MySQL

When you have a large list of items ( search results, products list, blog articles ). Its better to display them grouped in pages and you can navigate from one page to another through navigational elements.
Its very easy to apply pagination. Its very basic tutorial to teach you how to apply pagination.

To display the limited records per page, you have to use mysql start and limit syntax. So you have to give start point and a limit. “Select * from table Limit {starting Point}, Limit”

For example

“Select * from post Limit 0, 5″

the records starting from 0 index of the table will be returned. As we have applied limit of 5, so five records from 0 index will be returned.

Database Table


CREATE TABLE IF NOT EXISTS `post` (
   `id` int(11) NOT NULL auto_increment,
   `title` varchar(255) NOT NULL,
   `detail` text NOT NULL,
   PRIMARY KEY  (`id`)
 )

 

Sample Data:

 


INSERT INTO `test`.`post` (`id`, `title`, `detail`)
 VALUES (NULL, 'Concept', 'Web Technologies'),
 (NULL, 'HTML', 'Basic HTML, Special Tags, Formatting Tags, HTML Forms'),
 (NULL, 'CSS', 'Basic CSS, Advanced Topics, '),
 (NULL, 'JAVASCRIPT', 'Syntax, Enable, Location, External, Operators, Variable'),
 (NULL, 'PHP BASIC PART1', 'Introduction To PHP'),
 (NULL, 'PHP Basic 2', 'Arrays and Array Functions'),
 (NULL, 'MySQL Basic', 'Introduction To MySQL'),
 (NULL, 'CMS', 'What is Joomla'),
 (NULL, 'XML', 'What is XML'),
 (NULL, 'PHP Date', 'What''s a timestamp'),
 (NULL, 'Files', 'Reading Files'),
 (NULL, 'JavaScript', 'Ajax Basics'),
 (NULL, 'File Formats', 'Creating PDF Files'),
 (NULL, 'MySQL Database Administrators', 'Understanding MySQL Table Types'),
 (NULL, 'PHP OOP', 'Understanding OOP Concepts');

config.php

You need to change host name, username, password, and database name.

$conn=mysqli_connect("localhost","root","","team44");

pagination.php
There are two portions, items to display and navigational elements.

<?php

require_once "config.php";

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Pagination</title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body>

<table style="border: 1px #000000 solid;" width="400" cellspacing="2" cellpadding="2" align="center">
<?php

$perpage = 5;

if(isset($_GET["page"])){
$page = intval($_GET["page"]);
}
else {
$page = 1;
}
$calc = $perpage * $page;
$start = $calc - $perpage;
$result = mysqli_query($conn, "select * from post Limit $start, $perpage");

$rows = mysqli_num_rows($result);

if($rows){
$i = 0;
while($post = mysqli_fetch_assoc($result)) {
?>
<tbody>
<tr style="background-color: #cccccc;">

<td style="font-weight: bold;font-family: arial;"><?php echo $post["title"]; ?></td>

</tr>

<tr>

<td style="font-family: arial;padding-left: 20px;"><?php echo $post["detail"]; ?></td>

</tr>
<?php
}
}
?>

</tbody>
</table>

and for Navigation elements..


<?php
<table width="400" cellspacing="2" cellpadding="2" align="center">
<tbody>
<tr>
<td align="center">

<?php

if(isset($page))

{

$result = mysqli_query($conn,"select Count(*) As Total from post");

$rows = mysqli_num_rows($result);

if($rows)

{

$rs = mysqli_fetch_assoc($result);

$total = $rs["Total"];

}

$totalPages = ceil($total / $perpage);

if($page <=1 ){

echo "<span id='page_links' style='font-weight: bold;'>Prev</span>";

}

else

{

$j = $page - 1;

echo "<span><a id='page_a_link' href='pagination.php?page=$j'>< Prev</a></span>";

}

for($i=1; $i <= $totalPages; $i++)

{

if($i<>$page)

{

echo "<span><a id='page_a_link' href='pagination.php?page=$i'>$i</a></span>";

}

else

{

echo "<span id='page_links' style='font-weight: bold;'>$i</span>";

}

}

if($page == $totalPages )

{

echo "<span id='page_links' style='font-weight: bold;'>Next ></span>";

}

else

{

$j = $page + 1;

echo "<span><a id='page_a_link' href='pagination.php?page=$j'>Next</a></span>";

}

}

?></td>
<td></td>
</tr>
</tbody>
</table>

</body>
</html>

style.css


page_links
 {
  font-family: arial, verdana;
  font-size: 12px;
  border:1px #000000 solid;
  padding: 6px;
  margin: 3px;
  background-color: #cccccc;
  text-decoration: none;
 }
 #page_a_link
 {
  font-family: arial, verdana;
  font-size: 12px;
  border:1px #000000 solid;
  color: #ff0000;
  background-color: #cccccc;
  padding: 6px;
  margin: 3px;
  text-decoration: none;
 }

36 comments on “Simple Pagination With PHP and MySQL
  1. This Tutorial is Awesome.I hope all webprogrammer learn it very easy.
    it is very easy process to pagination.
    Good Luck sir

  2. nice work, its so good. but in computer, it repeat 2 time , like this
    prev 1, 2, 3, 4,5 Next prev 1, 2, 3, 4,5 Next, any one tell me why it repeat

  3. Hi,
    very useful code. it works well for me, but one error is coming.
    for eg: i am having a table with 19 rows.
    rows per page = 5
    then, it should show page1, page2, page3 by clicking the Next.
    but with this code, Next> in page3 is also showing clickable option, so the page4 is also opening. Please help with the code.

  4. can anybody tell me how should call it ?
    i don’t know about php.
    i was created all the files mentioned above
    config.php
    pagination.php
    stylee.css
    and post table also.
    then how should i apply this script. whats is the url to open in html. please tell me….thanks in advance

  5. Thank you sir..it is very useful code, first i try it but it was not working but after i write “mysql” where where you have write “mysqli” and now it is working, i am beginner in programming what is difference between mysql and mysqli . and why a team44.sql file is along with download . Code is also working without team44.sql file and what is team44.sql.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>