need help understanding part of PHP example

ashiers

HI,

I'm a Java programmer and so not familiar with PHP, though looking at the examples on this site, I can deduce most of what the code is doing. Having said that, I am in a quandry about one section of PHP code in one of the examples: http://www.datatables.net/release-datatables/examples/data_sources/server_side.html

I've recreated the code in the PHP file with equivelant Java Server Page code.

First some background:
1. I'm accessing a database table named "employees" that contains 661 records.
2. The DataTable is set to retrieve 10 records at a time

The PHP example shows the following at the bottom of the file:
...
/* Data set length after filtering */
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

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


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

...

Notice the last statement for the variable $output. In order to allow the DataTable to work properly and enable the NEXT button so that I can retrieve the next set of 10 records, for my Java Server Page I had to code it so that iTotalRecords and iTotalDisplayRecords both contained the same value "661" (the total number of records). In other words I had to set both to the value contained in $iTotal.

I'm in a quandry as to what value $iFilteredTotal contains? I realize that running the query "SELECT FOUND_ROWS();" is going to return the value 10 if the previous query to the database limits the number to 10 records. But is that the value being placed in the variable $iFilteredTotal? Or does it contain the same value as $iTotal, and if so, please explain?

Alan

allan

This is actually a MySQL 'thing' rather than PHP. Fromt eh DataTables documentation for server-side processing:

> iTotalRecords - Total records, before filtering (i.e. the total number of records in the database)

> iTotalDisplayRecords - Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned in this result set)

That is what is happening here. `SQL_CALC_FOUND_ROWS` in the query before the `FOUND_ROWS()` is a MySQL extension to tell MySQL to efficient compute the number of rows that would have been returned without a limit, caching it for a FOUND_ROWS() query.

Allan

ashiers

Wow. Now I understand what that code is doing. I've never used SQL_CALC_FOUND_ROWS before in any of my queries. I tried it this morning on the database and it did exactly what you said it would do. I'm amazed. Thanks for your clarification. This will help enormously to refine my Java code.

Alan