Server-Side total rows

Rudy69

Hi,
I use Datatables to draw a table from Mysql elements . Its loads very quickly, it is such a pleasure.
Nevertheless, my Mysql statement returns a number of rows that is greater than the elements shown on my html page. (875 vs 857). Some elements from the database are not shown !
Can someone tell me where the problem is please. I cannot figure it out.

Thanks a lot !

allan

Please link to a test case, as required in the forum rules. Without an example to debug there is nothing that we can really do to offer any help.

Allan

Rudy69

Hi Allan,

You are right. Here is the link to the page.
http://passionoutremer-crm.com/test.php
As you can see, it is written "875 elements" on the top of the page.
In the datatables footer, I have 857 elements.

Thanks !

tangerine

it is written "875 elements" on the top of the page

How is that figure being calculated, and how is it getting written into your HTML?

Rudy69

The link I gave show you the datatables data.
If you look inside the page, you will see ajax link "datafromdbb.php"

Here is the code

...

$table = 'raisonsociale';
$primaryKey = 'id';
$columns = array(
array(
'db' => 'ville',
'dt' => 0
),
array(
'db' => 'cp',
'dt' => 1
)
);
$sql_details = array(
'user' => 'xxxx',
'pass' => 'xxxx',
'db' => 'xxxx',
'host' => 'xxxx'
);
require('ssp.class.php');
echo json_encode(
SSP::simple($_GET, $sql_details, $table, $primaryKey, $columns)
);

...

The MySQL statement that calculated the elements (top of the page)
The page does not actually show the entire data because I compared the data shown on the HTML and my Mysql query in PhpMyAdmin.

...

SELECT * FROM raisonsociale

...

tangerine

If you are saying that your database table has 18 more records than are being retrieved by your DataTables query, you should be examining the "missing" records to see what - if anything - they have in common. My first guess would be a UTF-8 problem, e.g. foreign accented characters.

Rudy69

Thank you for giving me a clue @tangerine.
I will inspect my database and come back later.

Rudy69

I am back from "inspecting" my database.
I am french and I usually use accentued characters.
I found two rows in the database that are not shown with Jquery datatables. I delete the accentued data and refresh but the problem is still the same...

Rudy69

@tangerine, I tried to add specific functions to the ajax file.

For instance, I turn this

...
'formatter' => function ($d, r$ow) {
return $d;
}
...

into

...
'formatter' => function ($d, r$ow) {
return utf8_encode($d);
}
...

but it does not work. I changed this function with these ones :
utft8_decode(), htmlspecialchars(), htmlentities(), mb_convert_encoding().
It does not work too.

My database is built with 'interclassement : latin1_general_ci'.

Rudy69

I changed data onto my database and saved but it does not refresh the datatables html web page !

I think there is a problem of "cache". I am getting stuck :(

allan

How are you counting the number of elements fetched? My guess is that the query you are using for that is different from the query that whatever server-side processing code you are using is.

Allan

Rudy69

I finally checked all my code and understood the problem.
My ajax file was calling a wrong database.
Sorry !

Thanks a lot for the support. Nice tool Allan.