One of the most common tasks of any web application involves allowing a user to ‘page’ through a large amount of data. In other words, given a large result set, the task is to present the user with a manageable subset and allow him/her to navigate through the result set by viewing ‘pages’ (i.e. subsets) of data. In this post, I’ll demonstrate two ways to do this with the existing functionality of the SQL Server Driver for PHP. Also note that the strategies discussed here can be applied to paging data regardless of the programming language or database.
Before diving into the details of the two examples, I want to point out two assumptions I have made:
1) Data on the server is dynamic (i.e. data may be updated between our query executions).
2) We want the most recent data with each page view.
With these assumptions in mind, the examples below demonstrate two similar strategies for paging. If your paging needs do not share these assumptions, you may want to consider another strategy (such as executing queries against an ordered temporary table or bringing a complete result set into client memory) that meets your needs.
The first example allows users to navigate through data with “Previous Page” and “Next Page” links, while the second example allows users to navigate directly to any page.
Note that both examples use the AdventureWorks database and that I’ve removed some of the connection code and error handling for the sake of brevity. Complete scripts are attached to this post if you want to see the code in-tact.
“Previous Page” and “Next Page” Navigation
This example presents users with ten products at a time and allows him/her to navigate through pages with “Previous Page” and “Next Page” links. The basic strategy is to retrieve an “extra” row (an 11th row in my example) and use it as a flag for enabling the “Next Page” link. This avoids multiple trips to the database to display one page of data.
This first section of code sets the number of rows to display on a page and executes a query that returns and “extra” row. The query uses the ROW_NUMBER() function and OVER clause in the context of a common table expression to produce an efficient query. Common table expressions were added to T-SQL in SQL Server 2005 and are extremely helpful for paging scenarios such as this, along with scenarios where you want to execute recursive queries or want to reference a complex result multiple times within a single statement. The code for connecting to the database has been removed and the code assumes “lowRowNum” and “highRowNum” values are being passed in the $_REQUEST variable:
/* Set the number of results to display on each page. */
$rowsPerPage = 10;
/* Order target data by ID and select only items
(by row number) to display on a given page.
The query asks for one "extra" row as an
indicator that another page of data exists. */
$tsql = "WITH Products AS
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS
RowNumber,
Name,
ProductNumber
FROM Production.Product)
SELECT * FROM Products
WHERE RowNumber BETWEEN ? AND ? + 1";
/* Determine which row numbers to display. */
if(isset($_REQUEST['lowRowNum']) &&
isset($_REQUEST['highRowNum']))
{
$lowRowNum = $_REQUEST['lowRowNum'];
$highRowNum = $_REQUEST['highRowNum'];
}
else
$lowRowNum = 1;
$highRowNum = $rowsPerPage;
/* Set query parameter values. */
$params = array($lowRowNum, $highRowNum);
/* Execute the query. */
$stmt = sqlsrv_query($conn, $tsql, $params);
/* Retrieve one row to see if there is any data. */
$row = sqlsrv_fetch_array($stmt);
if($row === false)
echo "Error in fetching row.";
die( print_r( sqlsrv_errors(), true));
elseif($row[0] == 0) /* Special case of no data returned. */
echo "No data returned.";
else /* A row was retrieved! */
/* Set the number of rows that have been retrieved. */
$rowsRetrieved = 1;
/*Display table header. */
print("<table border='1px'>
<tr>
<td>Row Number</td>
<td>Product Name</td>
<td>Product ID</td>
</tr>");
/* Display the retrieved rows while we haven't
displayed all of $rowsPerPage and there is
another row to display. */
do
print("<tr>
<td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td>
$rowsRetrieved++;
} while ($rowsRetrieved <= $rowsPerPage &
$row = sqlsrv_fetch_array($stmt));
/* Close table. */
print("</table></br></br>");
/*If there are previous results, display the
Previous Page link.*/
if($lowRowNum > 1)
$prev_page_high = $lowRowNum;
$prev_page_low = $lowRowNum - $rowsPerPage;
$prevPage = "?lowRowNum=$prev_page_low".
"&highRowNum=$prev_page_high";
print("<a href=$prevPage>".
"Previous Page</a> ");
/* If there are more results, display the Next Page link.
We know there are more results if the last call to
sqlsrv_fetch_array returned a row (the "extra" row).
*/
if($row != false)
$next_page_low = $highRowNum;
$next_page_high = $highRowNum + $rowsPerPage;
$nextPage = "?lowRowNum=$next_page_low".
"&highRowNum=$next_page_high";
print("<a href=$nextPage>Next Page</a>");
Direct Page Navigation
The next example presents the user with a series of page links that allows the user to click to any page of the results he/she wants to see (without having to click “Next Page” or “Previous Page” several times). This strategy requires executing two queries: one to retrieve the number of rows that are available, and one to actually retrieve the rows that will be displayed.
The following code executes a query to see how many rows of data are on the server, then populates the page with links that will query for $rowsPerPage at a time. Note that this query is executed every time the page is loaded, which is desirable if the data on the server is changing frequently. Also note that the query leverages the SQL Server COUNT function to query for the number of rows that are available without actually querying for the row data.
/* Define a query to get the number of rows on the server.
This query doesn't actually retrieve rows, it just
retrieves the number of rows that are available. */
$tsql = "SELECT COUNT(ProductNumber)
FROM Production.Product";
$stmt = sqlsrv_query($conn, $tsql);
if($stmt === false)
echo "Error in query execution.";
/* Get the number of rows returned. */
$rowsReturned = sqlsrv_fetch_array($stmt);
if($rowsReturned === false)
echo "Error in retrieving number of rows.";
elseif($rowsReturned[0] == 0)
echo "No rows returned.";
/* Display page links. */
$numOfPages = ceil($rowsReturned[0]/$rowsPerPage);
for($i = 1; $i<=$numOfPages; $i++)
$pageNum = "?pageNum=$i";
print("<a href=$pageNum>$i</a> ");
echo "</br></br>";
Now we need to retrieve and display the data. We’ll use the same query as in the first example to retrieve data. The only tricky part here is in determining what the query parameters are. I’m determining the $highRowNum value by multiplying the page clicked by the $rowsPerPage (25, in my case). The $lowRowNum value is determined by subtracting $resultsPerPage from $highRowNum. (I suppose I could have included this logic in the code above when printing the series of links to pages, but one way doesn’t seem to have significant advantages over the other.)
(by row number) to display on a given page. */
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID)
AS RowNumber,
WHERE RowNumber BETWEEN ? AND ?";
if(isset($_REQUEST['pageNum']))
$highRowNum = $_REQUEST['pageNum'] * $rowsPerPage;
$lowRowNum = $highRowNum - $rowsPerPage;
$stmt2 = sqlsrv_query($conn, $tsql, $params);
if($stmt2 === false)
/* Print table header. */
/* Display results. */
while($row = sqlsrv_fetch_array($stmt2) )
print("</table>");
And that’s it! Two similar strategies for paging with different UI’s. I hope this has been a useful post – please let us know what you think. For more information about the SQL Server Driver for PHP, see the product documentation.
Thanks.
Brian Swan
Programming Writer, SQL Server Driver for PHP
There are more important assumptions made which were not pointed out;-
1) The data has a non-unique incrementing key.
2) You dont want to sort by any useful fields, only by ID. This is not a real-world situation.
Also how would you page through a complex query that joins several tables? (none of the articles elsewhere on MSDN cover this either... )
Nice article, but so basic that it can only be useful in the most rudimentary of programming situations.
jezjones-
Thanks for the feedback. The examples in the post were kept simple to (hopefully) keep the focus on the paging strategy. By tweaking the $tsql that you execute, you can sort by fields other than ID, and you can page through a JOIN.
For example, this query does not sort by ID (and it works in the posted code with only a change to the $params array):
(SELECT ROW_NUMBER() OVER(ORDER BY ListPrice)
AS RowNumber, Name, ListPrice
FROM Production.Product
WHERE Name LIKE '%' + ? + '%'
AND ListPrice > 0.0)
And, this JOIN works with the code in the post:
(SELECT ROW_NUMBER() OVER(ORDER BY OrderQty)
AS RowNumber, OrderQty, Name
FROM Sales.SalesOrderDetail AS s
JOIN Production.Product AS p
ON s.ProductID = p.ProductID)
Yes, these are still simple (even contrived) examples. But, I think they demonstrate that the paging strategies posted are viable for the situations you mention. Do you agree?
I'm not sure I agree with your assumption about the script using a non-unique incrementing key. Can you elaborate?
-Brian