I'll try to keep this question informative and straight to the point as best as possible.
What I am trying to achieve
First of all, I am attempting to get my SQL table rows encoded to JSON with a PHP script followed by some JavaScript code which gets the JSON that has been encoded and appends it to a table on my website.
My problem
My PHP script works totally fine, it gets data from the database however it only works when I've applied DESC
to my SQL. Otherwise it breaks and I get a JavaScript error:
Error talking to server SyntaxError: Unexpected end of JSON input
at parse (<anonymous>)
at Ut (VM1393 jquery.min.js:2)
at k (VM1393 jquery.min.js:2)
at XMLHttpRequest.<anonymous> (VM1393 jquery.min.js:2)
"Error talking to server" is my debug output I've written in index.html
What I've tried
Tried to use a different browser, Firefox gives this error:
"JSON.parse: unexpected character at line 1 column 1 of the JSON data"
The JSON encoder.
<?php
header('Content-Type: application/json');
include 'php/connectdb.php';
$sth = mysqli_query($conn, "SELECT column FROM table ORDER BY column ASC LIMIT 300;");
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
$rows[] = $r;
}
echo json_encode($rows,JSON_PRETTY_PRINT);
?>
Relevant JavaScript code in index.html (excluded table append etc.)
function getFromMySql(command){
if (command===undefined) activity="";
return Promise.resolve($.getJSON("jsonencode.php",{
command: command,
}).done(function(data){
if (data===undefined) {
console.log("data undefined");
return false;
}
else if (data.hasOwnProperty('error')) {
console.log("error",data.error);
return false;
}
else {
// console.log(data);
}
return data;
}).fail(function(jqXHR, status, error) {
if(status == 'parseerror'){
console.log('invalid json',error);
if (command=='saveactivity') displayError();
return false;
} else {
console.log('Error talking to server',error);
if (command=='saveactivity') displayError();
return false;
}
})
);
}
Sample of working JSON (limit 2)
[
{
"id": "2959",
"alma": "991516634674702204",
"type": "Book",
"isbn": "0704339986",
"author": "Zoline, Pamela",
"title": "Busy about the tree of life",
"dscrptn": "",
"limg": "",
"simg": "",
"rating": null,
"categories": "Refinements|Women's Literary Fiction|Short Stories|English|Font Size (format_browse-bin)|Fiction Complete|Women Writers & Fiction|Fiction|Regular Size|Lesbian|Women's Popular Fiction|Contemporary Fiction",
"source": "bookmooch",
"recommended": "
"
},
{
"id": "10268",
"alma": "991516634674702204",
"type": "Book",
"isbn": "0704339986",
"author": "Zoline, Pamela",
"title": "Busy about the tree of life",
"dscrptn": "",
"limg": "http:\/\/images.paperbackswap.com\/xl\/89\/9989\/9780704339989.jpg",
"simg": "http:\/\/images.paperbackswap.com\/m\/89\/9989\/9780704339989.jpg",
"rating": "0",
"categories": null,
"source": "paperbackswap",
"recommended": null
}
]
What I've noticed
Currently I noticed that this works
SELECT column FROM table ORDER BY column DESC LIMIT 300;
While this does not work for some odd reason
SELECT column FROM table ORDER BY column ASC LIMIT 300;
Further investigating has given me that the SQL commands executed works perfectly fine when outputted in a .json file, however does not output on my website. In other words, my encoder should be working just fine.
echo "writing data into json file...
";
$fp = fopen('C:\wamp64\www\scifiprosjekt\scifidb2.json', 'w');
fwrite($fp, json_encode($rows,JSON_PRETTY_PRINT));
fclose($fp);
echo "completed.
";
This was added to prove that there is something wrong with outputting it on my website.