Trouble retrieving MySQL data using JSON

Question

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.

Don't try to create JSON by hand, that's what `json_encode` is for. Put everything into an array and encode it. — Barmar, Mar 01 '15 at 22:49
possible duplicate of [Removing Last Comma within while loop - PHP](http://stackoverflow.com/questions/25676956/removing-last-comma-within-while-loop-php) — Barmar, Mar 01 '15 at 22:57

prodigitalson · Accepted Answer · 2015-03-01T23:39:38.063

1

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);

edited Mar 01 '15 at 23:39

answered Mar 01 '15 at 23:06

prodigitalson

60,050
10
100
114

Thanks . Almost there. I get the Comments field ok and most others but my FName & Service_Tag fields are returning undefined. Never mind had to correct the field names on the index html page. Should this be implemented using PDO? or it doesnt make a difference. – Alan Mar 01 '15 at 23:26
Generally speaking i always lobby for `PDO` because its easier to use. If you are still in a stage of your project where you can switch then sure, I'd recommend it. But I would not use `PDO` for some things and `Mysqli` for others - stay consistent. – prodigitalson Mar 01 '15 at 23:37

Trouble retrieving MySQL data using JSON

1 Answers1