« Protect email address from spam botsHow to make a PHP calendar »

PHP / MySQL select data and split on pages Posted in PHP Tutorials, MySQL Tutorials | 160 Comments
This tutorial is going to show you how to SELECT data from a MySQL database, split it on multiple pages and display it using page numbers.

We have MySQL table called “students” holding 90 records with the following fields:
Name – varchar(250)
PhoneNumber – varchar(250)
Instead of doing a single SELECT query and display all the 90 records on a single page we can have 5 pages each containing maximum 20 records. To do this we will need to use the LIMIT clause for SELECT command so we can limit the query to show only 20 records. The LIMIT clause also allows you to specify which record to start from. For example this query

$sql = "SELECT * FROM students ORDER BY name ASC LIMIT 0, 20";


returns 20 records sorted by name starting from the first record. This next query

$sql = "SELECT * FROM students ORDER BY name ASC LIMIT 50, 20";


shows 20 records sorted again by name but this time it will start from the 50th record.
So basically in this clause (LIMIT start, count) ‘start’ specify the starting record and ‘count’ specifies how many records to show.

Next thing to do is to make a PHP file called pagination.php which will show the first 20 records from our table. The code below selects and then prints the data in a table.

<?php 
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * 20;
$sql = "SELECT * FROM students ORDER BY name ASC LIMIT $start_from, 20";
$rs_result = mysql_query ($sql, $connection);
?>
<table>
<tr><td>Name</td><td>Phone</td></tr>
<?php
while ($row = mysql_fetch_assoc($rs_result)) {
?>
<tr>
<td><? echo $row["Name"]; ?></td>
<td><? echo $row["PhoneNumber"]; ?></td>
</tr>
<?php
};
?>
</table>


Now, when you open pagination.php in your web browser you will see table showing the first 20 records from your ‘students’ table.

The first 2 lines of the above code

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$start_from = ($page-1) * 20;


are used to create a $start_from variable depending on the page that we want to view. Later you will see that we will pass a “page” value using the URL (e.g. pagination.php?page=2) to go to different pages. Next we need to find out the total amount of records in our table and the number of pages that we will need. To do this we run another query using COUNT() function.

$sql = "SELECT COUNT(Name) FROM students"; 
$rs_result = mysql_query($sql,$connection);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];


The $total_records is now equal to the number of records that we have in our database, in our case 90. We have 20 records per page so the total number of pages that will be needed is 5 (4 pages with 20 records and last page will have 10 records).

Calculating the amount of pages needed using PHP can be done using ceil() function.

$total_pages = ceil($total_records / 20);


We divide the total number of records by records per page and then the ceil() function will round up the result. Now we have 2 new variables - $total_records equal to 90 and $total_pages equal to 5.
To print page numbers and associate URLs to each number we will use for() cycle.

<?php 
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='pagination.php?page=".$i."'>".$i."</a> ";
};
?>


Above code will print numbers from 1 to 5 and for each number will create different link.
pagination.php?page=1
pagination.php?page=2
pagination.php?page=3
pagination.php?page=4
pagination.php?page=5
as you can see each link passes different page value which is used in the SELECT query above.

At the end you should have a file like this (remember to add the MySQL connection string):

<?php 
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * 20;
$sql = "SELECT * FROM students ORDER BY name ASC LIMIT $start_from, 20";
$rs_result = mysql_query ($sql,$connection);
?>
<table>
<tr><td>Name</td><td>Phone</td></tr>
<?php
while ($row = mysql_fetch_assoc($rs_result)) {
?>
<tr>
<td><? echo $row["Name"]; ?></td>
<td><? echo $row["PhoneNumber"]; ?></td>
</tr>
<?php
};
?>
</table>
<?php
$sql = "SELECT COUNT(Name) FROM students";
$rs_result = mysql_query($sql,$connection);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / 20);

for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='pagination.php?page=".$i."'>".$i."</a> ";
};
?>


This pagination.php file will print a table with maximum 20 records per page and at the bottom 5 page numbers each pointing to a page showing different 20 records.

Do not forget that for a small fee I can add pagination to all your PHP files. Let me know if you need help with this and I will give you a quote.

Do you know PHP / HTML / CSS / JS well?

Write tutorial on a topic you are good in and become a trusted PHP jabber! Share your experience with millions of other webmasters visiting our website. Contact us for more information how to become a contributor.

160 Replies to "PHP / MySQL select data and split on pages"

Rohit Ranjan March 15, 2015 at 7:21 pm | Reply

0

There seems to be some error in the last echo statement....
cha March 4, 2015 at 11:51 am | Reply

+1

thanks .. ur so awesome !!
raj mohammed February 16, 2015 at 4:33 pm | Reply

0

I written all codes but when click page number it will show error like this




Object not found!

The requested URL was not found on this server. The link on the referring page seems to be wrong or outdated. Please inform the author of that page about the error.

If you think this is a server error, please contact the webmaster.

Error 404

localhost
Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.6.3




and my coding is





<div id="section">
<?php
include 'admin/connect.php';

if(isset($_GET['page'])) {
$page = $_GET['page'];
}
else {
$page = 3;
};
$start_from = ($page-1) * 3;

$select_post = "SELECT * FROM `posts` order by `post_id` desc LIMIT 0, 3";

$run_posts = mysql_query($select_post);

while($rows=mysql_fetch_array($run_posts)){
$post_id = $rows['post_id'];
$post_title = $rows['post_title'];
$post_date = $rows['post_date'];
$post_auther = $rows['post_auther'];
$post_image = $rows['post_image'];
$post_content = substr($rows['post_content'], 0, 50);

?>
<div class="post">
<h2><?php echo $post_title; ?> </h2>

<p align="left"> Published On: <b> <?php echo $post_date; ?> </b></p>

<center> <img src="images/<?php echo $post_image; ?>" width="160" height="160"> </center>
<div id="spec">
<p align ="justify"> <b>Specifications of item :</b><br> <?php echo $post_content; ?>... <a href="page.php"> Read more</a></p>
</div>
<p align="right"> Published By: <b> <?php echo $post_auther; ?> </b></p>
</div>
<?php }

$sql = "SELECT COUNT(`post_id`) FROM `posts`";
$rs_result = mysql_query($sql, $connect);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / 3);

for ($i=1; $i<=$total_pages; $i++) {
echo '<a href="content.php?page=".$i."">'.$i.'</a> ';
};
?>
</div>
gerg January 23, 2015 at 7:51 pm | Reply

0

i went to retriv data from data base and display in the retrived data on an other page
how i do?
mohammed asharudeen October 24, 2014 at 2:12 pm | Reply

+2

well done...
ajith October 14, 2014 at 6:57 pm | Reply

0

hi, i have new question
consider 100 records per
in the printing concept i want to print the 25 record data per page in php mysql . the rest will be printed in next page and .... please help me..
pravin dabhi October 14, 2014 at 2:55 pm | Reply

+4

this is amazing when you create dynamic image gallery...


<?php
include_once("connect.php");
//$v=mysql_query("select * from gallery limit 12");

?>

<!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>Welcome to 3-D Manufacturing, Inc</title>
<link type="text/css" rel="stylesheet" href="css/style.css" />
<style>
.left_column
{
float:left;
width: 30%;
padding: 10px;
}

.middle_column
{
float:left;
width: 30%;
padding: 10px;
}

.right_column
{
float:right;
width: 30%;
padding: 10px;
}
</style>
</head>

<body>
<div>
<?php
include("common_include/Header.php");
?>
</div>
<div class="mid_bg">
<div class="margin">
<div class="mid_main">
<div class="mid">
<div class="mid_left">
<?php
include("common_include/left.php");
?>
<div class="mid_rig">
<div class="rig_tit">Gallery </div>
<div class="gallery_main">
<div class="rig_block">
<?php

if (isset($_GET["page"]))
{ $page = $_GET["page"]; }
else { $page=1; };
$start_from = ($page-1) * 12;
$sql = "SELECT * FROM gallery ORDER BY id ASC LIMIT $start_from, 12";
$rs_result = mysql_query ($sql);

$num_records = mysql_num_rows($rs_result);
// loop through the huds
$count=0;
while ($row = mysql_fetch_array($rs_result)){
$count ++;
if ($item_num % 3 == 0){?>
<div class="left_column"> <?php }
// If its the second column of 3 write the div middle_column class
else if ($item_num % 3 == 1){?>
<div class="middle_column"> <?php }
// If its the second column of 3 write the div right_column class
else if ($item_num % 3 == 2){?>
<div class="right_column"> <?php } ?>
<div class="box_img"><a href="#">
<img alt="" src="gallary_image/<?=$row['image_path'];?>" height="200px" width="200px">
</a> </div>
<div class="box_tit">Test<?=$count;?></div>
</div> <?php
$item_num ++;
}
$sql = "SELECT COUNT(name) FROM gallery";
$rs_result = mysql_query($sql);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / 12);
?>

</div>
<div class="pagi">
<?php
for ($i=1; $i<=$total_pages; $i++) {
echo "<a href='gallery.php?page=".$i."'>".$i."</a> ";

};
?>

</div>
</div>
</div>
</div>
</div>
<?php
include("common_include/Footer.php");
?>

almera October 14, 2014 at 7:01 am | Reply

+1

hi,

I have a problem. the problem is an error in the display of user exit. I do not know how to solve. error out is a string (128) "SELECT * FROM` WHERE are changeStatus` = 'Approved' AND `mark` = 'JBG' ORDER BY DESC LIMIT 0 id_tempah, 10"

any idea how to solve it

juliusmh March 9, 2015 at 7:56 am

0

Use $id_tempah.. It seens like your select statement ist wrong.. Anyone would need more code to answer.
sswonderboy October 12, 2014 at 3:30 pm | Reply

0

Niels ur sloution is here *wink*..


//ur error
$sqlget = "SELECT * FROM artikel, images LIMIT 0, 20";

//ur solution
$sqlget = "SELECT * FROM artikel, images LIMIT $start_from, 20";


u use LIMIT offset to be '0' so d files keep loading from 0 per page ... Whereas ... Ur LIMIT offset ought to be ur variable $start_from as calcuted from d code already... #dts it .. Make sure u go through ur codes @least thrice before panicing *wink*
poornima August 19, 2014 at 2:06 pm | Reply

0

i have given above code it works fine ...but for each page alignment is taking different width size is not looks good..

pls tel me how to set same table td size for all pages ...
pravin dabhi October 14, 2014 at 3:04 pm

+3

it is good to use div tag rather then table ..
use following code and paste in your website porson where u display dynamic images...
if it useful contact my email...



<?php
include_once("connect.php");
//$v=mysql_query("select * from gallery limit 12");

?>

<!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>Welcome to 3-D Manufacturing, Inc</title>
<link type="text/css" rel="stylesheet" href="css/style.css" />
<style>
.left_column
{
float:left;
width: 30%;
padding: 10px;
}

.middle_column
{
float:left;
width: 30%;
padding: 10px;
}

.right_column
{
float:right;
width: 30%;
padding: 10px;
}
</style>
</head>

<body>
<div>
<?php
include("common_include/Header.php");
?>
</div>
<div class="mid_bg">
<div class="margin">
<div class="mid_main">
<div class="mid">
<div class="mid_left">
<?php
include("common_include/left.php");
?>
</div>
</div>
</div>
<?php
include("common_include/Footer.php");
?>


Please be polite and helpful and do not spam or offend others. We promise you will be treated the same way.

Log in your free account or if you still haven't joined you can create your free account now.

Posting tip:
if you use code in your comments please put it in these tags [php], [sql], [css], [js]
PHP code example: [php] echo date("Y-m-d"); [/php]

Thank you,
~ PHPJabbers team ~