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
