Paging Data with the SQL Server Drivers for PHP: Simplified

One of the best insights I had at the SQL Server JumpIn! Camp back in November was this: Lots of PHP applications and frameworks generate SQL queries dynamically. I’m sure that for very experienced developers, that realization comes across as somewhat naive – and perhaps it is. However, to be clear, the realization had more to do with just how often applications/frameworks need to dynamically create SQL queries, not that the practice is sometimes necessary.

Armed with that new realization (and with Josh Holmes’ repeated mantra of “Keep Things Simple”),  I revisited a post I wrote some time back: How to Page Data with the SQL Server Drivers for PHP. Two things about that post stood out:

  1. The query that forms the basis of the paging functionality (it uses the ROW_NUMBER…OVER syntax) is very complex and would be a pain to generate dynamically.
  2. The code for tracking which page a user was on is complex enough that it would be a pain to maintain.

There should be a simpler way to page data with the SQL Server Drivers for PHP. Fortunately, there is. Vineet Chaudhary (lead developer for the SQL Server Drivers for PHP) pointed out that we could use the server-side cursors that are accessible in the drivers to address both issues above. So, in this post, I’ll provide a simpler way to page data than I did in my previous post about paging.

Note: Four example scripts are attached to this post. Two that use the SQLSRV driver (one for “direct page navigation” and one for “previous/next page navigation”, and two that use the PDO_SQLSRV driver (again, “direct” and “previous/next page” navigation). The code snippets below are taken from the SQLSRV examples.

What is a cursor?

An oversimplified definition of a database cursor might be this: A cursor is database functionality that allows you to point to a certain location within a result set and allows you to move forward (and sometimes backward, depending upon the cursor type) through the result set one row at a time. If you are interested in more detailed information, I suggest reading Cursors (Database Engine) and/or Understanding Cursor Options in the SQL Server Driver for PHP.

In the paging scenarios below, I’ll use a static cursor since that cursor type would seem to satisfy the requirements of many web-based applications.

Note: While use of scrollable cursors does simplify paging, that simplification does come a price. Be sure to read the Performance Considerations section below for more information.

Executing a query with a dynamic cursor

To connect to the server with a scrollable cursor (a static cursor in my example), you just need to specify it when executing a query:

// Connect to the server.
$serverName = 'server\sqlexpress';
$connOptions = array("UID" => "user",
                     "PWD" => "password",
                     "Database"=>"AdventureWorks");
$conn = sqlsrv_connect($serverName, $connOptions);
if (!$conn)
    die( print_r( sqlsrv_errors(), true));

// Define and execute the query. 
// Note that the query is executed with a "scrollable" cursor.
$sql = "SELECT Name, ProductNumber FROM Production.Product";

$stmt = sqlsrv_query($conn,
                     $sql,
                     array(),
                     array( "Scrollable" => 'static' ));
if ( !$stmt )
    die( print_r( sqlsrv_errors(), true));

Note that in my example I’m executing a query against the Production.Product table of the AdventureWorks example database.

Determining the number of rows (and pages)

Since we are using a scrollable cursor, we can use the sqlsrv_num_rows function to determine the number of rows returned by the query. If we also set the number of results we want per page (10 in my example), we can calculate the number of pages:

// Set the number of rows to be returned on a page.
$rowsPerPage = 10;

// Get the total number of rows returned by the query. 
$rowsReturned = sqlsrv_num_rows($stmt);
if($rowsReturned === false)
    die( print_r( sqlsrv_errors(), true));
elseif($rowsReturned == 0)
{
    echo "No rows returned.";
    exit();
}
else
{    
    /* Calculate number of pages. */
    $numOfPages = ceil($rowsReturned/$rowsPerPage);
}

A function for paging

Because we used a scrollable cursor to execute our query, we can begin retrieving data from any row in the result set. The function below accepts the statement resource (returned from our query execution), the desired page, and the desired rows per page. The function uses the sqlsrv_fetch_array function and the SQLSRV_SCROLL_ABSOLUTE setting to begin retrieving data from the specified point in the result set (determined by $offset + $i).

function getPage($stmt, $pageNum, $rowsPerPage)
{
    $offset = ($pageNum - 1) * $rowsPerPage;
    $rows = array();
    $i = 0;
    while($row = sqlsrv_fetch_array($stmt,
                                    SQLSRV_FETCH_NUMERIC,
                                    SQLSRV_SCROLL_ABSOLUTE,
                                    $offset + $i)
           && $i < $rowsPerPage)
    {
        array_push($rows, $row);
        $i++;
    }
    return $rows;
}

Note that the function returns a 2-dimensional array so that you can cache page results easily.

How you display the data returned by the function above will, of course, depend on your application. For the purposes of demonstration, here’s one way:

// Display the selected page of data.
echo "<table border='1px'>";
$pageNum = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;
$page = getPage($stmt, $pageNum, $rowsPerPage);

foreach($page as $row)
    echo "<tr><td>$row[0]</td><td>$row[1]</td></tr>";

echo "</table><br />";

Displaying links to pages

There are a many ways to allow a user of your application to select pages for display. Again, for the purposes of demonstration, I’ll provide two examples.

Direct page navigation

If you would like to allow a user to navigate directly to any page, here is a way to display links to all the pages of a result set:

for($i = 1; $i<=$numOfPages; $i++) 
{ 
    $pageLink = "?pageNum=$i"; 
    print("<a href=$pageLink>$i</a>&nbsp;&nbsp;"); 
}

Previous/Next page navigation

If you want to allow users to navigate one page at a time with Previous Page and Next Page links, here’s one way to facilitate that:

// Display Previous Page link if applicable.
if($pageNum > 1)
{
    $prevPageLink = "?pageNum=".($pageNum - 1);
    echo "<a href='$prevPageLink'>Previous Page</a>";
}

// Display Next Page link if applicable.
if($pageNum < $numOfPages)
{
    $nextPageLink = "?pageNum=".($pageNum + 1);
    echo "&nbsp;&nbsp;<a href='$nextPageLink'>Next Page</a>";
}

Performance Considerations

The simplicity that scrollable cursors offer comes at a cost on the server. When the SELECT statement above is executed, the entire result set is copied into a temporary table (from which we can select rows by specifying an offset and the number of rows we want). This is not as efficient as executing the SELECT ROW_NUM…OVER query (mentioned earlier and explained in this post), which selects only the the rows specified in a range. Also note that in that post I used a forward-only cursor, which uses few resources on the server. So, this is something to consider when building an application – is the simplicity that scrollable cursors offer worth the performance hit on the server? However, both queries save on network traffic – only data for the requested page is sent over the network.

Note: In the next release of SQL Server (code named “Denali”), the OFFSET clause will facilitate paging in a way that allows you to write simple code without impacting performance on the server. You can read more about the OFFSET clause here: SQL Server v.Next (Denali): Using the OFFSET clause (paging).

As I mentioned earlier, complete example scripts are attached (including examples that use the PDO_SQLSRV driver). I hope you will agree that using a scrollable server-side cursor greatly simplifies paging data (at least compares with my previous examples).

Thanks.

-Brian

Share this on Twitter

No Comments