Problem with Ajax, Server-side request, Json Data and MultiSelect input

ArturoSantamaria

Hi!

I'm currently having some trouble trying to do an ajax load of data in my data table.
I have a form in wich I have two multiselect input required to do a query in my DB
My form sends the data through Ajax to a php file called 'ConsultaCxP.php'

Here are my codes

HTML

<form id="cxp" method="POST">
                        <div class="form-row">
                            <div class="form-group col-md-6">
                                <label for="sucursal">Sucursal</label>
                                <select class="custom-select" data-placeholder="Seleccione una sucursal..." multiple="multiple" name="sucursal[]">
                                    <option value=""></option>
                                    <?php echo RepositorioCuentasPagar::sucursales() ?> <!--gets options from a query-->
                                </select>
                            </div>
                            <div class="form-group col-md-6">
                                <label for="proveedor">Proveedor</label>
                                <select class="custom-select" data-placeholder="Seleccione un proveedor..." multiple="multiple" name="proveedor[]">
                                    <option value=""></option>
                                    <?php echo RepositorioCuentasPagar::proveedores() ?> <!--gets options from a query-->
                                </select>
                            </div>
                        </div>
                        <div class="form-row">
                            <div class="form-group col-md-6">
                                <label for="fecha1">Fecha del:</label>
                                <input type="text" name="fecha1" class="form-control" id="fecha1">
                            </div>
                            <div class="form-group col-md-6">
                                <label for="fecha2">Fecha al:</label>
                                <input type="text" name="fecha2" class="form-control" id="fecha2">
                            </div>
                        </div>
                        <div class="form-group">
                            <div class="custom-control custom-radio custom-control-inline">
                                <input type="radio" id="tipo1" value="resumido" name="radioInline" class="custom-control-input">
                                <label class="custom-control-label" for="tipo1">Resumido</label>
                            </div>
                            <div class="custom-control custom-radio custom-control-inline">
                                <input type="radio" id="tipo2" value="detallado" name="radioInline" class="custom-control-input">
                                <label class="custom-control-label" for="tipo2">Detallado</label>
                            </div>

                        </div>
                        <button type="submit" class="btn btn-primary">Generar reporte</button>
                    </form>

JS

$(document).ready(function (e) {
                $("#cxp").submit(function (e) {
                    $('#reporteR').hide();
                    $('#reporteR').DataTable().clear();
                    $('#reporteR').dataTable().fnDestroy();

                    if ($('#cxp input[name=radioInline]:checked').val() === 'resumido') {
                        $('#reporteR').show();
                        $('#reporteR').dataTable({
                            "ajax": {
                                "url": "ConsultaCxP.php",
                                "type": "POST",
                                "data": function (d) {
                                    d.form = $('form#cxp').serializeArray();
                                },
                                "dataSrc": ""
                            },
                            "columns": [
                                {"data": "idsucursal"},
                                {"data": "nombre"},
                                {"data": "saldoVencido"},
                                {"data": "saldoPendiente"}
                            ]
                        });
                    }
                    e.preventDefault();
                });
            });

PHP ajax destination

$tipo = filter_input(INPUT_POST, "radioInline", FILTER_SANITIZE_STRING);
$proveedor = filter_input(INPUT_POST, "proveedor", FILTER_SANITIZE_STRING, FILTER_REQUIRE_ARRAY);
$sucursal = filter_input(INPUT_POST, "sucursal", FILTER_SANITIZE_STRING, FILTER_REQUIRE_ARRAY);
$fecha1 = filter_input(INPUT_POST, "fecha1");
$fecha2 = filter_input(INPUT_POST, "fecha2");

if ($tipo === "resumido") {
    $idP = '';
    $idS = '';
    $array = array();
    $aP = (array) $proveedor;
    $aS = (array) $sucursal;
    foreach ($aP as $p => $value) {
        $idP .= "?,";
        array_push($array, $value);
    }
    $cadenaP = substr($idP, 0, strlen($idP) - 1);
    foreach ($aS as $s => $value) {
        $idS .= "?,";
        array_push($array, $value);
    }
    $cadenaS = substr($idS, 0, strlen($idS) - 1);
    
    array_push($array, $fecha1,$fecha2);
        
    $select = "cp.idsucursal, p.nombre, SUM(IFNULL((SELECT total from cxp WHERE fechavencimiento < CURDATE() and cxp.referencia = 'SIN PAGAR' AND cxp.ID = cp.ID),0))
as saldoVencido, 
SUM(IFNULL((SELECT total from cxp WHERE fechavencimiento >= CURDATE() AND cxp.referencia = 'SIN PAGAR' AND cxp.ID = cp.ID), 0))
as saldoPendiente"; //manejamos los campos a seleccionar aparte
//realizamos nuestra consulta
    $resultado = Sentencias::ConsultaLibre("$select", "cxp as cp, proveedores as p", "cp.idproveedor = p.idproveedor AND cp.referencia = 'SIN PAGAR' AND cp.idproveedor IN (".$cadenaP.") AND cp.idsucursal IN (".$cadenaS.") AND cp.fechaaplicacion BETWEEN ? AND ? GROUP BY p.idproveedor", $array);
    $tabla = array(); //declaramos variable tabla

    
    foreach ($resultado as $c) {
        $tabla['data'][] = $c;
    }
    
    echo json_encode($tabla);
} else if ($tipo === "detallado") {
    
}

I can't figure out where is my problem, the only error I'm receiving is from datatables.js

DataTables warning: table id=reporteR - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

ALSO:

I have tested my PHP file using Postman and I'm having at least a response.

And I checked if my form is actually sending data to my PHP file

Please Help!

Thank you

kthorngren

Did you follow the troubleshooting steps at the link provided in the error?

If so what is the response shown in the browser's network tools (The Response Tab in your last screenshot)?

Based on the data in the postman test you will want to remove "dataSrc": "" since your dataSrc is the default data.

Kevin

ArturoSantamaria

Ok, made some changes and it seems that my js script it's not sending the multiselect data... or maybe I'm not reading it properly because the response my browser network tool shows me is this:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND cp.idsucursal IN () AND cp.fechaaplicacion BETWEEN NULL AND NULL GROUP BY '

note the () doesn't contain any data...

Again this is my html code for my multiselect

                        <div class="form-row">
                            <div class="form-group col-md-6">
                                <label for="sucursal">Sucursal</label>
                                <select class="custom-select" data-placeholder="Seleccione una sucursal..." multiple="multiple" name="sucursal[]">
                                    <option value=""></option>
                                    <?php echo RepositorioCuentasPagar::sucursales() ?>
                                </select>
                            </div>
                            <div class="form-group col-md-6">
                                <label for="proveedor">Proveedor</label>
                                <select class="custom-select" data-placeholder="Seleccione un proveedor..." multiple="multiple" name="proveedor[]">
                                    <option value=""></option>
                                    <?php echo RepositorioCuentasPagar::proveedores() ?>
                                </select>
                            </div>
                        </div>

JS Script

$(document).ready(function (e) {
                $("#cxp").submit(function (e) {
                    $('#reporteR').hide();
                    $('#reporteR').DataTable().clear();
                    $('#reporteR').dataTable().fnDestroy();

                    if ($('#cxp input[name=radioInline]:checked').val() === 'resumido') {
                        $('#reporteR').show();
                        $('#reporteR').DataTable({
                            "ajax": {
                                "url": "ConsultaCxP.php",
                                "type": "POST",
                                "data": function (d) {
                                    d.form = $('form#cxp').serialize();
                                }
                            },
                            "columns": [
                                {"data": "idsucursal"},
                                {"data": "nombre"},
                                {"data": "saldoVencido"},
                                {"data": "saldoPendiente"}
                            ]
                        });
                    }
                    e.preventDefault();
                });
            });

And the PHP part that reads all the _POST variables

$tipo = filter_input(INPUT_POST, "radioInline", FILTER_SANITIZE_STRING);
$proveedor = filter_input(INPUT_POST, "proveedor", FILTER_SANITIZE_STRING, FILTER_REQUIRE_ARRAY);
$sucursal = filter_input(INPUT_POST, "sucursal", FILTER_SANITIZE_STRING, FILTER_REQUIRE_ARRAY);
$fecha1 = filter_input(INPUT_POST, "fecha1");
$fecha2 = filter_input(INPUT_POST, "fecha2");

I executed this to check if the multiselect input data was sent to my PHP file

var_dump($sucursal);

And got a NULL response