No pagination

timcadieux

Hi Allan, I just realized that with all the tweaks we did to my code, is that the Pagination no longer works, it only displays 1 Page?

[code]
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS id,fldsurname,fldfirstnameinitial,flddeceased,fldrank,fldservice,fldunit,fldcountry,fldcemetery,fldotherdetails
FROM tblsearchtable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

$sQuery = "
SELECT COUNT(id)

FROM tblsearchtable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];

if ( $sWhere != "" )
{
$sQuery = "
SELECT FOUND_ROWS()

";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
}
else
{
$iFilteredTotal = $iTotal;
}
[/code]

timcadieux

Allan, just so you know, I had to change the line

[code]SELECT FOUND_ROWS() [/code]

Back to

[code]SELECT id
FROM tblsearchtable[/code]

in order to get the Pagination to work

allan

Hi,

I think the problem is actually just in the ordering of your code, rather than a specific problem with the SQL. Try this instead:

[code]
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS id,fldsurname,fldfirstnameinitial,flddeceased,fldrank,fldservice,fldunit,fldcountry,fldcemetery,fldotherdetails
FROM tblsearchtable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

$sQuery = "
SELECT COUNT(id)
FROM ajax
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
[/code]

The main thing to note here is that if you use SQL_CALC_FOUND_ROWS, then FOUND_ROWS() is only valid for the next query (it's cached by the database). In the case above, you had a query in the middle of the two, which resulted in the error you were seeing.

Regards,
Allan

timcadieux

That was it, thx!