w3programmers - Simple Pagination With PHP and MySQL
Posted on by

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 `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;
 }

94 comments to “Simple Pagination With PHP and MySQL”

  11. This Tutorial is Awesome.I hope all webprogrammer learn it very easy.
    it is very easy process to pagination.
    Good Luck sir

    Reply

  17. 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

    Reply

  29. 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.

    Reply

  30. 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

    Reply

  32. 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.

    Reply

  34. When I add WHERE or ASC to $result I get
    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\paginate\pagination.php on line 29

    My script is:

    $result = mysqli_query($conn, “SELECT * FROM `master_excel` ASC LIMIT $start, $perpage”);

    Works otherwise

    Reply

  35. when I run pagination.php I get
    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\pagination.php on line 30

    plz help.
    thank u

    Reply

  36. when I run pagination.php I get
    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\pagination.php on line 30

    plz help
    thank u

    Reply

  40. kiram dahanet ba in codet
    حهاشحبتشبختشب
    6541654

    شیبحختشحثۀ[ا
    jf[oqje+9652fa
    f’qojt[‘q

    Reply

  58. Thank you for this tutorial. However, I have noted that when fetching say 3000 records with a limit of 20 records per page, the page links are very many. Could you have a solution where we’d have something like :

    Prev 1 2 3 4 5 …56 57 58 59 60 Next

    Thank you.

    Reply

  70. Great Tutorial! This PHP pagination tutorial have all the options. But I think it would be better if you add an option to change the total number of results. What do you think about it?

    Reply

  71. Thanks Very much. I want to ask if there’s a way to get my query (that selects from the database table) out of the whole code and place it up. Thanks

    Reply

  78. how can we create it completely dynamic pagination using just one method function?with dynmic $table, $field, for whole site?

    Reply

  81. Hello,

    First, thanks for this pagination template. Second, I a beginner in php, so please, if you want to help would be of great help as I’m struggling with this for days and I really need help.
    I have a website that contain a table from a database and I have a php search form linked , using POST to insert some options via a dropdown box :
    if(isset($_POST[‘search’])) {
    $searchq = $_POST[‘search’];
    $query = mysqli_query($conn, “SELECT * FROM table WHERE colname LIKE ‘$searchq'”) or die (mysql_error());
    My result is not paginated so I tried to use your pagination file, only modifying the SELECT clauses. Unfortunately it’s not working as expected, if I put instead $searchq:
    the count works but only first page of results is shown, then going to page 2, it does not show anything, same for all next pages…, although there should be results in these following pages.
    Instead it works perfect when I put a text as ( ‘option’) instead and not using POST, but this is not what I want of course :
    SELECT * FROM ‘table’ WHERE ‘ colname = ‘ option ‘ Limit $start, $perpage”);
    Similar in the SELECT * count () as Total FROM ‘my table’ WHERE ‘ colname = ‘ option ‘

    What am I doing wrong ?
    I do not know if I made my self well understood, I am still learning. Thank you very much in advance, please contact me via email (mihai58@yahoo.com) if you need more details. Best regards

    Reply

Leave a Reply

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