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.