PHP Professional এবং Zend Certified PHP Engineer (ZCPE ) Course এ সীমিত সংখ্যক আসন বাকি আছে। আগ্রহীদেরকে অতিসত্বর মাসুদ আলম স্যার এর সাথে যোগাযোগ করার জন্য অনুরোধ করা যাচ্ছে। স্যার এর মোবাইল নম্বর : ০১৭২২ ৮১ ৭৫ ৯১

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

Hi, My name is Masud Alam, love to work with Open Source Technologies, living in Dhaka, Bangladesh. I’m a Certified Engineer on ZEND PHP 5.3, I served my first five years a number of leadership positions at Winux Soft Ltd, SSL Wireless Ltd, Canadian International Development Agency (CIDA), World Vision, Care Bangladesh, Helen Keller, US AID and MAX Group where I worked on ERP software and web development., but now i’m a founder and CEO of TechBeeo Software Company Ltd. I’m also a Course Instructor of ZCPE PHP 7 Certification and professional web development course at w3programmers Training Institute – a leading Training Institute in the country.

86 comments on “Simple Pagination With PHP and MySQL
        • i wanna to know i have pagination on my website in pakistani celebrities page. there i have 3 news showing on one page. all is perfect but i have one problem. i want to link one news to another page its because user can see detail page of news.. plz how to link one pagination news to other page

  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.

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

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

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

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

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

  10. Thank you so much!! It works and is easily portable to my web site…..i have not got your code onto my main website…but it is working great on my local site….

  11. Thank you so much it’s very helpful for me it’s i use in my website, I’m Rana Raheel Tariq Web Developer in Lahore, Pakistan, http://www.ranaraheeltariq.info
    once again Thank you soo much

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

  13. 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?

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

Leave a Reply

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