doujie9252
2019-07-04 16:23 阅读 287

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

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • dongzhang7157 dongzhang7157 2019-07-04 21:35

    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

    Results

    Hope it helps =)

    点赞 评论 复制链接分享

相关推荐