I have a problem of paging my server side processing
i want to customzie paging to 10 records per page , but its not working. i have read some documentation that to make paging or pagination. i have to set
"serverSide": false
but i want the server side to reamin true, also i dont want to set the limit of results on server side script
So is there any way to fix this with jquery,javascript
$(document).ready(function () {
$("#contacts").dataTable({
pagingType: "simple",
lengthMenu: [
[10, 25, 50, -1],
[10, 25, 50, "All"],
],
bLengthChange: false,
sDom: "ltipr",
oLanguage: {
sEmptyTable:
'<span>No data</span>',
oPaginate: {
sNext: 'Next',
sPrevious: 'Previous',
},
},
"processing": true,
"serverSide": true,
"ajax": {
"url": "dl.php",
"dataType":"json"
},
rowCallback: function (row, data) {
$('td:eq(0)', row).html('<p><span class="imfm"> ' + data[1] + '</span></p>');
$('td:eq(1)', row).hide();
$('td:eq(2)', row).html('<p> ' + data[2] + ' </p>');
$('td:eq(3)', row).hide();
},
});
});
When you set serverside to true, which is needed to fetch data from server directly into the datatable, you must do the paging on servers site. If you don’t want this, you have to fetch all data from server before creating datatanle and then use this data in a non serverside datatable.
will this not slow the page, because i see most people get data to json and then parse to datatable, i thought there some magic which can be done. to set paging with server side: true
Why should it slow down the page? If you load all data (which at the end slows down the page of course) it doesn’t matter if you load it before or while datatable creation.
I would recommend to load only data you want to display, but as said, therefor you need to change serverside script. But that’s the only correct way at all.
i have tried to limit as u said in server side script
if ( isset( $request['start'] ) && $request['length'] != '-1' ) {
$query = $query . "LIMIT $request['start'],$request['length'] ";
}
its working, but the problem i see is that still the next and previous dont work. if i limit results to 11.
my next and previous for datatable will get enabled, if i limit to 10. the next and previous will get disabled
it means the pagination is ok, but not just working. because when i click it. it just shows link.com/file.php#
I cannot help without the server side code.
Normally you need to do two queries. One without limit which is only fetching the total number of records with a count(*) and one with the limit which is fetching the data. The returned data then contains the total number, the filtered number and the display number. But this is all described in the datatables manual
Don’t put data directly into an sql query statement. Use a prepared query instead.
Jquery datatables has a php class, that they use in the server-side processing examples, that does this work for you - https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php
this is the server side script. am using
#define the columnNames of table name from which, you are display details.
my @columns = qw/columnName1 columnName2 columnName3 columnName4 columnName5 columnName6 columnName7/;
my $sql_rowcount = "SELECT FOUND_ROWS() ";
my $sql_count = "SELECT COUNT(*) FROM tbdata";
# get total number of rows in table, replace the above tableName with a actual table in datbase.
my $count = $dbh->selectrow_arrayref($sql_count)->[0];
my @values;
# base query for data.
my $sql = "SELECT columnName1 columnName2 columnName3 columnName4 columnName5 columnName6 columnName7 FROM tbdata ORDER BY created ASC";
# filtering for search criteria
my $searchValue = $params->{'search[value]'};
if( $searchValue ne '' ) {
$sql .= ' WHERE (';
$sql .= 'columnName1 LIKE ? or columnName3 LIKE ? or columnName5 LIKE ?)';
push @values, ('%'. $searchValue .'%','%'. $searchValue .'%','%'. $searchValue .'%');
}
#individual column filtering
my $colsValue = "";
my $columnsStatus = "";
for (my $i = 0; $i < @columns; $i++) {
$colsValue = $params->{'columns['. $i . '][search][value]'};
$columnsStatus = $params->{'columns['. $i .'][searchable]'};
if ($colsValue ne '' and $columnsStatus eq "true" ) {
if (index ($sql,'WHERE') > 0) {
$sql .= ' AND ';
}
else {
$sql .= ' WHERE ';
}
$sql .= "`" . $columns[$i] . "` LIKE '%$colsValue%' ";
}
}
# paging
if ($params->{'start'} ne '' and $params->{'length'} ne '-1' ) {
$sql .= " LIMIT 10 ";
push @values, $params->{'length'};
push @values, $params->{'start'};
}
my $sth = $dbh->prepare($sql);
$sth->execute();
# for filtered row count.
my $s1th = $dbh->prepare($sql_rowcount);
$s1th->execute();
my @result = $s1th->fetchrow_array();
my $filterCount = $result[0];
# output hash
my %output = (
"draw" => $q->param('draw'),
"iTotalRecords" => $count,
"iTotalDisplayRecords" => $filterCount
);
my $rowcount = 0;
my $dataElement = "";
# fetching the different rows data.
while(my @aRow = $sth->fetchrow_array) {
my @row = ();
for (my $i = 0; $i < @columns; $i++) {
# looping thru different columns, pushing data to an array.
$dataElement = "";
#$logger->info ("column value :" . $columns[$i] . ":" . $aRow[$i]);
$dataElement = $aRow[$i];
push @row, $dataElement;
}
push @row, $rowcount;
# add each row data to hash collection.
@{$output{'data'}}[$rowcount] = [@row];
$rowcount++;
}
unless($rowcount) {
$output{'data'} = ''; #we don't want to have 'null'. will break js
}
#covert the hash to string format.
my $json_response = \%output;
# convert the response data to JSON format
my $jsonOutput = encode_json $json_response;
# add response header, important
print $q->header( -type => 'application/json' ),$jsonOutput;
am not using php, i was just referencing from php example i saw and implimate it to perl