I'm using server-side processing : nginx and sqlite
I'm using server-side processing : nginx and sqlite
mjesus
Posts: 13Questions: 4Answers: 0
..........my html:
$(document).ready(function() {
var dataTable = $('#acao_table').DataTable( {
"processing": true,
"serverSide": true,
"ajax":{
url :"BF_acessoBbSqlite_acao.php", // json datasource
type: "post", // method , by default get
error: function(){ // error handling
$(".acao_table_error").html("");
$("#acao_table").append('<tbody class="acao_table_error"><tr><th colspan="4">Nenhum dado encontrado no servidor</th></tr></tbody>');
$("#acao_table_processing").css("display","none");
}
}
} );
} );
.... my server-side
if( !empty($requestData['search']['value']) ) {
$sql.=" AND (id LIKE '" . $requestData['search']['value'] . "%' ";
$sql.=" OR nome LIKE '" . $requestData['search']['value'] . "%' ";
$sql.=" OR cmt LIKE '" . $requestData['search']['value'] . "%' ";
$sql.=" OR mrid LIKE '" . $requestData['search']['value'] . "%' ) ";
}
$query = $sqlite_db->query($sql) or die("Com o search: BF_acessoBbSqlite_acao.php: get acao");
$totalFiltered = count($query->fetchAll());
$sql.=" ORDER BY " . $columns[$requestData['order'][0]['column']] . " " . $requestData['order'][0]['dir'] . " LIMIT " . $requestData['start'] . " ," . $requestData['length'] . " ";// adding length
$query = $sqlite_db->query($sql) or die("Erro no sql: BF_acessoBbSqlite_acao.php: get acao");
$data = array();
foreach ($sqlite_db->query($sql) as $row) { // preparing an array
$nestedData=array();
$nestedData[] = $row["ID"];
$nestedData[] = $row["NOME"];
$nestedData[] = $row["CMT"];
$nestedData[] = $row["MRID"];
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval( $requestData['draw'] ), // for every request/draw by clientside
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
<?php > -- VISUAL LINE -- ?>
Answers
My database sqlite was created from csv files. The problem is when I have to request from a very large table > 12 M, The contents are not shown. What can I do?
From Maria
The first thing I would suggest is that you echo out the generated SQL statement. Let's make sure that is what would be expected.
Allan
Hello Allan, using the chrome debugger I noticed that I was having trouble dealing with large tables containing a considerable number of columns. I changed the sqlite command that gets the total registry number, is now being done in the sql itself, and also include inside the if that forms the filter for the second query the new query to be formed. See the code ...
$sql = "SELECT count(*) as x FROM pas ";
$query = $sqlite_db->query($sql) or die("BF_acessoBbSqlite_pas.php: get pas");
$aux = $query->fetch(PDO::FETCH_ASSOC);
$totalData = $aux["x"];
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
...
if( !empty($requestData['search']['value']) ) {
$filter .=" AND (id LIKE '" . $requestData['search']['value'] . "%' ";
...
$sql.= $filter;
$query = $sqlite_db->query($sql) or die("Com o search: BF_acessoBbSqlite_pas.php: get pas");
$aux = $query->fetch(PDO::FETCH_ASSOC);
$totalFiltered = $aux["x"];
}
$sql = "SELECT * FROM pas where 1 = 1";
$sql.= $filter;
Hello Allan,
thanks for the help and support!
Maria
Is it working now? Or are you still having problems with it?
Allan
Yes Allan, It's working!
Thank you!
Maria