I am facing a problem whilst calling a php script that uses sql to query a MySQL database. The code is working offline but when I upload to the server, the code loops through 5 times and returns a strange json string. I've checked the sql in phpMyAdmin and it returns the correct values.
I'm calling the script using the following javascript:
var jsonData1 = $.ajax({
url: "php/ResidualArisingsdata.php?PrimaryKey=<?php echo $primarykey ?>",
dataType: "json",
async: false
}).responseText;
and the php script is (with login details removed)
<?php
$_SESSION['url'] = $_SERVER['REQUEST_URI'];
$primarykey = $_GET['PrimaryKey'];
// These variables define the connection information for your MySQL database
$username = "xxx";
$password = "xxx";
$host = "localhost";
$dbname = "xxx";
$mysqli = new mysqli($host, $username, $password, $dbname);
if (mysqli_connect_errno()) {
printf("Connect failed: %s
", mysqli_connect_error());
exit();
}
$SQLString = "SELECT PrimaryKey,
Name,
`HouseholdRecyclingRate2005/06`,
`HouseholdRecyclingRate2006/07`,
`HouseholdRecyclingRate2007/08`,
`HouseholdRecyclingRate2008/09`,
`HouseholdRecyclingRate2009/10`,
`HouseholdRecyclingRate2010/11`,
`HouseholdRecyclingRate2011/12`
FROM `districts_recyclingrates`
WHERE `districts_recyclingrates`.PrimaryKey =" . $primarykey;
$result = $mysqli->query($SQLString);
$rows = array();
$table = array();
$table['cols'] = array(
array('label' => 'Year', 'type' => 'string'),
array('label' => 'Name', 'type' => 'number'),
array('label' => 'UK Average', 'type' => 'number')
);
foreach($result as $r) {
$temp = array();
$temp[] = array('v' => "05/06");
$temp[] = array('v' =>(int) $r['`HouseholdRecyclingRate2005/06`']);
$temp[] = array('v' => "25.2");
$rows[] = array('c' => $temp);
unset($temp);
$temp[] = array('v' => "06/07");
$temp[] = array('v' =>(int) $r['HouseholdRecyclingRate2006/07']);
$temp[] = array('v' => "29.7");
$rows[] = array('c' => $temp);
unset($temp);
$temp[] = array('v' => "07/08");
$temp[] = array('v' =>(int) $r['HouseholdRecyclingRate2007/08']);
$temp[] = array('v' => "33.6");
$rows[] = array('c' => $temp);
unset($temp);
$temp[] = array('v' => "08/09");
$temp[] = array('v' =>(int) $r['HouseholdRecyclingRate2008/09']);
$temp[] = array('v' => "36.4");
$rows[] = array('c' => $temp);
unset($temp);
$temp[] = array('v' => "09/10");
$temp[] = array('v' =>(int) $r['HouseholdRecyclingRate2009/10']);
$temp[] = array('v' => "38.2");
$rows[] = array('c' => $temp);
unset($temp);
$temp[] = array('v' => "10/11");
$temp[] = array('v' =>(int) $r['HouseholdRecyclingRate2010/11']);
$temp[] = array('v' => "40.3");
$rows[] = array('c' => $temp);
unset($temp);
$temp[] = array('v' => "11/12");
$temp[] = array('v' =>(int) $r['HouseholdRecyclingRate2011/12']);
$temp[] = array('v' => "42.1");
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
// convert data into JSON format
$jsonTable = json_encode($table);
echo $jsonTable;
mysqli_close($mysqli);
?>
The returned json (identified with console.log) looks like the following where the database values are 0 and the foreach has looped through 5 times.
{"cols":[{"label":"Year","type":"string"},{"label":"Name","type":"number"},{"label":"UK Average","type":"number"}],"rows":[{"c":[{"v":"05\/06"},{"v":0},{"v":"25.2"}]},{"c":[{"v":"06\/07"},{"v":0},{"v":"29.7"}]},{"c":[{"v":"07\/08"},{"v":0},{"v":"33.6"}]},{"c":[{"v":"08\/09"},{"v":0},{"v":"36.4"}]},{"c":[{"v":"09\/10"},{"v":0},{"v":"38.2"}]},{"c":[{"v":"10\/11"},{"v":0},{"v":"40.3"}]},{"c":[{"v":"11\/12"},{"v":0},{"v":"42.1"}]},{"c":[{"v":"05\/06"},{"v":0},{"v":"25.2"}]},{"c":[{"v":"06\/07"},{"v":0},{"v":"29.7"}]},{"c":[{"v":"07\/08"},{"v":0},{"v":"33.6"}]},{"c":[{"v":"08\/09"},{"v":0},{"v":"36.4"}]},{"c":[{"v":"09\/10"},{"v":0},{"v":"38.2"}]},{"c":[{"v":"10\/11"},{"v":0},{"v":"40.3"}]},{"c":[{"v":"11\/12"},{"v":0},{"v":"42.1"}]},{"c":[{"v":"05\/06"},{"v":0},{"v":"25.2"}]},{"c":[{"v":"06\/07"},{"v":0},{"v":"29.7"}]},{"c":[{"v":"07\/08"},{"v":0},{"v":"33.6"}]},{"c":[{"v":"08\/09"},{"v":0},{"v":"36.4"}]},{"c":[{"v":"09\/10"},{"v":0},{"v":"38.2"}]},{"c":[{"v":"10\/11"},{"v":0},{"v":"40.3"}]},{"c":[{"v":"11\/12"},{"v":0},{"v":"42.1"}]},{"c":[{"v":"05\/06"},{"v":0},{"v":"25.2"}]},{"c":[{"v":"06\/07"},{"v":0},{"v":"29.7"}]},{"c":[{"v":"07\/08"},{"v":0},{"v":"33.6"}]},{"c":[{"v":"08\/09"},{"v":0},{"v":"36.4"}]},{"c":[{"v":"09\/10"},{"v":0},{"v":"38.2"}]},{"c":[{"v":"10\/11"},{"v":0},{"v":"40.3"}]},{"c":[{"v":"11\/12"},{"v":0},{"v":"42.1"}]},{"c":[{"v":"05\/06"},{"v":0},{"v":"25.2"}]},{"c":[{"v":"06\/07"},{"v":0},{"v":"29.7"}]},{"c":[{"v":"07\/08"},{"v":0},{"v":"33.6"}]},{"c":[{"v":"08\/09"},{"v":0},{"v":"36.4"}]},{"c":[{"v":"09\/10"},{"v":0},{"v":"38.2"}]},{"c":[{"v":"10\/11"},{"v":0},{"v":"40.3"}]},{"c":[{"v":"11\/12"},{"v":0},{"v":"42.1"}]}]}
Would appreciate some help. Thanks in advance.
You can debug JSON errors in php with json_last_error function.
An example would be:
<?php
// An invalid UTF8 sequence
$text = "\xB1\x31";
$json = json_encode($text);
$error = json_last_error();
var_dump($json, $error === JSON_ERROR_UTF8);
?>
Anyway, you have an information security flaws in you're code the first one called XSS (Cross site Scripting).
which is located in you'r javascript code, this line:
url: "php/ResidualArisingsdata.php?PrimaryKey=",
You are printing an unvalidated input from the user, read about it at:
http://en.wikipedia.org/wiki/Cross-site_scripting
And the second flaw is SQL injection, you include an unvalidated input from the user into you'r SQL query, this is very dangerous thing to do, read about it here: http://en.wikipedia.org/wiki/SQL_injection