can't print all data on datatables serverside

fahri

Hallo,
i have problem for print databales serverside use php code.
Only print 10 data by amount row. i need print full data on all page.

i use datatables filtering table,

serverside code:

<?php
/* Database connection start */
$servername = "localhost";
$username = "******";
$password = "******";
$dbname = "****";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

/* Database connection end */

// storing request (ie, get/post) global array to a variable
$requestData= $_REQUEST;

$columns = array(
// datatable column index => database column name
0 =>'asset_code',
1 => 'linkid',
2=> 'refdoc',
3=> 'division_name',
4=> 'account_name',
5=> 'dapertement_name',
6=> 'major_name',
7=> 'minor_name',
8=> 'item_name',
9=> 'item_desc',
10=> 'manufacture_name',
11=> 'model_name',
12=> 'asset_desc',
13=> 'serial_number',
14=> 'barcode_qty',
15=> 'qty',
16=> 'maesurement_name',
17=> 'status_name',
18=> 'condition_name',
19=> 'label',
20=> 'site_name',
21=> 'location_name',
22=> 'name',
23=> 'purchase_no',
24=> 'purchase_date',
25=> 'unit_cost',
26=> 'currency_name',
27=> 'company_name',
28=> 'reg_date',
29=> 'remark'

);

$no=0;

// getting total number records without any search
$sql = "SELECT idasset,asset_code, linkid, refdoc, division_name , account_name , dapertement_name , major_name , minor_name , item_name , item_desc , manufacture_name , model_name ,
model_name, asset_desc , serial_number, barcode_qty, qty, maesurement_name, status_name, condition_name, label, site_name, location_name, name, purchase_no, purchase_date, unit_cost, currency_name, company_name, reg_date,remark ";
$sql.=" FROM tbl_view_report2";
$query=mysqli_query($conn, $sql) or die("datatable_server_side/filter_asset.php: get employees");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.

$sql = "SELECT idasset, asset_code, linkid, refdoc, division_name , account_name , dapertement_name , major_name , minor_name , item_name , item_desc , manufacture_name , model_name ,
model_name, asset_desc , serial_number, barcode_qty, qty, maesurement_name, status_name, condition_name, label, site_name, location_name, name, purchase_no, purchase_date, unit_cost, currency_name, company_name, reg_date,remark ";
$sql.=" FROM tbl_view_report2 WHERE 1 = 1";

// getting records as per search parameters
if( !empty($requestData['columns'][8]['search']['value']) ){ //item_name
$sql.=" AND item_name LIKE '".$requestData['columns'][8]['search']['value']."%' ";
}
if( !empty($requestData['columns'][3]['search']['value']) ){ //division_name
$sql.=" AND division_name LIKE '".$requestData['columns'][3]['search']['value']."%' ";
}
if( !empty($requestData['columns'][17]['search']['value']) ){ //status_name
$sql.=" AND status_name LIKE '".$requestData['columns'][17]['search']['value']."%' ";
}

if( !empty($requestData['columns'][28]['search']['value']) ){ //reg_date
$sql.=" AND reg_date LIKE '".$requestData['columns'][28]['search']['value']."%' ";
}
if( !empty($requestData['columns'][18]['search']['value']) ){ //condition_name
$sql.=" AND condition_name LIKE '".$requestData['columns'][18]['search']['value']."%' ";
}

if( !empty($requestData['columns'][4]['search']['value']) ){ //account_name
$sql.=" AND account_name LIKE '".$requestData['columns'][4]['search']['value']."%' ";
}
if( !empty($requestData['columns'][0]['search']['value']) ){ //asset_code
$sql.=" AND asset_code LIKE '".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){ //linkid
$sql.=" AND linkid LIKE '".$requestData['columns'][1]['search']['value']."%' ";
}

if( !empty($requestData['columns'][5]['search']['value']) ){ //dapertement_name
$sql.=" AND dapertement_name LIKE '".$requestData['columns'][5]['search']['value']."%' ";
}
if( !empty($requestData['columns'][20]['search']['value']) ){ //site_name
$sql.=" AND site_name LIKE '".$requestData['columns'][20]['search']['value']."%' ";
}
if( !empty($requestData['columns'][21]['search']['value']) ){ //location_name
$sql.=" AND location_name LIKE '".$requestData['columns'][21]['search']['value']."%' ";
}
if( !empty($requestData['columns'][6]['search']['value']) ){ //major_name
$sql.=" AND major_name LIKE '".$requestData['columns'][6]['search']['value']."%' ";
}

if( !empty($requestData['columns'][7]['search']['value']) ){ //minor_name
$sql.=" AND minor_name LIKE '".$requestData['columns'][7]['search']['value']."%' ";
}
if( !empty($requestData['columns'][22]['search']['value']) ){ //name
$sql.=" AND name LIKE '".$requestData['columns'][22]['search']['value']."%' ";
}

/* if( !empty($requestData['columns'][2]['search']['value']) ){ //age
$rangeArray = explode("-",$requestData['columns'][2]['search']['value']);
$minRange = $rangeArray[0];
$maxRange = $rangeArray[1];
$sql.=" AND ( employee_age >= '".$minRange."' AND employee_age <= '".$maxRange."' ) ";
} */

$query=mysqli_query($conn, $sql) or die("datatable_server_side/filter_asset.php: get employees");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." "; // adding length

$query=mysqli_query($conn, $sql) or die("datatable_server_side/filter_asset.php: get employees");

$data = array();
while( $row=mysqli_fetch_array($query) ) { // preparing an array
$nestedData=array();
$no++;
$nestedData[] = $row["idasset"];
$nestedData[] = $row["asset_code"];
$nestedData[] = $row["linkid"];
$nestedData[] = $row["refdoc"];
$nestedData[] = $row["division_name"];
$nestedData[] = $row["account_name"];
$nestedData[] = $row["dapertement_name"];
$nestedData[] = $row["major_name"];
$nestedData[] = $row["minor_name"];
$nestedData[] = $row["item_name"];
$nestedData[] = $row["item_desc"];
$nestedData[] = $row["manufacture_name"];
$nestedData[] = $row["model_name"];
$nestedData[] = $row["asset_desc"];
$nestedData[] = $row["serial_number"];
$nestedData[] = $row["barcode_qty"];
$nestedData[] = $row["qty"];
$nestedData[] = $row["maesurement_name"];
$nestedData[] = $row["status_name"];
$nestedData[] = $row["condition_name"];
$nestedData[] = $row["label"];
$nestedData[] = $row["site_name"];
$nestedData[] = $row["location_name"];
$nestedData[] = $row["name"];
$nestedData[] = $row["purchase_no"];
$nestedData[] = $row["purchase_date"];
$nestedData[] = $row["unit_cost"];
$nestedData[] = $row["currency_name"];
$nestedData[] = $row["company_name"];
$nestedData[] = $row["reg_date"];
$nestedData[] = $row["remark"];

$data[] = $nestedData;

}

$json_data = array(
"draw" => intval( $requestData['draw'] ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"data" => $data // total data array
);

echo json_encode($json_data); // send data as json format

<?php > ?>

----end serverside----

view file

jQuery(function($) { //initiate dataTables plugin var oTable1 = $('#employee-grid') //.wrap("

") //if you are applying horizontal scrolling (sScrollX) .DataTable( { "dom": 'lBfrtip', "buttons": [ { extend: 'collection', text: 'Export', buttons: [ 'copy', 'excel', 'csv', 'pdf', 'print' ] } ], select: true, "lengthMenu": [[10, 25, 50, 100, 150, 200, 300, 400, 500, 1000, 5000 -1], [10, 25, 50,100, 150, 200, 300, 400, 500, 1000, 5000, "All"]], "processing": true, "serverSide": true, "ajax":{ url :"datatable_server_side/filter_asset.php", // json datasource type: "post", // method , by default get error: function(){ // error handling $(".employee-grid-error").html(""); $("#employee-grid").append('No data found in the server'); $("#employee-grid_processing").css("display","none"); } } } );