Paging Data with the SQL Server Driver for PHP
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);
Next, we need to display the retrieved data - if there is any! This code retrieves one row of data to make sure some data was returned. (We are working on features in v1.1 of the driver that will make this easier.) Then we display a row of data at a time until we have displayed $rowsPerPage rows, or until there are no more rows to display. If we have displayed $rowsPerPage rows and there is another row of data (that “extra” row), we display the “Next Page” link:
/* 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>
</tr>");
$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.
/* Set the number of results to display on each page. */
$rowsPerPage = 10;
/* 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";
/* Execute the query. */
$stmt = sqlsrv_query($conn, $tsql);
if($stmt === false)
{
echo "Error in query execution.";
die( print_r( sqlsrv_errors(), true));
}
/* Get the number of rows returned. */
$rowsReturned = sqlsrv_fetch_array($stmt);
if($rowsReturned === false)
{
echo "Error in retrieving number of rows.";
die( print_r( sqlsrv_errors(), true));
}
elseif($rowsReturned[0] == 0)
{
echo "No rows returned.";
}
else
{
/* 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.)
/* Order target data by ID and select only items
(by row number) to display on a given page. */
$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 ?";
/* Determine which row numbers to display. */
if(isset($_REQUEST['pageNum']))
{
$highRowNum = $_REQUEST['pageNum'] * $rowsPerPage;
$lowRowNum = $highRowNum - $rowsPerPage;
}
else
{
$lowRowNum = 1;
$highRowNum = $rowsPerPage;
}
/* Set query parameter values. */
$params = array($lowRowNum, $highRowNum);
/* Execute the query. */
$stmt2 = sqlsrv_query($conn, $tsql, $params);
if($stmt2 === false)
{
echo "Error in query execution.";
die( print_r( sqlsrv_errors(), true));
}
/* Print table header. */
print("<table border='1px'>
<tr>
<td>Row Number</td>
<td>Product Name</td>
<td>Product ID</td>
</tr>");
/* Display results. */
while($row = sqlsrv_fetch_array($stmt2) )
{
print("<tr>
<td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td>
</tr>");
}
/* Close table. */
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
Comments
- Anonymous
April 03, 2009
There are more important assumptions made which were not pointed out;-
- The data has a non-unique incrementing key.
- 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.
- Anonymous
April 03, 2009
The comment has been removed