score:0

Accepted answer

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="//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>

score:0

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; ?>");


Related Query

More Query from same tag