can't print all data on datatables serverside

can't print all data on datatables serverside

fahrifahri Posts: 1Questions: 1Answers: 0

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 ?>

----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"); } } } );
This discussion has been closed.