I want to make a json request with jquery to populate a wysihtml5 textarea but it doesn't work
$("#calendarAnchor").click(function(event) {
$.getJSON( "php/databaseHandler.php?action=GET_DATE_DETAILS&day=1&month=11&year=2014&username=bd107a66ba", function( json ) {
alert( "JSON Data: " + json ); // not executed
});
});
When i put my json in jsonlint then it doesn't validate
({'id': '1124','day': '1','month': '11','year': '2014','red_day': 'ja','name_day': ['Allhelgona'],'images':[{'id': '2','url': 'svala_mini15.png','description': 'Idag är det soligt','category_id': '1','slot_id': '0'},{'id': '1','url': 'img/arstider/host/Flyttfagel_sadesarla_mini15.png','description': 'Idag regnar det, men vi har kul ändå!','category_id': '1','slot_id': '1'}],'holidays':[{'id': '1','name': 'Allhelgon','description': 'Nu firar man!'}],'dateDescription':[{'description': 'Idag!'}]})
WWhy is the json not valid? Should I change the output or use jquery some other way to read the fields? The php that outputs the json is
function getDateDetails($day, $month, $year, $username, $db){
$sql = <<<SQL
SELECT calendar_dates.id,
calendar_dates.day,
calendar_dates.month,
calendar_dates.year,
calendar_dates.name_day,
calendar_dates.red_day
FROM calendar_dates
WHERE calendar_dates.day=$day
AND calendar_dates.month=$month
AND calendar_dates.year=$year
SQL;
$ret = "{";
$isFirst = true;
if(!$result = $db->query($sql)){
die('There was an error running the query [' . $db->error . ']');
}
while($row = $result->fetch_assoc()){
if($isFirst){
$names = join("','", array_filter(explode(";", "'" . $row['name_day'] . "'")));
$imagesJson = getImagesJson($row['id'], $username, $db);
$holidaysJson = getHolidaysJson($row['id'], $username, $db);
$dateDescriptionJson = getDateDescriptionJson($row['id'], $username, $db);
$ret .= "'id': '" . $row['id'] . "',";
$ret .= "'day': '" . $row['day'] . "',";
$ret .= "'month': '" . $row['month'] . "',";
$ret .= "'year': '" . $row['year'] . "',";
$ret .= "'red_day': '" . $row['red_day'] . "',";
$ret .= "'name_day': [";
$ret .= $names;
$ret .= "],";
$ret .= "'images':";
$ret .= $imagesJson . ",";
$ret .= "'holidays':";
$ret .= $holidaysJson . ",";
$ret .= "'dateDescription':";
$ret .= $dateDescriptionJson . ",";
$isFirst = false;
}
}
$ret = rtrim($ret, ",");
$ret .= "}";
return($ret);
}
With help from Dave, this is valid json
{
"id": "1124",
"day": "1",
"month": "11",
"year": "2014",
"red_day": "ja",
"name_day": [
"Allhelgon "
],
"images": [
{
"id": "2",
"url": "mini15.png",
"description": "Idag är det soligt!",
"category_id": "1",
"slot_id": "0"
},
{
"id": "1",
"url": "sadesarla_mini15.png",
"description": "Idag regnar det!",
"category_id": "1",
"slot_id": "1"
}
],
"holidays": [
{
"id": "1",
"name": "Allhelgon",
"description": "Nu!"
}
],
"dateDescription": [
{
"description": "Idag!"
}
]
}
My new php (still injection vulnerable from users.username = '$username' ?) is
function getDateDescription($dateId, $username, $db){
$sql = <<<SQL
SELECT calendar_date_description.description
FROM calendar_date_description
INNER JOIN calendar_users
ON calendar_users.username = '$username'
WHERE calendar_date_description.user_id= calendar_users.id
AND calendar_date_description.date_id = $dateId
SQL;
$ret = "[";
$description ="";
if(!$result = $db->query($sql)){
die('There was an error running the query [' . $db->error . ']');
}
while($row = $result->fetch_assoc()){
$description = $row['description'];
}
return($description);
}
function getDateDetails($day, $month, $year, $username, $db){
$sql = <<<SQL
SELECT calendar_dates.id,
calendar_dates.day,
calendar_dates.month,
calendar_dates.year,
calendar_dates.name_day,
calendar_dates.red_day
FROM calendar_dates
WHERE calendar_dates.day=$day
AND calendar_dates.month=$month
AND calendar_dates.year=$year
SQL;
//$ret = "{";
$isFirst = true;
if(!$result = $db->query($sql)){
die('There was an error running the query [' . $db->error . ']');
}
$ret = array();
while ($row = $result->fetch_assoc()) {
$names = array_filter(explode(";", $row['name_day']));
$dateDescriptionJson = getDateDescription($row['id'], $username, $db);
$ret = array(
'id' => $row['id'],
'day' => $row['day'],
'month' => $row['month'],
'year' => $row['year'],
'red_day' => $row['red_day'],
'name_day' => $names,
'dateDescription' => $dateDescriptionJson
);
}
$ret = json_encode($ret);
return ($ret);
}
I'm glad to have helped but I was actually recommending json_encode. The problem is from single quotes but the underlying issue is that you're constructing this complex JSON string by hand. Sometimes I do this, but only in extremely rare cases such as one level JSON strings, {"error":"true"}, but this is not the case for you.
Please try this method instead:
$ret = array();
while ($row = $result->fetch_assoc()) {
$names = array_filter(explode(";", $row['name_day']));
$imagesJson = getImages($row['id'], $username, $db);
$holidaysJson = getHolidays($row['id'], $username, $db);
$dateDescriptionJson = getDateDescription($row['id'], $username, $db);
$ret = array(
'id' => $row['id'],
'day' => $row['day'],
'month' => $row['month'],
'year' => $row['year'],
'red_day' => $row['red_day'],
'name_day' => $names,
'images' => $imagesJson,
'holidays' => $holidaysJson,
'dateDescription' => $dateDescriptionJson
);
}
$ret = json_encode($ret);
return ($ret);
I would highly recommend keeping everything in the PHP array format, then when the data is ready to be outputted to the client, then do a final json_encode.
So this would mean:
getImagesJson -> getImages
getHolidaysJson -> getHolidays
dateDescriptionJson -> dateDescription
These functions will then return PHP arrays instead of JSON. This will allow you to manipulate the data across functions easier. Simply apply the same techniques to those functions and this will make your life so much easier.