Pagination breaks on 2nd page with SQL Server

tcash21

I thought I had everything working for pagination using SQL Server. The Next button is no longer grayed out and the iTotal #s are correct but when I click Next to go to page 2 there are no rows!

Debugger page:
http://debug.datatables.net/adinix

[code]
/*
* Paging modified for SQL Server
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = " row > " .$_GET['iDisplayStart']." and row <= ".
$_GET['iDisplayLength'];
}
[/code]

I removed the WHERE clause for now for testing purposes:
[code]
/*
* SQL queries
* Get filtered data to display without pagination
*/
$sQuery1 = "
SELECT *
FROM
$sTable

$sOrder
";

// Query with filtering AND pagination
$sQuery2 = "
SELECT *
FROM
( SELECT *, ROW_NUMBER() OVER (ORDER BY NumDays) as row FROM
$sTable) $sTable WHERE
$sLimit

$sOrder
";

$rResult = mssql_query( $sQuery1, $gaSql['link'] ) or die(mssql_get_last_message());

/* Data set length after filtering */
$sQuery1 = "
SELECT @@ROWCOUNT
";
$rResultFilterTotal = mssql_query( $sQuery1, $gaSql['link'] ) or die(mssql_get_last_message());
$aResultFilterTotal = mssql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
SELECT COUNT('".$sIndexColumn."')
FROM $sTable
";
$rResultTotal = mssql_query( $sQuery, $gaSql['link'] ) or die(mssql_get_last_message());
$aResultTotal = mssql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];

$rResult2 = mssql_query( $sQuery2, $gaSql['link'] ) or die(mssql_get_last_message());

/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mssql_fetch_array( $rResult2 ) )
{
$row = array();
for ( $i=0 ; $i

[/code]

tcash21

Anyone? The Where clause is back in now, but still only searches the results on the first page. Once I hit "Next" there are no results on 2nd page but 'Show 10, 25, 50 entries" works.

cjxmccac

Have you tried executing the query directly in SSMS? I'm guessing the ROW_NUMBER is where it's failing because that syntax looks incorrect for MS SQL.

Here is an article outlining usage of ROW_NUMBER() in MS Sql Server 2005 (not sure of your version). Get the query working directly first and then you should have no problems back porting it into your code!
http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

tcash21

The query works:
[code]
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY NumDays) as row FROM
leadActivity) leadActivity WHERE row > 1 and row <= 100 AND (Company like '%Studio%') ORDER BY Company
[/code]

And it returns 4 rows of data.

The next query also works and returns the #4.
[code]
SELECT @@ROWCOUNT;
[/code]

Is that the desired behavior?

Thanks,
Tanya

snarf2larf

If you are only returning 4 rows how do you expect there to be a 2nd page?

tcash21

Sorry that's just an example query. Is there a way to send an alert after paging or searching that outputs the exact SQL query that is being executed? My js and jquery skills are lacking here :/

snarf2larf

[code]
$sQuery2 = "
SELECT *
FROM
( SELECT *, ROW_NUMBER() OVER (ORDER BY NumDays) as row FROM
$sTable) $sTable WHERE
$sLimit

$sOrder
";
[/code]

After that put:

[code]
die($sQuery2);
[/code]

Then in firebug the result should be your query -- your table won't load correctly until you remove the die statement but this will allow you to see what the query being generated is.

tcash21

Thanks so much. I've been able to debug properly now and found the problem.

Had to update my limit to be:
[code]
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = " row > " .$_GET['iDisplayStart']." and row <= ".
($_GET['iDisplayLength'] + $_GET['iDisplayStart']);
}
[/code]

I just added an offset to iDisplayLength.

snarf2larf

No problem, be careful to validate that $_GET['iDisplayStart'] & $_GET['iDisplayLength'] are numbers or addslashes so you don't end up with a SQL injection issue.