newbie to datatables

newbie to datatables

ebudiharebudihar 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">

$(document).ready( function() { var oTable = $('#pt_table').dataTable( { "processing": true, "serverSide": true, "ajax": { "url": "children_finder_ajax.pl", "type": "POST", "dataType":"json" }, "columns": [ {"sName": "Child_ID"}, {"sName": "Full_Name_EN"}, {"sName": "Full_Name_KH"}, {"sName": "Gender"}, {"sName": "Date_of_Birth"} ], "lengthMenu": [ 10, 25, 50, 100 ], "pageLength": 10, "aria": { "sortAscending": ": activate to sort column ascending", "sortDescending": ": activate to sort column descending" }, "search": "Search all columns:" } ); // This is to support column-specific search fields. // Borrowed from the multi_filter.html example. $("thead input").keyup(function () { // Filter on the column (the index) of this element oTable.fnFilter( this.value, $("thead input").index(this) ); }); });

</head>
<body class="body_top">

Processing...
Child IDFull Name ENFull Name KHGenderDate 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));">$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));">$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

This discussion has been closed.