I was recently searching for an approach to do paging with MSSQL and PHP5.
There are many methods posted on the internet e.g. using recordsets, temp-table, cursors etc. There is a pretty simple Asc-Desc method which uses default ordering in a subquery and then applies the reverse ordering to emulate MySQL Limit functionality in MSSQL.
MySQL:
SELECT * FROM TABLE LIMIT 20,10
MSSQL:
SELECT *
FROM (SELECT TOP 10 *
FROM (SELECT TOP 30 *
FROM TABLE
ORDER BY PK)
ORDER BY PK DESC)
ORDER BY PK
It was pretty cool but later I realised that it had flaws in that it requires the sorting column to be unique for it work. In most cases sorting columns (besides the PK) won't be unique! Complications comes in as well if you have complicated queries involving more than one tables. The other methods seems to involve a lot of work such as creating stored procedures, slamming temp tables etc.
And then an article triggers my thoughts -> MSSQL - XML paging:
I felt that this could be the direction and I did some research and experimentation. I was happy that the approach worked and the best part is - no modification of queries (already damn complicated) and it's simple and neat.
Here's what I did:
Step 1:
Use PHP DOM function to stream MSSQL query results to a XML file. The created XML file is then parsed using PHP SimpleXML functions.
$query = "<my complex query ...>";
$result = mssql_query($query) or die();
if ($totRec > 0) {
$doc = new DomDocument('1.0', 'UTF-8');
$root = $doc->createElement('root');
$root = $doc->appendChild($root);
while($row = mssql_fetch_assoc($result)) {
$occ = $doc->createElement('Products');
$occ = $root->appendChild($occ);
foreach ($row as $fieldname => $fieldvalue) {
$child = $doc->createElement($fieldname);
$child = $occ->appendChild($child);
$value = $doc->createTextNode($fieldvalue);
$value = $child->appendChild($value);
}
}
// get completed xml document
$xmldoc = $doc->saveXML();
$xml = simplexml_load_string($xmldoc);
Step 2:
Compute the start row and end row for the selected page.
Start Row = (Selected Page - 1) * (No. of Recs Per Page)
End Row = (Start Row + No. of Recs Per Page - 1)
Step 3:
Use a FOR loop to display the desired nodes (record sets) in the created XML file.
for ($i = $rowStart; $i <= $rowEnd; $i++) {
...
...
$xml->Child[$i]->SubChild;
...
...
}
Hope that this helps
