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

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

rohit July 16, 2014 at 7:07 pm | Reply

0

Hi everyone. ive implemented the pagination successfully but now i need a search button in my pagination code through which i can jump to any page in my database by just entering the page number. Reply as soon as possible
Thanks in advance.
this is my pagination code:
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';

$conn = mysql_connect($dbhost, $dbuser, $dbpass );
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('form1');
$rowsperpage = 2; // how many items per page
$range = 3;// how many pages to show in page link

if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage']))
{

// cast var as int
$currentpage = (int) $_GET['currentpage'];
}
else
{
// default page num
$currentpage = 1;
} // end if
// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;

$sql = "SELECT COUNT(*) FROM quotes";
$result = mysql_query($sql, $conn) or die(mysql_error());
$r = mysql_fetch_row($result);
$numrows = $r[0];
$totalpages = ceil($numrows / $rowsperpage);

if ($currentpage > $totalpages)
{
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1)
{
// set current page to first page
$currentpage = 1;
} // end if

//PUT YOUR SQL CODE HERE AS WELL AS EVERYTHING THAT SHOULD BE ON THE PAGE.
//THE FOLLOWING GOES BELOW AT THE BOTTOM OF YOUR CONTENT:
$query2=" SELECT * FROM quotes ";
$result2=mysql_query($query2,$conn);
echo mysql_error();
$nume=mysql_num_rows($result2);
$bgcolor="#f1f1f1";
echo "<table width=50% align=center cellpadding=0 cellspacing=0> <tr>";
echo "<td bgcolor='dfdfdf' > <font face='arial,verdana,helvetica' color='#000000' size='4'>id</font></td>";
echo "<td bgcolor='dfdfdf' > <font face='arial,verdana,helvetica' color='#000000' size='4'>quotes</font></td>";
echo "<td bgcolor='dfdfdf'> <font face='arial,verdana,helvetica' color='#000000' size='4'>author</font></td></tr>";
$limit = 6;
$query=" SELECT * FROM quotes limit $offset, $limit ";
$result=mysql_query($query,$conn);
echo mysql_error();
while($noticia = mysql_fetch_array($result))
{
if($bgcolor=='#f1f1f1')
{
$bgcolor='#ffffff';
}
else{$bgcolor='#f1f1f1';}
echo "<tr >";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[id]</font></td>";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[quotes]</font></td>";
echo "<td align=left bgcolor=$bgcolor id='title'> <font face='Verdana' size='2'>$noticia[author]</font></td>";
echo "</tr>";
}
echo "</table>";
echo "<table align='center' width='50%'><tr><td align='left' width='30%'>";

$result = mysql_query("SELECT * FROM quotes", $conn);
$num_rows = mysql_num_rows($result);
if ($num_rows<1)
{
?>
 
<?php
}
ELSE
{
echo "Page ".$currentpage." of ".$totalpages."<br>";

if ($currentpage > 1)
{
// show << link to go back to page 1
echo " <a href='hogayamayb.php?currentpage=1'><<</a> ";
// get previous page num
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
echo " <a href='hogayamayb.php?currentpage=$prevpage'><</a> ";
} // end if

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++)
{
// if it's a valid page number...
if (($x > 0) && ($x <= $totalpages))
{
// if we're on current page...
if ($x == $currentpage)
{
// 'highlight' it but don't make a link
echo " [<b>$x</b>] ";
// if not current page...
}
else
{
// make it a link
echo " <a href='hogayamayb.php?currentpage=$x'>$x</a> ";
} // end else
} // end if
} // end for

// if not on last page, show forward and last page links
if ($currentpage != $totalpages)
{
// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page

echo " <a href='hogayamayb.php?currentpage=$nextpage'>></a> ";
// echo forward link for lastpage
echo " <a href='hogayamayb.php?currentpage=$totalpages'>>></a> ";
} // end if
} // end else
echo "</td></tr></table>";

?>
dr6yu July 11, 2014 at 10:39 am | Reply

0

Thanks!
muij uddin June 13, 2014 at 2:34 pm | Reply

+2

Nice pagination code. but it shows all the data from database but i want to sql data by id in multiple page..how can i do it please help me.
Niels June 3, 2014 at 2:53 pm | Reply

0

Thanks allot for this post, but I still have problems with it. I'm stuck on having only 1 page, but that's not possible because I have allot more rows then that.

This is my code:



<?php
include('connect-mysql.php');
if (!empty($_GET["page"])) {
$page = $_GET["page"];
} else {
$page=1;
};
$start_from = ($page-1) * 20;
$sqlget = "SELECT *
FROM artikel, images
LIMIT 0, 20
";

$sqldata = mysqli_query($dbcon, $sqlget) or die('error getting');

echo "<table>";
echo "<tr><th>A_ARTCODE</th><th>A_NUMMER</th><th>A_OMSCHRN</th><th>A_REFLEV</th><th>A_WINKEL</th><th>I_ARTCODE</th><th>I_FILE</th></tr>";

while($row = mysqli_fetch_array($sqldata)){
echo "<tr><td align='right'>";
echo $row['A_ARTCODE'];
echo "</td><td align='left'>";
echo $row['A_NUMMER'];
echo "</td><td align='left'>";
echo $row['A_OMSCHRN'];
echo "</td><td align='left'>";
echo $row['A_REFLEV'];
echo "</td><td align='right'>";
echo $row['A_WINKEL'];
echo "</td><td align='right'>";
echo $row['I_ARTCODE'];
echo "</td><td align='right'>";
echo $row['I_FILE'];
//echo "<img src='000000-001000".$row['I_ID']."' />";
echo "</td></tr>";
}

echo "</table>";

$sql = "SELECT COUNT(I_FILE) FROM images";
$rs_result = mysqli_query($dbcon, $sql);
$row = mysqli_fetch_row($rs_result) or die ("mysqli fetch row dies");
$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> ";
//};


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

};
?>


I hope someone can help me.
Andrew May 22, 2014 at 2:15 am | Reply

+2

See here any good solution :)


// START AICI
if (!empty($_GET["page"])) {
$page = $_GET["page"];
} else {
$page=1;
};
$start_from = ($page-1) * 5;
$sql = mysql_query("SELECT * FROM `news_list` ORDER BY `id` DESC LIMIT {$start_from}, 5");
while ($rowm = mysql_fetch_array($sql)) {
$ziua = date('d', $rowm['data']);
$luna = date('M', $rowm['data']);
$cutdesc = (strlen($rowm['description']) > 125) ? substr($rowm['description'],0,120).'...' :$rowm['description'];
echo '<ul class="news-items">
<li>
<div class="news-item-detail">
<a href="#" class="news-item-title">'.$rowm['name'].'</a>
<p class="news-item-preview">'.$cutdesc.'</p>
</div>
<div class="news-item-date">
<span class="news-item-day">'.$ziua.'</span>
<span class="news-item-month">'.$luna.'</span>
</div>
</li>
</ul>';
}

echo '</div> <!-- /widget-content -->
</div> <!-- /widget -->';
$sql = mysql_query("SELECT COUNT(`name`) FROM `news_list`");
$row = mysql_fetch_array($sql);
$total_records = $row[0];
$total_pages = ceil($total_records / 5);

//echo "< ";
//for ($i=1; $i<=$total_pages; $i++) {
// echo "<a href='./news-p".$i.".html'>".$i."</a> ";
//};
echo '<center>';
if($page > 1) {
echo " <a href='./news-p".($page-1).".html' class='btn btn-success'>«PREVIOUS</a> ";
}
for($i=1;$i<=$total_pages;$i++) {
if($i==$page) {
echo "<span class='btn btn-success'>".$i."</span> ";
} else {
echo "<a href='./news-p".$i.".html' class='btn btn-small btn-info'>".$i."</a> ";
}
}
if($page!=$total_pages) {
echo " <a href='./news-p".($page+1).".html' class='btn btn-success'>NEXT»</a> ";
}
echo '</center>';
// END AICI


Have fun!
vaidyamanishjoshi May 16, 2014 at 1:46 pm | Reply

+1

Can Anybody explain How To use Date From Database In pagination Instead of Number like
previous |1 | 2 | 3 | 4 | 5 |..... | Next

E.g. if There is a column lecture_date in mysql database containing dates like 2014-05-16, 2014-05-10, 2014-05-03 etc...Can we Display these Dates in Pagination Instead of Numbers ?
Like : -

Previous | 2014-05-03 | 2014-05-10 | 2014-05-16 | ..... | Next
Muktubek May 16, 2014 at 12:19 pm | Reply

0

Hi every one if you need help about php mysql i can help you this is my skype smtx_muktubek
khalid June 6, 2014 at 5:20 am

0

<?php
session_start();
if(!isset($_SESSION['aid'])){
header("Location:index.php");
}
else
$aid=$_SESSION['aid'];
?>
<html>
<head>
<title>ad categaries</title>
<link rel="stylesheet" href="sty.css" />
</head>
<body>
<div id="mwrapper">
<?php
include "header.php";
include "menubar.php";
?>
<div id="wrapper">
<?php
$host="localhost";
$un="root";
$pwd="";
$database="kkmart";
$link=mysql_pconnect($host,$un,$pwd);
mysql_select_db($database,$link);
$sv=$_REQUEST['sv'];
$sql="select * from products $sv,8;";
$result=mysql_query($sql);
$num=mysql_num_rows($result);
$totalpage=$num/8;
//echo $num;
?>
<table border="1">
<tr>
<th>PRODUCT PRICE</th><th>PRODUCT DESCRIPTION</th><th>PRODUCT PRICE</th><th>PRODUCT IMAGE</th><th>ACTION</th>
</tr>
<?php
while($row=mysql_fetch_array($result)) {
echo ("<tr><td>" . $row['p_name'] . "</td>" );
echo ( "<td>" . $row['p_desc'] . "</td>" );
echo ( "<td>" . $row['p_price']. ".00</td>" );
echo ( "<td><img src='../images1/" . $row['p_image'] . "' width='200' height='100' /></td>" );
echo ( "<td><a href='del_pro.php?pid=" . $row['p_id' ] . "'><img src='images/del1.png' width='40' height='25' /></a> | <a href='edit_pro_form.php?pid=" . $row['p_id' ] . "'><img src='images/edit.png' width='40' height='25' /></a></td></tr>");

}
mysql_close($link);
?>
</table>
<?php
for ($i=1; $i<=$totalpage; $i++) {
echo "<a href='pagination.php?sv=".$i."'>".$i."</a> ";
};
?>

<?php
include "footer.php";
?>
</div>
</body>
</html>


problem
paging code is not run
raja March 29, 2014 at 10:45 am | Reply

0

I have this error when i try to execute this code
Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, boolean given in C:xampphtdocspgs.php on line 32

code:
<?php

$con=mysqli_connect("localhost","root","","phantomfx"); // include your code to connect to DB.
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * 5;
//$sql = "SELECT * FROM assign ORDER BY id ASC LIMIT $start_from, 5";
$rs_result = mysqli_query ($con,"SELECT * FROM assign ORDER BY id ASC LIMIT $start_from, 5");

echo "<table>
<tr>
<td>Name</td>
<td>Project</td>

</tr>";

while ($row = mysqli_fetch_assoc($rs_result)) {

echo "<tr>";
echo "<td>".$row['username']."</td>";
echo "<td>".$row['shot']."</td>";
echo "</tr>";

}
echo "</table>";
//$sql = "SELECT COUNT(userame) FROM assign";
$result = mysqli_query($con,"SELECT COUNT(userame) FROM assign");
$row = mysqli_fetch_row($result);
$total_records = $row[0];
$total_pages = ceil($total_records / 5);

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

};

?>
xtreme April 24, 2014 at 2:46 pm

+2

userame -> username
Praveen March 24, 2014 at 11:39 am | Reply

+3

Sir is there is any way to use the created page in format 'previous page' '1' '2' '3' ......'250' 'last page' 'next page'
amin March 19, 2014 at 4:09 pm | Reply

+3

hi
tnx for your toturial
there is a bug in

if (isset($_GET["page"]))
the correct code is :
if (!empty($_GET["page"]))

because url could be "mysite/pagination.php?page="
and now isset return true and an error occur but using !empty return false and that is what we want

best reguards


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 ~