Limiting number of records per page

Now let us start with some variable settings to be used in this page. This code can be used with different page name so we can set the page name in a variable so the links at the end of the records display table will link to the same page

$page_name="php_paging.php"; // If you use this code with a different page ( or file ) name then change this

We will be collecting the values for variables by using query string & collect them by GET method. So we need to check these data before using inside our script and particularly using them in accessing tables. Data sanitization is required as hackers can exploit this to enter malicious quires. Read more on database security at our MySQL security section.

As we expect numeric data only against the variable $start, so we will check by using is_numeric PHP function.

$start=$_GET['start'];// To take care global variable if OFF
if(strlen($start) > 0 and !is_numeric($start)){
echo "Data Error";

Now we will set some more variables which will check from which record to start and up to which record we will display. We will be using SQL limit command to do this. We will set the variable $limit to the number of records per page to be displayed. Ten records per page is a standard way of displaying and you can change this any value. 

$eu = ($start - 0);
$limit = 10; // No of records to be shown per page.
$this1 = $eu + $limit;
$back = $eu - $limit;
$next = $eu + $limit;

We have to find out the total number of records exists in our table with the condition specified in the query. Based on this number we can break the pages

$nume = $dbo->query("select count(id) from student")->fetchColumn();

Now let us do some formatting and display the table headers. We have used style sheet you can edit that to match your requirments

echo "<TABLE class='t1'>";
echo "<tr><th>ID</th><th>Name</th><th>Class</th><th>Mark</th></tr>";

Now let us start collecting the records from the table based on the starting and ending marks decided by the variables above. We will use SQL limit command to manage the starting and ending location of the records and that will break the records into pages.

$query=" SELECT * FROM student limit $eu, $limit ";

The above function will apply the query to MySQL database and if any error is there will print out. Now we will display the records returned by MySQL table inside rows of a formatted table. The header of the table is already displayed above.

foreach ($dbo->query($query) as $row) {
$m=$i%2; // required for alternate color of rows matching to style class
$i=$i+1; // increment for alternate color of rows

echo "<tr class='r$m'><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td><td>$row[mark]</td></tr>";
echo "</table>";

We have displayed the records inside the rows of the table and you can see we have used the foreach loop along with php array to display the records.
Displaying records per Page
Now let us go to the bottom of the page where we will be displaying the links to different part of the page with next and previous link to navigate. Here we will use different if conditions to check and display the links.
If our variable $back is equal to 0 or more then only we will display the link to move back

echo "<table align = 'center' width='50%'><tr><td align='left' width='30%'>";
if($back >=0) {
print "<a href='$page_name?start=$back'><font face='Verdana' size='2'>PREV</font></a>";

Let us display the page links at center. We will not display the current page as a link and we will give it red color with a higher size font

echo "</td><td align=center width='30%'>";
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i'><font face='Verdana' size='2'>$l</font></a> ";
else { echo "<font face='Verdana' size='4' color=red>$l</font>";} /// Current page is not displayed as link and given font color red

Now let us check for the NEXT link at the right side on our condition and accordingly display. If we are in the last page then we will not display the NEXT link

echo "</td><td align='right' width='30%'>";
if($this1 < $nume) {
print "<a href='$page_name?start=$next'><font face='Verdana' size='2'>NEXT</font></a>";}
echo "</td></tr></table>";

Improvement to Script

Some time we don't want to display any link or the simple text saying page number 1 if there are less records and paging is not required. For example if we have total 18 records( $nume) and we have set the value of $limit ( number of records per page ) to 10 records then there is no point in displaying any page number at the bottom. So we will check this by using one if condition and if true then only we will execute all bottom navigational links. Like this .

if ( $limit < $nume) {
// keep the total script block of bottom links here

PHP paging Part I

Demo of Basic Paging script

PHP paging customized paging script demo II >>

Customized sorting in paging script demo III >>

download Php Paging  Script

Subscribe to our mailing list

* indicates required
Subscribe to plus2net
Visitors Rating
Your Rating




Thank you, but where i use the code $page_name="php_paging.php"

Post Comment This is for short comments only. Use the forum for more discussions.

Subscribe to our mailing list

* indicates required
Subscribe to plus2net
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2017 All rights reserved worldwide Privacy Policy Disclaimer