JSON invalidated when it comes to query php mysql, but it works when it is called json format

1

After reading hundreds of similar problems and unable to find the solution to the problem, I ask the following. I have a php file where the data is listed by datatables , which come by ajax from another php file.

The problem is that when I want to call the data in that file, an error occurs:

  

DataTables warning: table id = list - Invalid JSON response. For more   information about this error, please see link .

I carried out the steps of verifying the correct format of my JSON chain in several validators, including link , giving me as a result, a valid JSON.

When I save my generated JSON string and save it to another file, for example, test.json and call it from datatables via ajax, it works fine. I've been doing the same for 10 hours and I have not been able to find the solution. If someone could help me, I would greatly appreciate it.

This is my JSON string:

{
"data": [{
    "0": "8",
    "folio": "8",
    "1": null,
    "etapa": null,
    "2": "2018",
    "ano": "2018",
    "3": "Diciembre",
    "nombre_mes": "Diciembre",
    "4": "Corona Zirconio PFP",
    "procedimiento": "Corona Zirconio PFP",
    "5": "56000",
    "valor": "56000",
    "6": "2018-12-04",
    "fecha_envio": "2018-12-04",
    "7": "12",
    "id": "12"
}, {
    "0": "14",
    "folio": "14",
    "1": "1",
    "etapa": "1",
    "2": "2018",
    "ano": "2018",
    "3": "Diciembre",
    "nombre_mes": "Diciembre",
    "4": "Corona Zirconio PFP",
    "procedimiento": "Corona Zirconio PFP",
    "5": "56000",
    "valor": "56000",
    "6": "2018-12-05",
    "fecha_envio": "2018-12-05",
    "7": "12",
    "id": "12"
}, {
    "0": "15",
    "folio": "15",
    "1": "1",
    "etapa": "1",
    "2": "2018",
    "ano": "2018",
    "3": "Diciembre",
    "nombre_mes": "Diciembre",
    "4": "Espiga Mu\u00f1on Colado",
    "procedimiento": "Espiga Mu\u00f1on Colado",
    "5": "42000",
    "valor": "42000",
    "6": "2018-12-18",
    "fecha_envio": "2018-12-18",
    "7": "12",
    "id": "12"
}, {
    "0": "14",
    "folio": "14",
    "1": "3",
    "etapa": "3",
    "2": "2018",
    "ano": "2018",
    "3": "Diciembre",
    "nombre_mes": "Diciembre",
    "4": "Incrustacion Metalica Overlay",
    "procedimiento": "Incrustacion Metalica Overlay",
    "5": "70000",
    "valor": "70000",
    "6": "2018-12-29",
    "fecha_envio": "2018-12-29",
    "7": "12",
    "id": "12"
}]
}

This is the code that the JSON generates from php:

function getArraySQL($sql){

$conexion = connectDB();

    mysqli_set_charset($conexion, "utf8"); 

if(!$result = mysqli_query($conexion, $sql)) die(); 

$rawdata = array(); 

$i=0;

while($row = mysqli_fetch_array($result))
{
  $rawdata['data'][$i] = array(
    'col_1' => $row['folio'],
    'col_2' => $row['etapa'],
    'col_3' => $row['ano'],
    'col_4' => $row['nombre_mes'],
    'col_5' => $row['procedimiento'],
    'col_6' => $row['valor'],
    'col_7' => $row['fecha_envio']
  );
    $i++;
}

disconnectDB($conexion); 

return $rawdata;
}

    $myArray = getArraySQL($sql);
    echo json_encode($myArray);

and so called from datatables:

$(document).ready(function() {
  var table = $('#listado').DataTable({
    "ajax" : {
        "url" : "jsonprueba.php",
        "type" : "POST"
    },
    "columns" : [
    {
      "sClass": "alignRight",
      "data": "col_1"
    },{
      "sClass": "alignRight",
      "data": null,
      "render": function (data, type, row, meta){
        var etapa = row['col_2'];
        if (etapa == null){
          return 'N/A';
        }else{
          return ' '+etapa+' ';
        }
      }
    },{
      "sClass": "alignRight",
      "data": "col_3"
    },{
      "sClass": "alignRight",
      "data" : "col_4"
    },{
      "sClass": "alignRight",
      "data" : "col_5"
    },{
      "sClass": "alignRight",
      "data" : "col_6"
    },{
      "sClass": "alignRight",
      "data" : "col_7",
      "render": function (data) {
        var dateString = new Date(data);
        return moment.utc(dateString).format('DD/MM/YYYY');
      }
    }
    ],
    "columnDefs": [
    {
        'targets': 5,
        "render": function (data) {
           return FormatValue(data);
        }
    }
    ],
      "iDisplayLength": 25,
      "language": {
      "emptyTable": "SIN REGISTROS",
      "infoEmpty": "",
        "search": "Buscar _INPUT_ ",
        "info": "Mostrando Pagina _PAGE_ de _PAGES_",
        "lengthMenu": "Mostrando _MENU_ Registros",
        "infoFiltered": "(Busqueda Desde _MAX_ Registros Totales)",
        "paginate": {
            "next": "Siguiente",
            "previous": "Anterior"
        }
    },
  });
});

If there is a lack of information to clarify the question, I will gladly add it. In advance, thank you very much as always to all.

    
asked by maha1982 04.01.2019 в 04:32
source

2 answers

0

DataTables is waiting for data types Integer and you are returning them as String from PHP in your JSON. That's why you're receiving an error like:

  

DataTables warning: table id = list - Invalid JSON response

In your cycle while converts all data types String needed to Integer . It is evident that the code that you put while is not the same one that creates all the JSON, but the idea is the same. Return the fields that have as a key in your array (0,1,2,3,4,5,6 and 7) as integers and their value wherever you use numbers that are also type Integer .

You can use the intval function to convert from String to Integer . You can find more references about this feature in PHP in this link .

    
answered by 04.01.2019 в 04:43
0

replaces

echo json_encode($myArray);

for

header('Content-Type: application/json');
echo json_encode($myArray, JSON_NUMERIC_CHECK);

The first line informs the browser that you are sending json, in the second it passes all the numbers to type number.

    
answered by 04.01.2019 в 06:23