Pagination with MSSQL backend example code
Pagination with MSSQL backend example code
robertwoodcock
Posts: 1Questions: 0Answers: 0
The query that the code in http://www.datatables.net/development/server-side/php_mssql_odbc builds will always select the top $iDisplayLength records, never offset by $iDisplayStart. Meaning if you click on page 2, you get page 1's records.
I was able to get it to work by changing this line:
$sQuery = "SELECT * FROM ( SELECT " . $sLimit . " * FROM ( SELECT " . $sLimit2 . " " . implode(',',$aColumns) . " FROM $sTable $sWhere $sOrder )as x)as y";
To this:
$sQuery = "SELECT * FROM ( SELECT " . $sLimit . " * FROM ( SELECT " . $sLimit2 . " ROW_NUMBER() OVER(" . $sOrder . ") AS Row," . implode(',',$aColumns) . " FROM $sTable $sWhere $sOrder ) AS x ORDER BY Row DESC) AS y ORDER BY Row ASC";
This works for me in MS SQL 2005. My understanding is that SQL 2000 does not have ROW_NUMBER() so a different approach will be needed there. You may also want to change Row to something less likely to collide with field names found in the wild.
I was able to get it to work by changing this line:
$sQuery = "SELECT * FROM ( SELECT " . $sLimit . " * FROM ( SELECT " . $sLimit2 . " " . implode(',',$aColumns) . " FROM $sTable $sWhere $sOrder )as x)as y";
To this:
$sQuery = "SELECT * FROM ( SELECT " . $sLimit . " * FROM ( SELECT " . $sLimit2 . " ROW_NUMBER() OVER(" . $sOrder . ") AS Row," . implode(',',$aColumns) . " FROM $sTable $sWhere $sOrder ) AS x ORDER BY Row DESC) AS y ORDER BY Row ASC";
This works for me in MS SQL 2005. My understanding is that SQL 2000 does not have ROW_NUMBER() so a different approach will be needed there. You may also want to change Row to something less likely to collide with field names found in the wild.
This discussion has been closed.