I want to make a jQuery function in where, getting a value of an input, send it to a PHP file to make a query in mysql and populate a datatable with the information received.
Another problem I have is that the table is initialized when the user is logged in and I don't know if that can obstruct the function I want to do.
This the table:
This is where I initialize it:
$.fn.dataTable.ext.errMode = 'none';
var table = $('#m3_sem').DataTable( {
"ajax": "dist/ajax/prueba_m3_sem.php",
"paging": false,
"ordering": false,
"info": false,
"searching": false,
"columns": [
{ "data": "resistencia" },
{ "data": "res1" },
{ "data": "res2" },
{ "data": "res3" },
{ "data": "res4" },
{ "data": "res5" },
{ "data": "res6" },
{ "data": "total" }
],
"order": [[0, 'asc']],
"pagingType": "full_numbers",
"language": {
"sSearch" : "Buscar:",
"lengthMenu": "Mostrando _MENU_ registros por pagina",
"zeroRecords": "No hay pedidos pendientes",
"info": "Mostrando pagina _PAGE_ de _PAGES_",
"infoEmpty": "Sin registros",
"infoFiltered": "(Filtrados de _MAX_ registros totales)",
"paginate" : {
"first" : "Primera pagina",
"previous" : "Anterior",
"next" : "Siguiente",
"last" : "Ultima pagina"
}
}
});
} );
And this is the PHP file "prueba_m3_sem.php", it generates the JSON I use to populate the table:
$sql = "SELECT DISTINCT resistencia ";
$sql.= "FROM registros ORDER BY resistencia";
$query=mysqli_query($conexion, $sql) or die("ajax-grid-data.php: get PO");
$data = array();
while( $row=mysqli_fetch_array($query) ) {
$sumtot = 0;
$nestedData=array();
$nestedData["resistencia"] = $row["resistencia"];
$sqld = "SELECT DISTINCT(fecha_entrega) FROM registros where sem_entrega = ".date("W")." and YEAR(fecha_entrega) = ".date("Y")." ORDER BY fecha_entrega";
$queryd=mysqli_query($conexion, $sqld) or die("ajax-grid-data.php: get PO");
$count = 0;
$tot = 0;
while( $rowd=mysqli_fetch_array($queryd) ) {
$count++;
$m3tot = 0;
$sqlm = "SELECT m3 FROM registros WHERE fecha_entrega = '".$rowd["fecha_entrega"]."' AND resistencia =".$row["resistencia"]."";
$querym=mysqli_query($conexion, $sqlm) or die("ajax-grid-data.php: get PO");
while( $rowm=mysqli_fetch_array($querym) ) {
if (empty($rowm['m3'])){
$m3 = 0;
}else{
$m3 = $rowm["m3"];
}
$m3tot = $m3tot + $m3;
}
$tot = $tot + $m3tot;
$nestedData["res".$count] = $m3tot;
$sumtot = $sumtot + $m3tot;
}
$nestedData["total"] = "<b>".$sumtot."</b>";
$data[] = $nestedData;
}
$sqld2 = "SELECT DISTINCT(fecha_entrega) as fecha FROM registros where sem_entrega = ".date("W")." and YEAR(fecha_entrega) = ".date("Y")." ORDER BY fecha_entrega";
//echo $sqld;
$queryd2=mysqli_query($conexion, $sqld2) or die("ajax-grid-data.php: get PO");
$totm3 = 0;
$nestedData["resistencia"] = "<b>Total</b>";
$count = 0;
while( $rowd2=mysqli_fetch_array($queryd2) ) {
//echo $rowd["fecha"]."</br>";
$sqltot = "SELECT SUM(m3) AS m3 from registros WHERE fecha_entrega ='".$rowd2["fecha"]."'";
$querytot=mysqli_query($conexion, $sqltot) or die("ajax-grid-data.php: get PO");
while( $rowtot=mysqli_fetch_array($querytot) ){
$count ++;
//echo $rowtot["m3"]."</br>"
$nestedData["res".$count] = "<b>".$rowtot["m3"]."</b>";
$totm3 = $totm3 + $rowtot["m3"];
}
}
$nestedData["total"] = "<b>".$totm3."</b>";
$data[] = $nestedData;
$json_data = array("data" => $data);
echo json_encode($json_data);
I've seen some code examples and the datatable documentation but I just can't find something that fits in the function I need or I just don't understand it very well.
Also, as you can see, English is not my native language. I hope and you can forgive my misspellings.
In advance thanks a lot for your response.