带有DataTables的PHP由于引号而给出无效的JSON响应

Question

从数据库中获取数据并通过DataTables显示时遇到问题。我发现我的大部分数据都有单引号和双引号以及其他特殊字符。我尝试了PHP中的所有转义函数，但都不起作用。addslashes只检索40,000个数据中的59个数据。到目前为止，我有了以下代码：

PHP：

$query = mysqli_query($new_conn, "SELECT * FROM bill_of_materials");

$table = '';

while($row = mysqli_fetch_array($query)) {


    $table.= '{
        "allotment_code":"'.$row['allotment_code'].'",
        "activity":"'.$row['activity'].'",
        "category_name":"'.addslashes($row['category_name']).'",
        "description":"'.addslashes($row['description']).'"
    },';
}


$table = substr($table,0, strlen($table) - 1);

echo '{"data":['.$table.']}';


**jQuery data tables:**

    $(function() {

        $('#dataTables-example').DataTable( {
            "bLengthChange": false,
            "pageLength": 50,
            "bDeferRender": true,
            "bProcessing": true,
            "sPaginationType": "full_numbers",
            "ajax": base_url('ajax/ajaxGetBOM.php'),
            "columns":[
                {mData: "allotment_code"},
                {mData: "activity"},
                {mData: "category_name"},
                {mData: "description"}
            ],
            contentType: 'application/json',
            dataType: 'json'
        });
    })

    function base_url(path) {

        var url = 'https://192.168.3.254/'+path;
        return url;
    }

Answer 1

使用json_encode()函数使用JSON格式对响应进行正确编码。

$query = mysqli_query($new_conn, "SELECT * FROM bill_of_materials");

$data = array();   
while($row = mysqli_fetch_array($query)) {
   $data[] = array(
      "allotment_code" => $row["allotment_code"],
      "activity" => $row["activity"],
      "category_name" => $row["category_name"],
      "description" => $row["description"]
   );
}

header("Content-type: application/json");
echo json_encode(array("data" => $data));