how to send a php array by json_encode and show with datatables

Question

how to send a php array by json_encode and show with datatables

Navigation

#1 by (0 votes)

0

I have the following problem.

I need to send some data already formatted or calculated in a php array via json to be displayed in datatables.

The issue is that I have tried several possible solutions and none shows information about it.

My PHP query to MySQL:

$datos = array();
        while ($row = mysqli_fetch_array($resultado)){
            $idventa = $row['idventa'];
            $mesa = $row['mesa'];
            $nombre = $row['nombre'];
            $apellido = $row['apellido'];
            $total = $row['total'];
            $fecha = $row['fecha'];
            $pago = $row['pago'];               

            $datos[] = array('idventa'=> $idventa, 'mesa'=> $mesa, 'nombre'=> $nombre, 'apellido'=> $apellido,
                                'total'=> $total, 'fecha'=> $fecha, 'pago'=> $pago);

            //$arreglo["data"][]= $data;
        }
        $array = json_encode($datos);
        echo json_encode($array);

    }

Where do I get the json:

$( document ).ready(function() {
    $('#listado').dataTable({
        "ajax": "list_reportes.php",

        "columns" : [{
            "sClass": "alignRight", 
            "data": "fecha",
            "render": function (data) {
                var dateString = new Date(data);
                return moment.utc(dateString).format('DD/MM/YYYY');
            }
        },{
            "sClass": "alignRight", 
            "data" : "mesa",
        }
       .........

The reason for doing so, is because I need to send some variables such as $total that should already be calculated before being sent and others that should be the same way.

The console displays the following error:

Uncaught TypeError: Cannot read property 'length' of undefined.

I hope you can guide me on this subject, in advance, thank you very much.

mysql php json datatables

asked by maha1982 28.01.2018 в 20:52

source

1 answer

Help to set date and time in Air Datepicker Mixed content problem does not allow HTTPS to load

score 0 · Accepted Answer

The problems you have are the following:

A Datatable expects to be returned data JSON , with the following parameters set :

draw (integer) : The draw counter to which this object responds. The draw parameter sent as part of the data request. Note that strongly recommends, for security reasons , that you set this parameter to an integer number, instead of simply returning to the client what you sent in the draw parameter, to avoid attacks Cross Site Scripting (XSS) .



recordsTotal (integer) : Total records, before filtering ( that is, the total number of records in the database )



recordsFiltered (integer) : Total records after filtering ( that is, the total number of records after applying the filter, not just the number of records returned for this data page) .



data (arra) : The data that will be displayed in the table. This is an array of data source objects, one for each row, that will be used by DataTables. Note that the name of this parameter can be changed using the dataSrc property of the ajax option.



error (string) : Optional : If an error occurs during the execution of the server-side processing script, you can inform the user of this error by returning the error message. error that will be displayed with this parameter. Do not include if there is no error.

On the other hand, just to save you from possible headaches, you should set the respective headers to a response JSON .

Solution:

Applying the aforementioned, you could modify your PHP in the following way:

$datos = array();
while ($row = mysqli_fetch_array($resultado)) {
  $idventa = $row['idventa'];
  $mesa = $row['mesa'];
  $nombre = $row['nombre'];
  $apellido = $row['apellido'];
  $total = $row['total'];
  $fecha = $row['fecha'];
  $pago = $row['pago'];

  $datos[] = array('idventa' => $idventa, 'mesa' => $mesa, 
    'nombre' => $nombre, 'apellido' => $apellido,
    'total' => $total, 'fecha' => $fecha, 'pago' => $pago);
}

$recordsFiltered = count($datos);

// NOTA:
// Por ejemplo, si la consulta esta paginada (eg: LIMIT 0,10)
// deberías hacer otra para obtener el valor para $recordsTotal
// En este caso, hago de cuenta que no se ha paginado
$recordsTotal = $recordsFiltered;

header('Content-Type: application/json');
echo json_encode(array(
    'draw'=> (int) $_REQUEST['draw'],
    'recordsFiltered'=> $recordsTotal,
    'recordsTotal'=> $recordsFiltered,
    'data'=> $datos,
));