json not returning data

Question

g,day. could someone help me make sense of why my code is not returing the results to json? i am sure there is an error in my code but cannot seem to find it. what is supposed to happen is the values of $dept and $box are supposed to be returned in an alert, but this does not happen. thanks

<?php

function runSQL($rsql) {
 $hostname = "localhost";
 $username = "root";
 $password = "";
 $dbname   = "sample";
 $connect = mysql_connect($hostname,$username,$password) or die ("Error: could not connect to database");
 $db = mysql_select_db($dbname);
 $result = mysql_query($rsql) or die ('test'); 
 return $result;
 mysql_close($connect);
}
$new = 1;

$items = rtrim($_POST['items'],",");
$sql = "SELECT * FROM `boxes` WHERE id IN ($items)";
$result = runSQL($sql);


$i = 0;
$rows = mysql_num_rows($result);
while ($row = mysql_fetch_array($result)) {
   if ( $i < $rows ) {

      $dept .= $row['department'] . "," ;
      $box .= $row['custref'] . "," ;
   } else { 

   $dept .= $row['department'];
   $box .= $row['custref'];
   }
   $i++;
}


/*$items = rtrim($_POST['items'],",");
$sql = "UPDATE `boxes` SET status = 'Deleted' WHERE id IN ($items)";
$result = runSQL($sql);*/

//$sql = "INSERT INTO `act` (`item`) VALUES (\''.$box.'\')";
//$result = runSQL($sql);

$total = count(explode(",",$items)); 
$result = runSQL($sql);
$total = mysql_affected_rows(); 
/// Line 18/19 commented for demo purposes. The MySQL query is not executed in this case. When line 18 and 19 are uncommented, the MySQL query will be executed. 
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT" );
header("Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . "GMT" );
header("Cache-Control: no-cache, must-revalidate" );
header("Pragma: no-cache" );
header("Content-type: text/x-json");
$json = "";
$json .= "{\n";
$json .= "dept: '".$dept.",'\n";
$json .= "box: '".$box."'\n";
$json .= "}\n";
echo $json;
?>

the ajax

success: function(data){
dept = data.dept;
box = data.box;
alert("You have successfully deleted\n\r\n\rBox(es): "+data.dept+data.box);
$("#flex1").flexReload();
   }

Answer 1

Your JSON is incorrect. It is formatted like this:

{
dept: '...'
box: '...'
}

It should be:

{
"dept": "..."
"box": "..."
}

The identifiers need quotation marks around them, and strings are enclosed in quotation marks, not apostrophes.

Answer 2

RFC4627 defines the media type for JSON as "application/json".

Answer 3

$sql = "SELECT * FROM `boxes` WHERE id IN ($items)";

SQL-injection vulnerability. You must mysql_real_escape_string each individual item if they are strings, or ensure they are only numbers if that's what they're supposed to be (eg with intval()). Or use parameterised queries.

header("Content-type: text/x-json");

application/json.

$json .= "dept: '".$dept.",'\n";

Apart from JSON needing double-quotes around keys and string values, you would also need to JavaScript-string-literal-escape values being injected into a string. Otherwise an apostrophe/quote/backslash/newline would break the string. You can mostly do this with addslashes().

But really, there's no call to be constructing your own JSON values (or other JavaScript literals). PHP gives you json_encode(). It's simpler, faster, more reliable. Use it.

echo json_encode(array(
    'dept'=>$dept,
    'box'=>$box
));