re: Paging Data with the SQL Server Driver for PHP

jezjones — Fri, 03 Apr 2009 19:21:02 GMT

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.

re: Paging Data with the SQL Server Driver for PHP

Brian Swan — Fri, 03 Apr 2009 23:07:12 GMT

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):

$tsql = "WITH Products AS

(SELECT ROW_NUMBER() OVER(ORDER BY ListPrice)

AS RowNumber, Name, ListPrice

FROM Production.Product

WHERE Name LIKE '%' + ? + '%'

AND ListPrice > 0.0)

SELECT * FROM Products

WHERE RowNumber BETWEEN ? AND ? + 1";

And, this JOIN works with the code in the post:

$tsql = "WITH Products AS

(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)

SELECT * FROM Products

WHERE RowNumber BETWEEN ? AND ? + 1";

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?

Thanks.

-Brian