newbie to datatables
newbie to datatables
ebudihar
Posts: 2Questions: 1Answers: 0
dear fellows,
I am new to datatables. I am trying to understand and use it. I have some challenges that I face
1. I have unicode font. I already set mysql field into utf8mb4, but when I display it into datatables, it does not display the right one.
2. it does not display the right one base on the data amount that I want to have, it always shows 25.
3. how can I understand all variables or parameters that used in javascript and to my serverside processing?
note: i am using perl for coding.
I am sorry asking too much and general.
I am looking forward to a favorable reply from you. Thank you.
this is my html file:!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" /><title>PSE CSS Beneficiaries Finder</title>
<link rel="stylesheet" href="/interface/themes/style_light.css?v=41" type="text/css">
<link rel="stylesheet" href="/public/assets/datatables.net-dt-1-10-13/css/jquery.dataTables.min.css" type="text/css">
<link rel="stylesheet" href="/public/assets/datatables.net-colreorder-dt-1-3-2/css/colReorder.dataTables.min.css" type="text/css">
</head>
<body class="body_top">
| Child ID | Full Name EN | Full Name KH | Gender | Date of Birth |
|---|---|---|---|---|
</body>
</html>
this is my server side processing script written in perl:
CGI OBJECT
my $q = new CGI;
my @columns = qw/Child_ID Full_Name_EN Full_Name_KH Gender Date_of_Birth/;
my $params = $q->Vars;
info-("Parameter's-received:-"-.-Dumper($params));">info-("Parameter's-received:-"-.-Dumper($params));" href="#$logger->info-("Parameter's-received:-"-.-Dumper($params));">$logger->info ("Parameter's received: " . Dumper($params));
important, if you want to see the received parameters from GUI.
info-("Parameter's-received:-"-.-Dumper($params));">info-("Parameter's-received:-"-.-Dumper($params));" href="#$logger->info-("Parameter's-received:-"-.-Dumper($params));">$logger->info ("Parameter's received: " . Dumper($params));
connection details for Test and Prod database
my $server2 = xxx
my $db2 = xxx
my $username2 = xxx
my $password2 = xxx
my $dbh2 = DBI->connect("dbi:mysql:$db2:$server2;mysql_enable_utf8mb4=1", $username2, $password2);
Get the total count of rows in the table
my $sql_count = "select count(id) from Contacts";
my $count = $dbh2->selectrow_arrayref($sql_count)->[0];
Start building up the database query
my @values;
my $sql = "select contactCode, FirstNameEN, NameEN, FirstNameKH, NameKH, Gender, DateOfBirth from Contacts where Type='Student'";
if a search parameter was supplied in the AJAX call, build the WHERE part in the SQL statement
if( $params->{sSearch} ){
$sql .= ' WHERE ';
$sql .= 'contactCode LIKE ? OR FirstNameEN LIKE ? or NameEN LIKE ? or Gender LIKE ? or DateOfBirth LIKE ?';
push @values, ('%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%','%'.$params->{sSearch}.'%');
}
if a sorting parameter was supplied in the AJAX call, build up the ORDER BY part in the SQL statement
if( $params->{iSortingCols} ){
$sql .= ' ORDER BY';
foreach my $c (0 .. ( $params->{iSortingCols} -1 )){
$sql .= ' ' . $columns[ $params->{"iSortCol_$c"} ] . ' ' . $params->{"sSortDir_$c"};
$sql .= ','
}
$sql =~ s/,$//;
}
Limit the output and also allow to paginate or scroll infinitely
$sql .= " LIMIT ? OFFSET ?";
push @values, (($params->{iDisplayLength} > 0 ? $params->{iDisplayLength} : 25), ( $params->{iDisplayStart} // 0));
Fetch the data from the database
my $data = $dbh2->selectall_arrayref($sql, { Slice => [] }, @values);
Return the JSON object
print $q->header('text/json; charset=utf-8');
my $json = encode_json({ aaData => $data, iTotalRecords => $count, iTotalDisplayRecords => $count, sEcho => int($params->{sEcho}) });
print $json;
$dbh2->disconnect();
Answers
Hi @ebudihar ,
There's several threads on UTF8 setup and configuration, such as:
https://datatables.net/forums/discussion/46776/utf8-problem
https://datatables.net/forums/discussion/30155/does-datatables-support-utf8
https://datatables.net/forums/discussion/21242/invalid-json-response-when-adding-some-utf-8-characters
It looks like your code is doing this - possible the 25 is coming from here:
For server side processing, enable
serverSide. The protocol is discussed here.Cheers,
Colin
dear Colin,
the code is working partially. Now, it does not show the aaDaa in JSON data.
Hi @ebudihar ,
We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin