当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Trouble retrieving MySQL data using JSON

问题描述:

I am trying to use JSON w/PHP to retrieve data from MySQL database and have run into a problem. I get my table back from Server except when I include the Comments field in my query. I checked out the JSON in JSON LInt and that came back ok. The MySQL query checks out on its own. And looking in firebug I see SyntaxError: JSON.parse: bad control character in string literal at line 1 column 184 of the JSON data. I just get my header on the page. the code is:

example.php

<?php

ini_set('display_errors',1);

ini_set('display_startup_errors',1);

error_reporting(-1);

header("Access-Control-Allow-Origin: *");

header("Content-Type: application/json; charset=UTF-8");

$conn = new mysqli("localhost", "*****", "*****", "inventory_form");

$result = $conn->query("SELECT Comments, FName, LName, Eqpmnt_Brwd, Date_Taken, Brwd_Rsn, Service_Tag FROM Inventory");

$outp = "[";

while($rs = $result->fetch_array(MYSQLI_ASSOC)) {

if ($outp != "[") {$outp .= ",";}

$outp .= '{"Comments":"' . $rs["Comments"] . '",';

$outp .= '"FirstName":"' . $rs["FName"] . '",';

$outp .= '"Eqpmnt_Brwd":"'. $rs["Eqpmnt_Brwd"] . '",';

$outp .= '"Date_Taken":"'. $rs["Date_Taken"] . '",';

$outp .= '"Brwd_Rsn":"'. $rs["Brwd_Rsn"] . '",';

$outp .= '"ServiceTag":"'. $rs["Service_Tag"] . '"}';

}

$outp .="]";

$conn->close();

echo($outp);

?>

index.html:

<!DOCTYPE html>

<html>

<head>

<style>

h1 {

border-bottom: 3px solid #cc9900;

color: #996600;

font-size: 30px;

}

table, th , td {

border: 1px solid grey;

border-collapse: collapse;

padding: 5px;

}

table tr:nth-child(odd) {

background-color: #f1f1f1;

}

table tr:nth-child(even) {

background-color: #ffffff;

}

</style>

</head>

<body>

<h1>SHS Inventory Form</h1>

<div id="id01"></div>

<script>

var xmlhttp = new XMLHttpRequest();

var url = "inventory_table.php";

xmlhttp.onreadystatechange=function() {

if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {

myFunction(xmlhttp.responseText);

}

}

xmlhttp.open("GET", url, true);

xmlhttp.send();

function myFunction(response) {

var arr = JSON.parse(response);

var i;

var out = "<table>";

for(i = 0; i < arr.length; i++) {

out += "<tr><td>" +

arr[i].Comments +

"</td><td>" +

arr[i].FirstName +

"</td><td>" +

arr[i].Eqpmnt_Brwd +

"</td><td>" +

arr[i].Date_Taken +

"</td><td>" +

arr[i].Brwd_Rsn +

"</td><td>" +

arr[i].ServiceTag +

"</td></tr>";

}

out += "</table>"

document.getElementById("id01").innerHTML = out;

}

</script>

</body>

</html>

and a sample of response text is:

"[{"Comments":"","FirstName":"Nadine","Eqpmnt_Brwd":"Apple Video Dongle","Date_Taken":"2014-09-05","Brwd_Rsn":"Returned","ServiceTag":""},{"Comments":"Wants to check out hovercam. Can retrieve it if anyone needs a hovercam.","FirstName":"Nicole ","Eqpmnt_Brwd":"Hovercam","Date_Taken":"2014-09-04","Brwd_Rsn":"Borrowed","ServiceTag":"075642"},{"Comments":"with SD card All six cameras borrowed on 9/8/14 will be used throughout that school week.Expected return date is Monday 9/15/14","FirstName":"George","Eqpmnt_Brwd":"Nikon D3100 Camera","Date_Taken":"2014-09-08","Brwd_Rsn":"Borrowed","ServiceTag":"074753"},{"Comments":"w/ SD card "

Is there a problem with some of the text in the Comments field needing to be escaped? I've looked online but cant find much on the topic. Realize you should probably use PDO for this but good luck finding examples. I will work on that once I get this example working properly.

网友答案:

As @Barmar said use json_encode instead of trying to construct a JSON string by hand. For example:

$data = array();
while($rs = $result->fetch_array(MYSQLI_ASSOC)) {
    $data[] = $rs;
}
$conn->close();

echo json_encode($data);

In response to your comment/question about PDO... With PDO you can simplify this by using PDOStatement::fetchAll:

$pdo = new PDO($dsn, $user, $pass);
$stmt = $pdo->query("SELECT Comments, FName, LName, Eqpmnt_Brwd, Date_Taken, Brwd_Rsn, Service_Tag FROM Inventory");

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($data);
分享给朋友:
您可能感兴趣的文章:
随机阅读: