I am trying to edit the following code without success.
If no date filter is selected or a character is written in the imput seach, all the records in the table are displayed. What I am trying to do is that by default it does not show all the records and establish a filter using a WHERE.
I do not understand the whole logic of the code, but there are only two places where the query is made to the travel table.
$query = "SELECT * FROM viajes WHERE";
and
$query = "SELECT * FROM viajes";
I tried to add the following WHERE but when I enter it the following happens to me.
If I modify the first query by:
$query = "SELECT * FROM viajes WHERE id='1'";
A datatables script error appears and no record is displayed
"DataTables warning: table id = order_data - Invalid JSON response For more information about this error, please see link "
If I modify the second query by:
$query = "SELECT * FROM viajes WHERE id='1'";
Keep showing all the results in the table as if you had not added the WHERE condition.
Thanks for your help.
ajax.php
<?php
$connect = mysqli_connect("localhost", "root", "", "pruebas");//Configurar los datos de conexion
$columns = array('idviaje', 'apodocliente','fechacar','apodopro','nombrechofer','coste','pvp');
$query = "SELECT * FROM viajes WHERE";
if($_POST["is_date_search"] == "yes")
{
$query .= 'fechacar BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" AND';
}
if(isset($_POST["search"]["value"]))
{
$query .= '
(idviaje LIKE "%'.$_POST["search"]["value"].'%"
OR apodocliente LIKE "%'.$_POST["search"]["value"].'%")
';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'
';
}
else
{
$query .= 'ORDER BY idviaje DESC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
$result = mysqli_query($connect, $query . $query1);
$data = array();
while($row = mysqli_fetch_array($result))
{
$idviaje=$row["idviaje"];
$chofer=$row["apodopro"]." - ".$row["nombrechofer"];
$fechacar=date("d/m/Y", strtotime($row["fechacar"]));
$sub_array = array();
$sub_array[] = $fechacar;
$sub_array[] = $chofer;
$sub_array[] = $row["apodocliente"];
$sub_array[] = $row["coste"];
$sub_array[] = $row["pvp"];
$sub_array[] = '<a href="edit.php?idviaje='.$idviaje.'" class="glyphicon glyphicon-pencil"></a>';
$sub_array[] = '<a href=eliminar.php?idviaje='.$idviaje.'"><span class="glyphicon glyphicon-trash"></span></a>';
$data[] = $sub_array;
}
function get_all_data($connect)
{
$query = "SELECT * FROM viajes";
$result = mysqli_query($connect, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => get_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
index.php
<html>
<head>
<title>Filtrar datos por fechas usando datatables con PHP y MySQL</title>
<link rel="stylesheet" href="bootstrap-3.3.7/css/bootstrap.min.css">
<link rel="stylesheet" href="bootstrap-3.3.7/css/csscustom.css">
<link href="plugins/datatables/dataTables.bootstrap.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" href="plugins/datepicker/datepicker3.css">
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:1270px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:25px;
}
</style>
</head>
<body>
<div class="container box">
<h1 align="center"> Filtrar datos por fechas usando datatables con PHP y MySQL</h1>
<br />
<div class="table-responsive" style="overflow-x: hidden;">
<br />
<div class="row">
<div class="input-daterange">
<div class="col-md-4">
<input type="text" name="start_date" id="start_date" class="form-control" />
</div>
<div class="col-md-4">
<input type="text" name="end_date" id="end_date" class="form-control" />
</div>
</div>
<div class="col-md-4">
<input type="button" name="search" id="search" value="Buscar" class="btn btn-info active" />
</div>
</div>
<br />
<table id="order_data" class="table table-striped table-hover">
<thead>
<tr>
<th>FECHA CARGA</th>
<th>PROVEEDOR - CHOFER</th>
<th>CLIENTE</th>
<th>COSTE</th>
<th>PVP</th>
<th></th>
<th></th>
</thead>
</table>
</div>
</div>
<script src="bootstrap-3.3.7/js/jQuery-2.1.4.min.js"></script>
<script src="bootstrap-3.3.7/js/bootstrap.min.js"></script>
<script src="plugins/datepicker/bootstrap-datepicker.js"></script>
<script src="plugins/datatables/jquery.dataTables.js" type="text/javascript"></script>
<script src="plugins/datatables/dataTables.bootstrap.js" type="text/javascript"></script>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
$('.input-daterange').datepicker({
"locale": {
"separator": " - ",
"applyLabel": "Aplicar",
"cancelLabel": "Cancelar",
"fromLabel": "Desde",
"toLabel": "Hasta",
"customRangeLabel": "Custom",
"daysOfWeek": [
"Do",
"Lu",
"Ma",
"Mi",
"Ju",
"Vi",
"Sa"
],
"monthNames": [
"Enero",
"Febrero",
"Marzo",
"Abril",
"Mayo",
"Junio",
"Julio",
"Agosto",
"Septiembre",
"Octubre",
"Noviembre",
"Diciembre"
],
"firstDay": 1
},
format: "yyyy-mm-dd",
autoclose: true
});
fetch_data('no');
function fetch_data(is_date_search, start_date='', end_date='')
{
var dataTable = $('#order_data').DataTable({
"language":{
"lengthMenu":"Mostrar _MENU_ registros por página.",
"zeroRecords": "Lo sentimos. No se encontraron registros.",
"info": "Mostrando página _PAGE_ de _PAGES_",
"infoEmpty": "No hay registros aún.",
"infoFiltered": "(filtrados de un total de _MAX_ registros)",
"search" : "Búsqueda",
"LoadingRecords": "Cargando ...",
"Processing": "Procesando...",
"SearchPlaceholder": "Comience a teclear...",
"paginate": {
"previous": "Anterior",
"next": "Siguiente",
}
},
"processing" : true,
"serverSide" : true,
"sort": false,
"order" : [],
"ajax" : {
url:"ajax.php",
type:"POST",
data:{
is_date_search:is_date_search, start_date:start_date, end_date:end_date
}
}
});
}
$('#search').click(function(){
var start_date = $('#start_date').val();
var end_date = $('#end_date').val();
if(start_date != '' && end_date !='')
{
$('#order_data').DataTable().destroy();
fetch_data('yes', start_date, end_date);
}
else
{
alert("Por favor seleccione la fecha");
}
});
});
</script>
