Limiting number of records per page

Now let us start with some variable settings to be used in the 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'];// Collect data from Query string
if(strlen($start) > 0 and !is_numeric($start)){
echo "Data Error";
exit;
}
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 requirements
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%'>";
$i=0;
$l=1;
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
$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($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
}


download Php Paging  Script

Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Asarudeen

    03-06-2014

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

    Post your comments , suggestion , error , requirements etc here





    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer