在jQuery datatable的ajax调用中将POST参数发送到PHP

Question

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:

Table and button

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.

Answer 1

what I understand is that you want to display some results in your table after you submit some search value? if thats the case here is a little example I did using a employees sample db with mysql:

the html:

<div class="container">
        <input type="text" name="txtName" id="txtName" value="">
        <button type="btn btn-default" name="button" id="btnSearch">Search</button>
    </div>
    <div class="container" id="tblResult" style="display:none;">
        <div class="row">
            <div class="col-sm-6">
                <table id="example" class="table table-responsive" style="width:100%">
                    <thead>
                        <tr>
                            <th>Cliente</th>
                            <th>Nombre</th>
                            <th>Apellido</th>
                            <th>Device Id.</th>
                            <th>Client id</th>
                            <th>Accion</th>
                        </tr>
                    </thead>
                    <tfoot>
                        <tr>
                            <th>Cliente</th>
                            <th>Nombre</th>
                            <th>Apellido</th>
                            <th>Device Id.</th>
                            <th>Client id</th>
                            <th>Accion</th>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>

I use an input to search employees by a name parameter in this example so if you want to filter by a date it would not be that different.

the javascript:

$(document).ready(function(){
// click event to call the datatable request
$('#btnSearch').on('click', (event) => {
    let search = $('#txtName').val();// get the input value
    if (search != "") {// validate that the value is  not empty
    //assing the datatable call to a variable
    let example = $('#example').DataTable({
        "destroy": true,
        "responsive":{//this is usefull if you want to use a full responsive datatable just add the responsive css from dataTables.net
            "details": {
                renderer: function ( api, rowIdx, columns ) {
                    var data = $.map( columns, function ( col, i ) {
                        return col.hidden ?
                        '<tr data-dt-row="'+col.rowIndex+'" data-dt-column="'+col.columnIndex+'">'+
                        '<td>'+col.title+':'+'</td> '+
                        '<td>'+col.data+'</td>'+
                        '</tr>' :
                        '';
                    } ).join('');

                    return data ?$('<table/>').append( data ) :false;
                }
            }
        },
        "autoWidth": false,//
        "ajax": {
            "url": 'request.php',
            "method": 'POST',
            data:{action:"SLC",name:search}//parameter to search and the action to perform
        },
        "columns": [
            {"data": "emp_no"},
            {"data": "first_name"},
            {"data": "last_name"},
            {"data": "gender"},
            {"data": "salary"},
            {"data": "title"}
        ],
        "language":{"url": "//cdn.datatables.net/plug-ins/1.10.15/i18n/Spanish.json"},//load all dataTables default values in spanish
        "columnDefs": [
            {
                "className": "dt-center", "targets": "_all"
            }
        ]
    });//fin obtener tabla
    example.on( 'xhr', function ( e, settings, json ) {// check is the response is not null and show the table
        if (json != null) {
            $('#tblResult').css("display","");
        }
    } );
}
});
}); //end ready

As you can see I call the dataTable method until a search is performed also I display the Table if the response is not empty.

the php:

<?php
 $host = '127.0.0.1';
 $db   = 'employees';
 $user = 'root';
 $pass = '';
 $charset = 'utf8mb4';
 $pdo = "";
 $options = [
     \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
     \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
     \PDO::ATTR_EMULATE_PREPARES   => false,
 ];
 $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
 try {
     $pdo = new PDO($dsn, $user, $pass, $options);
 } catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
 }
// a fucntion that display the employees by our search value
 function getEmployeesBySearch($conn,$order,$name) {

     $sql = "SELECT e.emp_no,e.first_name,e.last_name,e.gender, gs.salary, gt.title
     FROM employees e
     inner join (
         SELECT  s.emp_no,MAX(s.salary) AS salary
         FROM salaries s
         GROUP by s.emp_no
     ) as gs on e.emp_no = gs.emp_no
     inner join (
         SELECT t.emp_no ,t.title ,MAX(t.from_date) as from_date
         FROM titles t
         WHERE t.to_date = '9999-01-01'
         GROUP BY t.emp_no,t.title
     ) gt on e.emp_no = gt.emp_no
     WHERE gt.title = 'Senior Engineer'
     AND e.emp_no BETWEEN 10001 and 11819";
     //use  bind parameters and prepared statement to do the search and prevent sql injection
     if ($name != "") {
         $sql .= " AND e.first_name like CONCAT( '%', :name, '%')";
     }

     if ($order == "DESC") {
         $sql .= " ORDER BY gs.salary DESC";
     }else {
         $sql .= " ORDER BY gs.salary ASC";
     }
     $json= array();

     $stmt = $conn->prepare($sql);
     if ($name != "") {
         $stmt->bindParam(':name', $name, PDO::PARAM_STR, 100);
     }
     $stmt->setFetchMode(PDO::FETCH_ASSOC);
     $stmt->execute();
     $rows = $stmt->fetchAll();
     //store the data inside an array
     foreach ($rows as $row) {
         $tempArray = array(
             'emp_no'=>$row["emp_no"],
             'first_name'=>$row['first_name'] ,
             'last_name'=>$row['last_name'],
             'gender'=>$row['gender'],
             'salary'=>$row['salary'],
             'title'=>$row['title']
         );
         //json encode the array and send the response back
         $json["data"][] = $tempArray;
     }
     echo json_encode($json);
 }

 if (isset($_POST["action"])) {
     // we set the variables here, since you will add another stuff later I decided a switch to run a specific case with the action the user send
     $action = $_POST["action"];
     $order = (isset($_POST["order"]) ? $_POST["order"] : "");
     $name = (isset($_POST["name"]) ? $_POST["name"] : "");
     switch ($action) {
         case 'SLC':
         getEmployeesBySearch($pdo,$order,$name);
        break;
     }
 }
 ?>

I use a simple connection here and a function that load the results from my db to send them back as a json rsponse also try to use prepared statements in your querys and bind parameters like the example

Hope it helps =)