Paging Using PHP

Rajeev Ranjan
10y
8.7k
0
0

Article

Introduction

This article explains how to handle a number of records using paging. Whenever we think of paging we think of a fixed number of files being divided into a number of pages, something like in computer architecture. Computer architecture of paging comes under memory management, where the processor divides the fixed size of a page to map the physical memory or hard disk or any any other storage device. In this article we will see the concepts of paging or something like that.

Use the following procedure for paging in PHP:

Create a table in SQL Server
Create a connection with SQL Server in your PHP application
Display the table data
Apply the paging logic
Output

Create a table in SQL Server

Creating a table in SQL Server is a very simple task. So in this project I created a table named "paging" and filled in some dummy data inside that table.

Create connection with SQL Server in your PHP application

This is the next phase, in this phase we need to write PHP code and make a connection with the SQL Server or any other database server. For making a connection with SQL Server using PHP, we should use Microsoft Drivers API and for the connection the sqlsrv_connect() function is used and in this function we should pass the server name and database name with the credentials (user name and password).

<html>
<head>
<title>Paging in PHP</title>
</head>
<body>
<?php
$serverName = "local";
$connectionInfo = array("UID"=>"sa","PWD"=>"urPwd","Database"=>"Practice");
$con = sqlsrv_connect( $serverName, $connectionInfo);
if( $con === false )
{
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true ));
}
</body>
</html>

Display the table data

After connecting with SQL Server, now we display all the table data in PHP. For displaying the table data use the following code snippet.

<html>
<head>
<title>Paging in PHP</title>
</head>
<body>
<?php
$serverName = "local";
$connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");
$con = sqlsrv_connect( $serverName, $connectionInfo);
if( $con === false )
{
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true ));
}
$str= sqlsrv_query($con, "SELECT * FROM Paging");
while($row= sqlsrv_fetch_array($str))
{
echo $row["ID"]. " " . $row["Name"];
echo "<br/>";
}
?>
</body>
</html>

Apply the paging logic

Step 1 For the paging logic, first of all we should pass a query that displays only five (5) records at a time, for that we should write a query like:
"SELECT * FROM Paging ORDER BY ID OFFSET 0 ROWS FETCH NEXT 05 ROWS ONLY"
But in the case of MySQL, you should use:
"SELECT * FROM Paging LIMIT 0,5"

Now count the number of records and divide those records into 5, the code snippet is below.

$str1= sqlsrv_query($con, "SELECT * FROM Paging",array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$count= sqlsrv_num_rows($str1);
$a= $count/5;
$a=ceil($a);
echo "<br/> number of recoreds - " .$count;

Step 2 Now in the next phase we should make a hyperlink and bind all the data using a query string.
We get the data from the query string as a page variable. Paging.php?page=<?php echo $i ?>
This code tells us to "page", it is the first query string value and $i assigns some values in the page variable.

for($i=1;$i<=$a; $i++)
{
?> <a href="Paging.php?page=<?php echo $i ?>" style=" text-decoration:none; " ><?php echo $i. " " ?></a><?php
}

Step 3 From now on we will be getting the value and passing that value into the SQL query for proper output.
So, for getting the value of the query string , we use $_GET. $_GET is a supergloabal or automatic gloabal variable. This simply means that it is available in all scopes throughout the script. The following code explains how to consume the query string value.

if(!isset($_GET["page"]))
{
$page="1";
}
else{
$page=$_GET["page"];
}
if($page=="" || $page=="1")
{
$page1=0;
}
else{
$page1= (($page*5)-5);
}

Step 4 The next and final stage of implementation of the logic. In this section we use very simple logic and the logic is (($page*5)-5)
If we look at this code carefully in every cycle it will return a multiple of 5 as output. Like if the value of $page is 0 then the output is 0 and when the value of $page is 1 then the output will be 5, the next time $page is 2 , output will be 10 and so on. This output value we passed into the $page1 (another variable). And this $page1 variable is passed into the query .

<?php
$serverName = "local";
$connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");
$con = sqlsrv_connect( $serverName, $connectionInfo);
if( $con === false )
{
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true ));
}
if(!isset($_GET["page"]))
{
$page="1";
}
else{
$page=$_GET["page"];
}
//$page=$_GET["page"];
if($page=="" || $page=="1")
{
$page1=0;
}
else{
$page1= (($page*5)-5);
}
$str= sqlsrv_query($con, "SELECT * FROM Paging ORDER BY ID OFFSET $page1 ROWS FETCH NEXT 05 ROWS ONLY");
while($row= sqlsrv_fetch_array($str))
{
echo $row["ID"]. " " . $row["Name"];
echo "<br/>";
}
// count no of records;
$str1= sqlsrv_query($con, "SELECT * FROM Paging",array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$count= sqlsrv_num_rows($str1);
$a= $count/5;
$a=ceil($a);
echo "<br/>" ;
for($i=1;$i<=$a; $i++)
{
?> <a href="Paging.php?page=<?php echo $i ?>" style=" text-decoration:none; " ><?php echo $i. " " ?></a><?php
}
?>

Output

output1

Summary

In this article we learned how to use paging in PHP.

Up Next

Ebook Download

View all

Printing in C# Made Easy

Read by 10.8k people

Download Now!

Learn

View all