format json data for flot graph array

Question

I am pulling temperature sensor data from a MySQL db into a php file using the following php code:

<?php
$hostname = 'xxxxx';
$username = 'xxxxx';
$password = 'xxxxx';
$dbname="measurements";
$usertable="temperature";

try {
$dbh = new PDO("mysql:host=$hostname;dbname=measurements", 
                           $username, $password);
/*** The SQL SELECT statement ***/
$sth = $dbh->prepare("
   SELECT
   ROUND(AVG(`temperature`),1) AS temperature,
   TIMESTAMP(LEFT(`dtg`,16)) AS dtg
   FROM `temperature`
   GROUP BY LEFT(`dtg`,16)
   ORDER BY `dtg` DESC
   LIMIT 0,800
");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
/*** close the database connection ***/
$dbh = null;

}
catch(PDOException $e)
{
    echo $e->getMessage();
}

function make_pair($date, $amount) {
return array($date, $amount);
}
$json_data = json_encode($result, JSON_NUMERIC_CHECK); 
?>

I am then using javascript to plot this data in a flot graph:

 <script type="text/javascript">
//put array into javascript variable

var dataset1 = <?php echo json_encode($result); ?>;

//plot
$(document).ready(function () {
    $.plot($("#placeholder"),  dataset1  );
});

</script>

When I open the php file in a browser and look at the javscript console I can see that the data is coming through ok and being held as the variable dataset1

It looks like this:

//put array into javascript variable

var dataset1 = [{"temperature":"19.6","dtg":"2016-07-28 12:53:00"},{"temperature":"19.5","dtg":"2016-07-28 12:52:00"},{"temperature":"19.5","dtg":"2016-07-28 12:51:00"},{"temperature":"19.6","dtg":"2016-07-28 12:50:00"},{"temperature":"19.6","dtg":"2016-07-28 12:49:00"},{"temperature":"19.6","dtg":"2016-07-28 12:48:00"},{"temperature":"19.6","dtg":"2016-07-28 12:47:00"},{"temperature":"19.6","dtg":"2016-07-28 12:46:00"},{"temperature":"19.6","dtg":"2016-07-28 12:45:00"},{"temperature":"19.6","dtg":"2016-07-28 12:44:00"},{"temperature":"19.7","dtg":"2016-07-28 12:43:00"},{"temperature":"19.7","dtg":"2016-07-28 12:42:00"},{"temperature":"19.7","dtg":"2016-07-28 12:41:00"},{"temperature":"19.7","dtg":"2016-07-28 12:40:00"},{"temperature":"19.7","dtg":"2016-07-28 12:39:00"},{"temperature":"19.7","dtg":"2016-07-28 12:38:00"},{"temperature":"19.8","dtg":"2016-07-28 12:37:00"},{"temperature":"19.8","dtg":"2016-07-28 12:36:00"},{"temperature":"19.8","dtg":"2016-07-28 12:35:00"},{"temperature":"19.8","dtg":"2016-07-28 12:34:00"},{"temperature":"19.8","dtg":"2016-07-28 12:33:00"},{"temperature":"19.8","dtg":"2016-07-28 12:32:00"},{"temperature":"19.8","dtg":"2016-07-28 12:31:00"},{"temperature":"19.8","dtg":"2016-07-28 12:30:00"},{"temperature":"19.9","dtg":"2016-07-28 12:29:00"},{"temperature":"19.9","dtg":"2016-07-28 12:28:00"},{"temperature":"19.9","dtg":"2016-07-28 12:27:00"},{"temperature":"19.9","dtg":"2016-07-28 12:26:00"},{"temperature":"19.9","dtg":"2016-07-28 12:25:00"},{"temperature":"19.9","dtg":"2016-07-28 12:24:00"},{"temperature":"20.0","dtg":"2016-07-28 12:23:00"},{"temperature":"20.0","dtg":"2016-07-28 12:22:00"},{"temperature":"20.1","dtg":"2016-07-28 12:21:00"},{"temperature":"20.1","dtg":"2016-07-28 12:20:00"},{"temperature":"20.1","dtg":"2016-07-28 12:19:00"},{"temperature":"20.0","dtg":"2016-07-28 12:18:00"},{"temperature":"20.0","dtg":"2016-07-28 12:17:00"},{"temperature":"20.0","dtg":"2016-07-28 12:16:00"},{"temperature":"20.0","dtg":"2016-07-28 12:15:00"},{"temperature":"20.0","dtg":"2016-07-28 12:14:00"},{"temperature":"20.0","dtg":"2016-07-28 12:13:00"},{"temperature":"20.0","dtg":"2016-07-28 12:12:00"},{"temperature":"20.0","dtg":"2016-07-28 12:11:00"},{"temperature":"19.9","dtg":"2016-07-28 12:10:00"},{"temperature":"20.0","dtg":"2016-07-28 12:09:00"},{"temperature":"20.0","dtg":"2016-07-28 12:08:00"},{"temperature":"20.0","dtg":"2016-07-28 12:07:00"},{"temperature":"20.0","dtg":"2016-07-28 12:06:00"},{"temperature":"19.9","dtg":"2016-07-28 12:05:00"},{"temperature":"19.9","dtg":"2016-07-28 12:04:00"},{"temperature":"19.9","dtg":"2016-07-28 12:03:00"},{"temperature":"19.9","dtg":"2016-07-28 12:02:00"},{"temperature":"19.9","dtg":"2016-07-28 12:01:00"},{"temperature":"19.9","dtg":"2016-07-28 12:00:00"},{"temperature":"19.9","dtg":"2016-07-28 11:59:00"},{"temperature":"19.9","dtg":"2016-07-28 11:58:00"},{"temperature":"19.8","dtg":"2016-07-28 11:57:00"},{"temperature":"19.8","dtg":"2016-07-28 11:56:00"},{"temperature":"19.8","dtg":"2016-07-28 11:55:00"},{"temperature":"19.8","dtg":"2016-07-28 11:54:00"},{"temperature":"19.8","dtg":"2016-07-28 11:53:00"},{"temperature":"19.7","dtg":"2016-07-28 11:52:00"},{"temperature":"19.7","dtg":"2016-07-28 11:51:00"},{"temperature":"19.7","dtg":"2016-07-28 11:50:00"},{"temperature":"19.7","dtg":"2016-07-28 11:49:00"},{"temperature":"19.7","dtg":"2016-07-28 11:48:00"},{"temperature":"19.7","dtg":"2016-07-28 11:47:00"},{"temperature":"19.6","dtg":"2016-07-28 11:46:00"},{"temperature":"19.7","dtg":"2016-07-28 11:45:00"},{"temperature":"19.6","dtg":"2016-07-28 11:44:00"},{"temperature":"19.6","dtg":"2016-07-28 11:43:00"},{"temperature":"19.6","dtg":"2016-07-28 11:42:00"},{"temperature":"19.6","dtg":"2016-07-28 11:41:00"},{"temperature":"19.6","dtg":"2016-07-28 11:40:00"},{"temperature":"19.6","dtg":"2016-07-28 11:39:00"},{"temperature":"19.6","dtg":"2016-07-28 11:38:00"},{"temperature":"19.6","dtg":"2016-07-28 11:37:00"},{"temperature":"19.6","dtg":"2016-07-28 11:36:00"},{"temperature":"19.6","dtg":"2016-07-28 11:35:00"},{"temperature":"19.6","dtg":"2016-07-28 11:34:00"},{"temperature":"19.5","dtg":"2016-07-28 11:33:00"},{"temperature":"19.5","dtg":"2016-07-28 11:32:00"},{"temperature":"19.5","dtg":"2016-07-28 11:31:00"},{"temperature":"19.5","dtg":"2016-07-28 11:30:00"},{"temperature":"19.5","dtg":"2016-07-28 11:29:00"},{"temperature":"19.5","dtg":"2016-07-28 11:28:00"},{"temperature":"19.5","dtg":"2016-07-28 11:27:00"},{"temperature":"19.5","dtg":"2016-07-28 11:26:00"},{"temperature":"19.5","dtg":"2016-07-28 11:25:00"},{"temperature":"19.5","dtg":"2016-07-28 11:24:00"},{"temperature":"19.4","dtg":"2016-07-28 11:23:00"},{"temperature":"19.4","dtg":"2016-07-28 11:22:00"},{"temperature":"19.4","dtg":"2016-07-28 11:21:00"},{"temperature":"19.4","dtg":"2016-07-28 11:20:00"},{"temperature":"19.4","dtg":"2016-07-28 11:19:00"},{"temperature":"19.4","dtg":"2016-07-28 11:18:00"},{"temperature":"19.4","dtg":"2016-07-28 11:17:00"},{"temperature":"19.4","dtg":"2016-07-28 11:16:00"},{"temperature":"19.4","dtg":"2016-07-28 11:15:00"},{"temperature":"19.4","dtg":"2016-07-28 11:14:00"},{"temperature":"19.3","dtg":"2016-07-28 11:13:00"},{"temperature":"19.3","dtg":"2016-07-28 11:12:00"},{"temperature":"19.3","dtg":"2016-07-28 11:11:00"},{"temperature":"19.3","dtg":"2016-07-28 11:10:00"},{"temperature":"19.3","dtg":"2016-07-28 11:09:00"},{"temperature":"19.3","dtg":"2016-07-28 11:08:00"},{"temperature":"19.3","dtg":"2016-07-28 11:07:00"},{"temperature":"19.3","dtg":"2016-07-28 11:06:00"},{"temperature":"19.3","dtg":"2016-07-28 11:05:00"},{"temperature":"19.3","dtg":"2016-07-28 11:04:00"},{"temperature":"19.2","dtg":"2016-07-28 11:03:00"},{"temperature":"19.2","dtg":"2016-07-28 11:02:00"},{"temperature":"19.2","dtg":"2016-07-28 11:01:00"},{"temperature":"19.2","dtg":"2016-07-28 11:00:00"},{"temperature":"19.2","dtg":"2016-07-28 10:59:00"},{"temperature":"19.2","dtg":"2016-07-28 10:58:00"},{"temperature":"19.2","dtg":"2016-07-28 10:57:00"},{"temperature":"19.2","dtg":"2016-07-28 10:56:00"},{"temperature":"19.1","dtg":"2016-07-28 10:55:00"},{"temperature":"19.1","dtg":"2016-07-28 10:54:00"},{"temperature":"19.1","dtg":"2016-07-28 10:53:00"},{"temperature":"19.1","dtg":"2016-07-28 10:52:00"},{"temperature":"19.1","dtg":"2016-07-28 10:51:00"},{"temperature":"19.1","dtg":"2016-07-28 10:50:00"},{"temperature":"19.1","dtg":"2016-07-28 10:49:00"},{"temperature":"19.1","dtg":"2016-07-28 10:48:00"},{"temperature":"19.1","dtg":"2016-07-28 10:47:00"},{"temperature":"19.1","dtg":"2016-07-28 10:46:00"},{"temperature":"19.1","dtg":"2016-07-28 10:45:00"},{"temperature":"19.0","dtg":"2016-07-28 10:44:00"},{"temperature":"19.0","dtg":"2016-07-28 10:43:00"},{"temperature":"19.0","dtg":"2016-07-28 10:42:00"},{"temperature":"19.0","dtg":"2016-07-28 10:41:00"}];

//plot
$(document).ready(function () {
    $.plot($("#placeholder"),  dataset1  );
});

A flot graph grid is displayed in my placeholder within the php page but no data is displayed.

If I hard code the data into the variable dataset1 using the following formatting then a graph appears.

[[1, 300], [2, 600], [3, 550], [4, 400], [5, 300]];

I believe the problem could be due to my formatting of the json data within the php section and therefore need to format it for flot graph plotting.

My apologies as I am new to flot graphs and have attempted many of the similar solutions within stackoverflow before I posted this question (my first here) but without success. Any help would be greatly appreciated!

Answer 1

Flot expects your dataset to be in a different format than the one you're passing in. I got it working by looping over your current dataset (your last example) to put it in the right format.

var dataset1 = [{"temperature":"19.6","dtg":"2016-07-28 12:53:00"},{"temperature":"19.5","dtg":"2016-07-28 12:52:00"},{"temperature":"19.5","dtg":"2016-07-28 12:51:00"},{"temperature":"19.6","dtg":"2016-07-28 12:50:00"},{"temperature":"19.6","dtg":"2016-07-28 12:49:00"},{"temperature":"19.6","dtg":"2016-07-28 12:48:00"},{"temperature":"19.6","dtg":"2016-07-28 12:47:00"},{"temperature":"19.6","dtg":"2016-07-28 12:46:00"},{"temperature":"19.6","dtg":"2016-07-28 12:45:00"},{"temperature":"19.6","dtg":"2016-07-28 12:44:00"},{"temperature":"19.7","dtg":"2016-07-28 12:43:00"},{"temperature":"19.7","dtg":"2016-07-28 12:42:00"},{"temperature":"19.7","dtg":"2016-07-28 12:41:00"},{"temperature":"19.7","dtg":"2016-07-28 12:40:00"},{"temperature":"19.7","dtg":"2016-07-28 12:39:00"},{"temperature":"19.7","dtg":"2016-07-28 12:38:00"},{"temperature":"19.8","dtg":"2016-07-28 12:37:00"},{"temperature":"19.8","dtg":"2016-07-28 12:36:00"},{"temperature":"19.8","dtg":"2016-07-28 12:35:00"},{"temperature":"19.8","dtg":"2016-07-28 12:34:00"},{"temperature":"19.8","dtg":"2016-07-28 12:33:00"},{"temperature":"19.8","dtg":"2016-07-28 12:32:00"},{"temperature":"19.8","dtg":"2016-07-28 12:31:00"},{"temperature":"19.8","dtg":"2016-07-28 12:30:00"},{"temperature":"19.9","dtg":"2016-07-28 12:29:00"},{"temperature":"19.9","dtg":"2016-07-28 12:28:00"},{"temperature":"19.9","dtg":"2016-07-28 12:27:00"},{"temperature":"19.9","dtg":"2016-07-28 12:26:00"},{"temperature":"19.9","dtg":"2016-07-28 12:25:00"},{"temperature":"19.9","dtg":"2016-07-28 12:24:00"},{"temperature":"20.0","dtg":"2016-07-28 12:23:00"},{"temperature":"20.0","dtg":"2016-07-28 12:22:00"},{"temperature":"20.1","dtg":"2016-07-28 12:21:00"},{"temperature":"20.1","dtg":"2016-07-28 12:20:00"},{"temperature":"20.1","dtg":"2016-07-28 12:19:00"},{"temperature":"20.0","dtg":"2016-07-28 12:18:00"},{"temperature":"20.0","dtg":"2016-07-28 12:17:00"},{"temperature":"20.0","dtg":"2016-07-28 12:16:00"},{"temperature":"20.0","dtg":"2016-07-28 12:15:00"},{"temperature":"20.0","dtg":"2016-07-28 12:14:00"},{"temperature":"20.0","dtg":"2016-07-28 12:13:00"},{"temperature":"20.0","dtg":"2016-07-28 12:12:00"},{"temperature":"20.0","dtg":"2016-07-28 12:11:00"},{"temperature":"19.9","dtg":"2016-07-28 12:10:00"},{"temperature":"20.0","dtg":"2016-07-28 12:09:00"},{"temperature":"20.0","dtg":"2016-07-28 12:08:00"},{"temperature":"20.0","dtg":"2016-07-28 12:07:00"},{"temperature":"20.0","dtg":"2016-07-28 12:06:00"},{"temperature":"19.9","dtg":"2016-07-28 12:05:00"},{"temperature":"19.9","dtg":"2016-07-28 12:04:00"},{"temperature":"19.9","dtg":"2016-07-28 12:03:00"},{"temperature":"19.9","dtg":"2016-07-28 12:02:00"},{"temperature":"19.9","dtg":"2016-07-28 12:01:00"},{"temperature":"19.9","dtg":"2016-07-28 12:00:00"},{"temperature":"19.9","dtg":"2016-07-28 11:59:00"},{"temperature":"19.9","dtg":"2016-07-28 11:58:00"},{"temperature":"19.8","dtg":"2016-07-28 11:57:00"},{"temperature":"19.8","dtg":"2016-07-28 11:56:00"},{"temperature":"19.8","dtg":"2016-07-28 11:55:00"},{"temperature":"19.8","dtg":"2016-07-28 11:54:00"},{"temperature":"19.8","dtg":"2016-07-28 11:53:00"},{"temperature":"19.7","dtg":"2016-07-28 11:52:00"},{"temperature":"19.7","dtg":"2016-07-28 11:51:00"},{"temperature":"19.7","dtg":"2016-07-28 11:50:00"},{"temperature":"19.7","dtg":"2016-07-28 11:49:00"},{"temperature":"19.7","dtg":"2016-07-28 11:48:00"},{"temperature":"19.7","dtg":"2016-07-28 11:47:00"},{"temperature":"19.6","dtg":"2016-07-28 11:46:00"},{"temperature":"19.7","dtg":"2016-07-28 11:45:00"},{"temperature":"19.6","dtg":"2016-07-28 11:44:00"},{"temperature":"19.6","dtg":"2016-07-28 11:43:00"},{"temperature":"19.6","dtg":"2016-07-28 11:42:00"},{"temperature":"19.6","dtg":"2016-07-28 11:41:00"},{"temperature":"19.6","dtg":"2016-07-28 11:40:00"},{"temperature":"19.6","dtg":"2016-07-28 11:39:00"},{"temperature":"19.6","dtg":"2016-07-28 11:38:00"},{"temperature":"19.6","dtg":"2016-07-28 11:37:00"},{"temperature":"19.6","dtg":"2016-07-28 11:36:00"},{"temperature":"19.6","dtg":"2016-07-28 11:35:00"},{"temperature":"19.6","dtg":"2016-07-28 11:34:00"},{"temperature":"19.5","dtg":"2016-07-28 11:33:00"},{"temperature":"19.5","dtg":"2016-07-28 11:32:00"},{"temperature":"19.5","dtg":"2016-07-28 11:31:00"},{"temperature":"19.5","dtg":"2016-07-28 11:30:00"},{"temperature":"19.5","dtg":"2016-07-28 11:29:00"},{"temperature":"19.5","dtg":"2016-07-28 11:28:00"},{"temperature":"19.5","dtg":"2016-07-28 11:27:00"},{"temperature":"19.5","dtg":"2016-07-28 11:26:00"},{"temperature":"19.5","dtg":"2016-07-28 11:25:00"},{"temperature":"19.5","dtg":"2016-07-28 11:24:00"},{"temperature":"19.4","dtg":"2016-07-28 11:23:00"},{"temperature":"19.4","dtg":"2016-07-28 11:22:00"},{"temperature":"19.4","dtg":"2016-07-28 11:21:00"},{"temperature":"19.4","dtg":"2016-07-28 11:20:00"},{"temperature":"19.4","dtg":"2016-07-28 11:19:00"},{"temperature":"19.4","dtg":"2016-07-28 11:18:00"},{"temperature":"19.4","dtg":"2016-07-28 11:17:00"},{"temperature":"19.4","dtg":"2016-07-28 11:16:00"},{"temperature":"19.4","dtg":"2016-07-28 11:15:00"},{"temperature":"19.4","dtg":"2016-07-28 11:14:00"},{"temperature":"19.3","dtg":"2016-07-28 11:13:00"},{"temperature":"19.3","dtg":"2016-07-28 11:12:00"},{"temperature":"19.3","dtg":"2016-07-28 11:11:00"},{"temperature":"19.3","dtg":"2016-07-28 11:10:00"},{"temperature":"19.3","dtg":"2016-07-28 11:09:00"},{"temperature":"19.3","dtg":"2016-07-28 11:08:00"},{"temperature":"19.3","dtg":"2016-07-28 11:07:00"},{"temperature":"19.3","dtg":"2016-07-28 11:06:00"},{"temperature":"19.3","dtg":"2016-07-28 11:05:00"},{"temperature":"19.3","dtg":"2016-07-28 11:04:00"},{"temperature":"19.2","dtg":"2016-07-28 11:03:00"},{"temperature":"19.2","dtg":"2016-07-28 11:02:00"},{"temperature":"19.2","dtg":"2016-07-28 11:01:00"},{"temperature":"19.2","dtg":"2016-07-28 11:00:00"},{"temperature":"19.2","dtg":"2016-07-28 10:59:00"},{"temperature":"19.2","dtg":"2016-07-28 10:58:00"},{"temperature":"19.2","dtg":"2016-07-28 10:57:00"},{"temperature":"19.2","dtg":"2016-07-28 10:56:00"},{"temperature":"19.1","dtg":"2016-07-28 10:55:00"},{"temperature":"19.1","dtg":"2016-07-28 10:54:00"},{"temperature":"19.1","dtg":"2016-07-28 10:53:00"},{"temperature":"19.1","dtg":"2016-07-28 10:52:00"},{"temperature":"19.1","dtg":"2016-07-28 10:51:00"},{"temperature":"19.1","dtg":"2016-07-28 10:50:00"},{"temperature":"19.1","dtg":"2016-07-28 10:49:00"},{"temperature":"19.1","dtg":"2016-07-28 10:48:00"},{"temperature":"19.1","dtg":"2016-07-28 10:47:00"},{"temperature":"19.1","dtg":"2016-07-28 10:46:00"},{"temperature":"19.1","dtg":"2016-07-28 10:45:00"},{"temperature":"19.0","dtg":"2016-07-28 10:44:00"},{"temperature":"19.0","dtg":"2016-07-28 10:43:00"},{"temperature":"19.0","dtg":"2016-07-28 10:42:00"},{"temperature":"19.0","dtg":"2016-07-28 10:41:00"}];

var dataset2 = [];
for (var i = 0; i < dataset1.length; i++) {
    dataset2.push( [ Date.parse(dataset1[i].dtg), 
	             parseFloat(dataset1[i].temperature) ] );
}
			
//plot
$(document).ready(function () {
    $.plot( $("#placeholder"),
            [dataset2], // wrap data series in a container
            { xaxis: { mode: "time" } }
    );
});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="http://cdnjs.cloudflare.com/ajax/libs/flot/0.7/jquery.flot.min.js"></script>
<div id="placeholder" style="width:600px; height:400px; float: left;"></div>

After var dataset2 = [];, the loop unpacks each element in your dataset and creates a [date, temp] data point to add to the array. I had to parse both the date and the float, since Flot expects numeric data, not strings.

That creates one data series. You can plot multiple data series in Flot, so note that I had to wrap that series in another container in the call to plot. (In other words, that container could have had [dataset2, dataset3, ..., datasetX].)

The last step that was needed was to set { mode: "time" } on the x-axis.