How to Page Data with the SQL Server Drivers for PHP
Shortly after the 1.0 version of the SQL Server Driver for PHP was released, I posted on the team blog as about how to page data with the driver. In the v1.1 release of the driver, the team added some functionality that made paging data easier and more intuitive, but I never got around to a blog post that showed how to use the new functionality. So, with the final release of the 2.0 version of the driver coming soon, I thought it was high time I posted something about using the functionality that was added in the v1.1 driver (better late then never!). While I’m at it, I’ll include code that shows how to do paging with the PDO driver. (Complete scripts attached to this post.)
(The truth is that the procedural API (sqlsrv) in the v1.1 release is basically unchanged in the 2.0 release, so consider the sqlsrv code below applicable to the v1.1 release and later. The code below that uses the PDO interface only applies to the 2.0 release of the driver.)
The scenario I’ll address is the same as the one I addressed on the SQL Server Driver for PHP team blog, and the strategy I’ll use for paging results is also the same. Here’s how I introduced it in that post:
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.
With that in mind, I’ll look at two paging strategies (“previous and next page navigation” and “direct page navigation”) that differ from my previous post in their use of the sqlsrv_has_rows and sqlsrv_num_rows functions (which were introduced in version 1.1).
"Previous Page" and "Next Page" Navigation
By “Previous Page” and “Next Page” navigation, I mean enabling/disabling links that allow you to go to the previous or next page of data…something that looks like this:
At the heart of this strategy is a query that uses the SQL Server ROW_NUMBER() function. This essentially numbers the rows of a result set, which then allows us to select a page’s worth of rows by using the WHERE…BETWEEN clause. The following code gets the row numbers to be displayed from the $_GET array. Note that in the sqlsrv code the query is executed with the “Scrollable” => “keyset” option. In the PDO code, the query is executed with the PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL setting. (See this post for more about scrollable cursors.)
SQLSRV Code:
$tsql = "SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID)
AS RowNumber, Name, ProductNumber FROM Production.Product)
AS TEST
WHERE RowNumber BETWEEN ? AND ? + 1";/* Determine which row numbers to display. */
if(isset($_GET['lowRowNum']) && isset($_GET['highRowNum']))
{
$lowRowNum = $_GET['lowRowNum'];
$highRowNum = $_GET['highRowNum'];
}
else
{
$lowRowNum = 1;
$highRowNum = $rowsPerPage; //$rowsPerPage defaults to 10
}/* Set query parameter values. */
$params = array(&$lowRowNum, &$highRowNum);/* Execute the query. */
$stmt = sqlsrv_query($conn,
$tsql,
$params,
array( "Scrollable" => "keyset" ));
PDO code:
$tsql = "SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID)
AS RowNumber, Name, ProductNumber FROM Production.Product)
AS TEST
WHERE RowNumber BETWEEN ? AND ? + 1";$stmt = $conn->prepare($tsql,
array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));/* Determine which row numbers to display. */
if(isset($_GET['lowRowNum']) && isset($_GET['highRowNum']))
{
$lowRowNum = $_GET['lowRowNum'];
$highRowNum = $_GET['highRowNum'];
}
else
{
$lowRowNum = 1;
$highRowNum = $rowsPerPage;
}/* Execute the query with parameter values. */
$stmt->execute(array($lowRowNum, $highRowNum));
Now, with the SQLSRV driver, we can use the sqlsrv_has_rows and sqlsrv_num_rows functions (which require a scrollable cursor) to figure out which rows to display (if any). With the PDO driver makes use of the PDOStatement::rowCount method, which returns the number of rows returned by a SELECT query only if the statement was prepared with a scrollable cursor (see PDO code above).
SQLSRV Code:
if( sqlsrv_has_rows($stmt)===false )
{
echo "No data returned.</br>";
}
else
{
print("<table border='1px'>
<tr><td>Row Number</td>
<td>Product Name</td><td>Product ID</td></tr>");
/*Display all but the last of the rows in the result set.*/
$numRows = sqlsrv_num_rows($stmt);
for($i=0; $i<$numRows-1; $i++)
{
$row = sqlsrv_fetch_array($stmt);
print("<tr><td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td></tr>");
}/*Display the last row in the result set if
there isn't another page of results.*/
if($numRows <=10)
{
$row = sqlsrv_fetch_array($stmt);
print("<tr><td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td></tr>");
}print("</table></br></br>");
/* If there are previous results,
display the Previous Page link. */
if($lowRowNum > 1)
{
$prev_page_high = $lowRowNum - 1;
$prev_page_low = $prev_page_high - $rowsPerPage + 1;
$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 query returned 11 rows. */
if($numRows >= 11)
{
$next_page_low = $highRowNum + 1;
$next_page_high = $next_page_low + $rowsPerPage - 1;
$nextPage = "?lowRowNum=$next_page_low&".
"highRowNum=$next_page_high";
print("<a href=$nextPage>Next Page</a>");
}
}
PDO Code:
$numRows = $stmt->rowCount();
if($numRows <= 0)
{
echo "No data returned.</br>";
}
else
{
print("<table border='1px'>
<tr><td>Row Number</td>
<td>Product Name</td><td>Product ID</td></tr>");
/*Display all but the last of the rows in the result set.*/
for($i=0; $i<$numRows-1; $i++)
{
$row = $stmt->fetch(PDO::FETCH_NUM);
print("<tr><td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td></tr>");
}
/*Display the last row in the result set if
there isn't another page of results.*/
if($numRows <=10)
{
$row = $stmt->fetch(PDO::FETCH_NUM);
print("<tr><td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td></tr>");
}//Print table – same code as in SQLSRV example.
}
Direct Page Navigation
By “direct page” navigation I mean allowing a user to go directly to any page of data he or she wants to. This means providing breadcrumb-like links to each page…something like this:
To do this, we first have to calculate how many pages of data there are, which means executing a query that uses the COUNT function to return the number of rows (without returning the rows themselves):
SQLSRV Code:
/* 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/>";
}
PDO Code:
$tsql = "SELECT COUNT(ProductNumber) FROM Production.Product";
/* Execute the query. */
$stmt = $conn->query($tsql);/* Get the number of rows returned. */
$rowsReturned = $stmt->fetch(PDO::FETCH_NUM);if($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, when a user clicks on a page link, we execute a query (as in the previous example) that returns the rows we want:
SQLSRV Code:
/* Order target data by ID and select only items
(by row number) to display on a given page. */
$tsql = "SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID)
AS RowNumber,
Name,
ProductNumber
FROM Production.Product)
AS Temp
WHERE RowNumber BETWEEN ? AND ?";
/* Determine which row numbers to display. */
if(isset($_GET['pageNum']))
{
$highRowNum = $_GET['pageNum'] * $rowsPerPage;
$lowRowNum = $highRowNum - $rowsPerPage + 1;
}
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>");
PDO Code:
$tsql = "SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ProductID)
AS RowNumber,
Name,
ProductNumber
FROM Production.Product)
AS Temp
WHERE RowNumber BETWEEN ? AND ?";$stmt2 = $conn->prepare($tsql);
/* Determine which row numbers to display. */
if(isset($_GET['pageNum']))
{
$highRowNum = $_GET['pageNum'] * $rowsPerPage;
$lowRowNum = $highRowNum - $rowsPerPage + 1;
}
else
{
$lowRowNum = 1;
$highRowNum = $rowsPerPage;
}/* Set query parameter values. */
$params = array(&$lowRowNum, &$highRowNum);/* Execute the query. */
$stmt2->execute(array($lowRowNum, $highRowNum));/* 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 = $stmt2->fetch(PDO::FETCH_NUM) )
{
print("<tr>
<td>$row[0]</td>
<td>$row[1]</td>
<td>$row[2]</td>
</tr>");
}/* Close table. */
print("</table>");
As I said in the beginning, I think either of these paging strategies are good given the assumptions I’m working from. I’d be interested in your opinion about these approaches…what do you see as the benefits or drawbacks of doing paging in this way? (Complete scripts for both approaches with each API are attached to this post.)
Thanks.
-Brian