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.

Database

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).

  1. <html>  
  2. <head>  
  3. <title>Paging in PHP</title>  
  4. </head>  
  5. <body>  
  6. <?php  
  7. $serverName = "local";  
  8. $connectionInfo = array("UID"=>"sa","PWD"=>"urPwd","Database"=>"Practice");  
  9. $con = sqlsrv_connect( $serverName$connectionInfo);  
  10.  if$con === false )  
  11.  {  
  12.      echo "Could not connect.\n";  
  13.      die( print_r( sqlsrv_errors(), true ));  
  14.  }  
  15. </body>  
  16. </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. 

  1. <html>  
  2. <head>  
  3. <title>Paging in PHP</title>  
  4. </head>  
  5. <body>  
  6. <?php  
  7. $serverName = "local";  
  8. $connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");  
  9. $con = sqlsrv_connect( $serverName$connectionInfo);  
  10.  if$con === false )  
  11.  {  
  12.      echo "Could not connect.\n";  
  13.      die( print_r( sqlsrv_errors(), true ));  
  14.  }  
  15. $str= sqlsrv_query($con"SELECT * FROM Paging");  
  16.   while($row= sqlsrv_fetch_array($str))  
  17.    {  
  18.         echo $row["ID"]. "    " . $row["Name"];  
  19.         echo "<br/>";  
  20.    }  
  21. ?>  
  22. </body>  
  23. </html>  
pagingRowData

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.

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

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.

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

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.

  1. if(!isset($_GET["page"]))  
  2. {  
  3.     $page="1";   
  4. }  
  5. else{  
  6.     $page=$_GET["page"];  
  7. }  
  8.    
  9. if($page=="" || $page=="1")  
  10. {  
  11.     $page1=0;  
  12. }  
  13. else{  
  14.     $page1= (($page*5)-5);  
  15. }  
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 .   

 

  1. <?php  
  2. $serverName = "local";  
  3. $connectionInfo = array("UID"=>"sa","PWD"=>"urPWD","Database"=>"Practice");  
  4. $con = sqlsrv_connect( $serverName$connectionInfo);  
  5.   if$con === false )  
  6.   {  
  7.       echo "Could not connect.\n";  
  8.       die( print_r( sqlsrv_errors(), true ));  
  9.   }  
  10.   if(!isset($_GET["page"]))  
  11.   {  
  12.       $page="1";  
  13.   }  
  14.   else{  
  15.       $page=$_GET["page"];  
  16.   }  
  17. //$page=$_GET["page"];  
  18.  if($page=="" || $page=="1")  
  19.   {  
  20.       $page1=0;  
  21.   }  
  22.   else{  
  23.       $page1= (($page*5)-5);  
  24.   }  
  25. $str= sqlsrv_query($con"SELECT * FROM Paging ORDER BY ID  OFFSET  $page1 ROWS  FETCH NEXT 05 ROWS ONLY");  
  26.   while($row= sqlsrv_fetch_array($str))  
  27.   {  
  28.       echo $row["ID"]. "    " . $row["Name"];  
  29.       echo "<br/>";  
  30.   }  
  31. // count no of records;  
  32. $str1= sqlsrv_query($con"SELECT * FROM Paging",array(), array"Scrollable" => SQLSRV_CURSOR_KEYSET ));  
  33. $count= sqlsrv_num_rows($str1);  
  34. $a$count/5;  
  35. $a=ceil($a);  
  36. echo "<br/>" ;  
  37. for($i=1;$i<=$a$i++)  
  38. {  
  39.    ?> <a href="Paging.php?page=<?php echo $i ?>" style=" text-decoration:none; " ><?php  echo $i"     " ?></a><?php   
  40. }  
  41. ?>  
Output

output1

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

Next Recommended Readings