limiting number of records per page using PHP mysql paging script

SQL PHP HTML ASP JavaScript articles and free scripts to download

Custom Search

Limiting number of records per page

<< PHP paging Part I

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 check if the default page is loaded where no starting record is specified so we will start from first record.

$start=$_GET['start'];// To take care global variable if OFF
if(!($start > 0)) { // This variable is set to zero for the first page
$start = 0;
}

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.
$this = $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

$query2=" SELECT * FROM student ";
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);

Now let us do some formatting and display the table headers.

$bgcolor="#f1f1f1";
echo "<TABLE width=50% align=center cellpadding=0 cellspacing=0> <tr>";
echo "<td bgcolor='dfdfdf' > Name</td>";
echo "<td bgcolor='dfdfdf' > Class</td>";
echo "<td bgcolor='dfdfdf'> Mark</td></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 ";
$result=mysql_query($query);
echo mysql_error();

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.

while($noticia = mysql_fetch_array($result))
{
if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}
else{$bgcolor='#f1f1f1';}
echo "<tr >";
echo "<td align=left bgcolor=$bgcolor id='title'> $noticia[name]</td>";
echo "<td align=left bgcolor=$bgcolor id='title'> $noticia[class]</td>";
echo "<td align=left bgcolor=$bgcolor id='title'> $noticia[mark]</td>";
echo "</tr>";
}
echo "</table>";

We have displayed the records inside the rows of the table and you can see we have used the while loop along with php array to display the records. 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'>PREV</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%'>";
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i'>$l</a> ";
}
else { echo "$l";} /// Current page is not displayed as link and given font color red
$l=$l+1;

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($this < $nume) {
print "<a href='$page_name?start=$next'>NEXT</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

PHP paging Part II >>

PHP paging demo I >>

PHP paging customized paging script demo II >>

Customized sorting in paging script demo III >>

Download the ZIP file here php_paging.php

Participate in active forum on PHP MySQL paging script

Discuss this tutorial at forum

Further readings

Basics of Paging script using PHP MySQL
Limiting number of records per page: Codes : Part II
Sorting the records by column head hyperlinks in paging script
DEMO Sorting records on column head ( or field head)
Selection of number of records per page by user in Paging script
Demo of number of records from a drop down by user in paging script
Advance Php paging script
Tutorial on Advance Php paging script