我正在尝试使用JSON w/PHP从MySQL数据库中检索数据,但遇到了问题。除非在查询中包含"注释"字段,否则我会从服务器取回表。我在JSON LInt中检查了JSON,结果很好。MySQL查询自己检查。在firebug中,我看到了JSON数据的第1行第184列的字符串文字中的SyntaxError:JSON.parse:错误的控制字符。我刚把我的标题拿到页面上。代码是: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>
并且响应文本的示例是:
"[{"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 "
"注释"字段中的某些文本是否存在需要转义的问题?我在网上查了一下,但找不到太多关于这个话题的内容。意识到你可能应该使用PDO,但祝你好运,找到例子。一旦我让这个例子正常工作,我会努力做到这一点。
正如@Barmar所说,使用json_encode
,而不是试图手动构造JSON字符串。例如:
$data = array();
while($rs = $result->fetch_array(MYSQLI_ASSOC)) {
$data[] = $rs;
}
$conn->close();
echo json_encode($data);
针对您对PDO的评论/问题。。。使用PDO,您可以使用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);