I'm attempting to insert a JSON-encoded MySQL results set from a PHP variable into a Highcharts script.
I have successfully inserted a MySQL results list from a PHP variable to Highcharts in a different instance, formatted into Highchart-acceptable data by Group Concatenating commas and apostrophes in the SQL Select statement (which is a dirty but effective way to do it). My goal now is to make my chart display metadata in a tooltip, which I cannot get to work, but I think I am close with what I have.
--
Here is the PHP script to retrieve data from MySQL database and JSON encode it:
$mysqli = new mysqli('localhost','username','password','database');
$myArray = array();
if ($result = $mysqli->query("
SELECT
time_minutes.minutes*60+time_seconds.seconds AS y,
run_date.id AS ID,
run_date.date AS RunDate,
run_temp.temperature AS Temperature,
run_conditions.weather AS Conditions,
run_hydration.hydration_level AS Hydration
FROM run_conditions, run_date, run_hydration, run_notes, run_temp, time_minutes, time_seconds
WHERE run_date.id = run_conditions.id
AND run_date.id = run_hydration.id
AND run_date.id = run_notes.id
AND run_date.id = run_temp.id
AND run_date.id = time_minutes.id
AND run_date.id = time_seconds.id
")) {
while($row = $result->fetch_array(MYSQL_ASSOC)) {
$myArray[] = $row;
}
}
$raw_json = json_encode($myArray);
$json_without_quotes = str_replace('"', "", $raw_json);
$result->close();
$mysqli->close();
?>
The y value is what I intend the bar-height to be; the rest is metadata (Temperature, Conditions, etc.) I would like to appear in the tooltip.
The raw_json output looks like this:
[{"y":"1500.00",
"ID":"1",
"RunDate":"2015-10-19",
"Temperature":"87",
"Conditions":"Humid and hot",
"Hydration":"8"},
{"y":"1474.48",
"ID":"2",
"RunDate":"2015-10-21",
"Temperature":"80",
"Conditions":"Light rain",
"Hydration":"9"},
{"y":"1442.01",
"ID":"3",
"RunDate":"2015-10-22",
"Temperature":"82",
"Conditions":"Sunny",
"Hydration":"4"}]
The json_without_quotes output looks like this:
[{y:1500.00,
ID:1,
RunDate:2015-10-19,
Temperature:87,
Conditions:Humid and hot,
Hydration:8},
{y:1474.48,
ID:2,
RunDate:2015-10-21,
Temperature:80,
Conditions:Light rain,
Hydration:9},
{y:1442.01,
ID:3,
RunDate:2015-10-22,
Temperature:82,
Conditions:Sunny,
Hydration:4}]
Below is the base Highcharts script (which is functional) that I'm attempting to remodel using my own data (found at this JSfiddle).
<script>
$(function () {
var chart = new Highcharts.Chart({
chart: {
renderTo: 'chartchartchart',
type: 'column'
},
xAxis: {
categories: [(a dymanically-generated list of dates)]
},
series: [{
data: [{
This is where I would insert the json_without_quotes variable; the data below is formatted correctly, but I notice that it only contains integers; something must be changed to make this accept strings as arguments, but I do not know what must be changed.
y: 3,
locked: 1,
unlocked: 1,
potential: 1,
}, {
y: 5,
locked: 2,
unlocked: 1,
potential: 3,
}, {
y: 7,
locked: 3,
unlocked: 1,
potential: 3,
}]
}],
tooltip: {
formatter: function() {return ' ' +
'Locked: ' + this.point.locked + '<br />' +
'Unlocked: ' + this.point.unlocked + '<br />' +
'Potential: ' + this.point.potential;
}
}
});
});
</script>
<div id="chartchartchart" style="height: 400px"></div>
Thanks in advance for your help!
Success: may not be the best practices way to do it, but I did it.
Quotes could not be applied unilaterally. I used the str_replace method to strip the double quote characters out of the JSON output, and used the CONCAT SQL function to add single quote characters to the strings ,where I wanted them.
For instance, a date that is left as 2015-11-15 rather than '2015-11-15' is interpreted as 2015 minus 11 minus 15, so the date must be enclosed in quotes. On the other hand, the "y:" value is not parsed as the bar size if there are quotes around it. So quotes had to be applied individually. MySQL concatenating was the easiest way to do that IMO.
PHP:
<html>
<head>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
</head>
<body>
<?php
// Open database connection
$mysqli = new mysqli('localhost','username','password','database');
// Get the Date List and format it for the Highchart x-axis labels
$datequery = "
SELECT GROUP_CONCAT( \"'\", run_date.date, \"'\" )
FROM run_date
ORDER BY id
";
$dateresult = $mysqli->query($datequery);
$daterow = $dateresult->fetch_array(MYSQLI_NUM);
print "<pre>";
print_r($daterow);
print "</pre>";
$date_list = $daterow[0];
// End of Date List part
// Start of Chart Data to generate bars
$myArray = array();
if ($result = $mysqli->query("
SELECT
time_minutes.minutes*60+time_seconds.seconds AS y,
run_date.id AS ID,
CONCAT(\"'\", time_minutes.minutes, ':', time_seconds.seconds, \"'\") AS RunTime,
run_temp.temperature AS Temperature,
run_hydration.hydration_level AS Hydration,
CONCAT(\"'\", run_notes.note, \"'\") AS Notes,
CONCAT(\"'\", run_date.date, \"'\") AS RunDate
FROM run_conditions, run_date, run_hydration, run_notes, run_temp, time_minutes, time_seconds
WHERE run_date.id = run_conditions.id
AND run_date.id = run_hydration.id
AND run_date.id = run_notes.id
AND run_date.id = run_temp.id
AND run_date.id = time_minutes.id
AND run_date.id = time_seconds.id
")) {
while($row = $result->fetch_array(MYSQL_ASSOC)) {
$myArray[] = $row;
}
}
// End of Bar Chart Part
// Beginning of producing JSON object
$raw_json = json_encode($myArray); // Put the MySQL results into JSON format
$json_without_quotes = str_replace('"', "", $raw_json); // Strip the double-quotes out of the JSON; the SQL concatenates single quotes where they are needed.
// Print the quote-stripped JSON to test it
echo $json_without_quotes;
// Close the connection, of course
$result->close();
$mysqli->close();
?>
JSON output:
[{
y:1500.00,
ID:1,
RunTime:'25:0.00',
Temperature:87,
Hydration:8,
Notes:'Sed sagittis. Nam congue, risus semper porta volutpat, quam pede lobortis ligula, sit amet eleifend pede libero quis orci.',
RunDate:'2015-10-19'},
{y:1474.48,
ID:2,
RunTime:'24:34.48',
Temperature:80,
Hydration:9,
Notes:'Nullam orci pede, venenatis non, sodales sed, tincidunt eu, felis.',
RunDate:'2015-10-21'},
{y:1442.01,
ID:3,
RunTime:'24:2.01',
Temperature:82,
Hydration:4,
Notes:'Duis bibendum. Morbi non quam nec dui luctus rutrum. Nulla tellus.',
RunDate:'2015-10-22'}]
Highcharts Script:
<script>
$(function () {
var chart = new Highcharts.Chart({
chart: {
renderTo: 'chartdiv',
type: 'column'
},
xAxis: {
categories: [<?=$date_list?>]
},
series: [{
data: <?=$json_without_quotes?>
}],
tooltip: {
formatter: function() {return ' ' +
'ID: ' + this.point.ID + '<br />' +
'Run Date: ' + this.point.RunDate + '<br />' +
'Temperature: ' + this.point.Temperature + '<br />' +
'Hydration: ' + this.point.Hydration + '<br />' +
'Notes: ' + this.point.Notes;
}
}
});
});
</script>
<div id="chartdiv" style="height: 1000px"></div>
</body>
</html>
You need to convert your output to a Javascript object or else javascript will consider it a string and read it like "['Main Item', 'Second Item', 'Third Item']" instead of an object. You can do this using the JSON.Parse() function in javascript.
Try to replace [(a dymanically-generated list of dates)] with something like JSON.Parse("<?php echo $raw_json; ?>");