I´m trying to display my data from a MySQL-DB with a chart.js line chart. I´m accessing the data from the DB via PHP and receive 1 array with hundreds of strings like this.
The dataset I receive is in the following format:
[
{"0":"MARKER01","timestamp":"1607943600","VALUE1":"3.02","VALUE2":"3.03","VALUE3":"3.16"},
{"0":"MARKER01","timestamp":"1607932800","VALUE1":"3.72","VALUE2":"2.93","VALUE3":"3.00"},
{"0":"MARKER01","timestamp":"1607882400","VALUE1":"3.10","VALUE2":"3.06","VALUE3":"2.98"},
{"0":"MARKER01","timestamp":"1607864400","VALUE1":"3.10","VALUE2":"3.06","VALUE3":"2.98"},
{"0":"MARKER03","timestamp":"1607943600","VALUE1":"2.30","VALUE2":"2.41","VALUE3":"2.74"},
{"0":"MARKER03","timestamp":"1607864400","VALUE1":"2.30","VALUE2":"2.41","VALUE3":"2.74"},
{"0":"MARKER03","timestamp":"1607943600","VALUE1":"2.29","VALUE2":"2.37","VALUE3":"2.74"},
{"0":"MARKER03","timestamp":"1607864400","VALUE1":"2.29","VALUE2":"2.37","VALUE3":"2.74"},
{"0":"MARKER07","timestamp":"1607882400","VALUE1":"2.74","VALUE2":"4.26","VALUE3":"4.26"},
{"0":"MARKER07","timestamp":"1607884400","VALUE1":"2.75","VALUE2":"4.26","VALUE3":"4.26"},
{"0":"MARKER00","timestamp":"1607882400","VALUE1":"5.64","VALUE2":"2.09","VALUE3":"1.30"},
{"0":"MARKER00","timestamp":"1607884400","VALUE1":"5.65","VALUE2":"2.09","VALUE3":"1.30"}
]
Using javascript, I need to put into the following format so that I can chart each marker-value combination:
{
data: {
m1v1: {
[timestamp: 1607932800, value: 1.17],
...
},
m1v2: {
[timestamp: 1607932800, value: 3.43],
...
},
m1v2: {
[timestamp: 1607932800, value: 2.72],
...
},
m2v1...and so on
}
My current code of course only displays one marker because there is nothing dynamic so far. Here is the code I managed so far:
$.getJSON("http://localhost/chart.php",function(data){
//get the line chart canvas
var ctx = $("#Chart");
//line chart data
var time = [];
var value1 = [];
var value2 = [];
var value3 = [];
for (var i = 0; i < data.length; i++) {
var date = new Date( data[i].timestamp *1000);
time.push(date.toLocaleString());
value1.push(data[i].VALUE1);
value2.push(data[i].VALUE2);
value3.push(data[i].VALUE3);
}
var chartData = {
labels: time,
datasets: [
{
label: 'Value1',
backgroundColor: 'blue',
borderColor: 'blue',
hoverBackgroundColor: '#CCCCCC',
hoverBorderColor: '#666666',
fill: false,
lineTension: 0,
radius: 0,
data: value1
},
{
label: 'Value2',
backgroundColor: 'green',
borderColor: 'green',
hoverBackgroundColor: '#CCCCCC',
hoverBorderColor: '#666666',
fill: false,
lineTension: 0,
radius: 0,
data: value2
},
{
label: 'Value2',
backgroundColor: 'red',
borderColor: 'red',
hoverBackgroundColor: '#CCCCCC',
hoverBorderColor: '#666666',
fill: false,
lineTension: 0,
radius: 0,
data: value3
},
]
};
//options
var options = {
responsive: true,
title: {
display: true,
position: "top",
text: "Chart",
fontSize: 18,
fontColor: "#111"
},
legend: {
display: true,
position: "top",
labels: {
fontColor: "#333",
fontSize: 16
}
},
scales: {
yAxes: [{
scaleLabel: {
display: true,
labelString: 'Value [ ]'
},
ticks: {
beginAtZero: true
}
}]
}
};
//create Chart class object
var chart = new Chart(ctx, {
type: "line",
data: chartData,
options: options
});
});
My desired outcome should be something like this: [![enter image description here][1]][1]
Example target format to plot data
{
"data": {
"m1": {
"v1": [3.72, 4.03, 5.42],
"v2": [1.22, 2.31, 3.27],
"v3": [2.51, 4.04, 1.49],
},
"m2": {
"v1": [1.74, 2.66, 7.56],
"v2": [3.23, 1.31, 4.45],
"v3": [1.52, 2.14, 2.32],
}
}
My SQL database structure: My "general" table:
CREATE TABLE `data` (
`id` int(11) NOT NULL,
`timestamp` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`markerId` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`lon` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`lat` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `data` (`id`, `timestamp`, `sensorID`, `lon`, `lat`) VALUES
(1, '1605087974', 'Marker01', '14.1428918430989', '56.09037310387064'),
(2, '1605087974', 'Marker03', '14.134828135508569', '56.0793705737164'),
(3, '1605087974', 'Marker07', '14.107481648897865', '56.08615752583438'),
(4, '1605087974', 'Marker04', '14.104676881040351', '56.09458825362679'),
(5, '1605087974', 'Marker00', '14.093808405592508', '56.08862523324586');
My "value" table:
CREATE TABLE `marker01_values` (
`id` int(11) NOT NULL,
`timestamp` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`VALUE1` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`VALUE2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`VALUE3` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `marker01_values` (`id`, `timestamp`, `VALUE1`, `VALUE2`, `VALUE3`) VALUES
(1, '1602838778', '2.18', '2.23', '2.17'),
(2, '1602838800', '2.18', '2.23', '2.17'),
(3, '1602842400', '2.18', '2.24', '2.17'),
(4, '1602846000', '2.18', '2.24', '2.17'),
(5, '1602849600', '2.18', '2.24', '2.17');
My PHP to fetch the data:
<?php
$resultArray = array();
// 1. Search in general table for query
$info="WITH ranked_data AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY markerId ORDER BY id DESC) AS rn FROM data AS m ) SELECT id, markerId, lon, lat FROM ranked_data WHERE rn = 1;";
$sql=($info);
// Check if there are results
if ($result = mysqli_query($con, $sql))
{
// Loop through rows in the result set
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result))
{
// Add active row into our temp array
$tempArray = $row;
$markerId = array_values($tempArray); //saves the value of the table fields
$sid=$markerId[1]; //saves sensor id value
$tableName=$sid.'_values'; //varaible table name messdata for every iteration
// 2. Next search in value table for query
$info2="SELECT timestamp, VALUE1, VALUE2, VALUE3 FROM $tableName order by id desc limit 400";
$sql2=($info2);
// Check if there are results
if ($result2 = mysqli_query($con, $sql2))
{
// Loop through rows in the result set
while($row2 = $result2->fetch_object())
{
// Add active row into our temp array
$tempArray2 = $row2;
// Add all temp arrays to result table
$midArray = (object) array_merge((array)$sid, (array) $tempArray2);
array_push($resultArray, $midArray);
}
}
}
}
}
// Echo out final result array
echo json_encode($resultArray);
The structure of the tables should remain the same, because I am not in charge of them...
Edit III 2020/12/29:
I adjusted my code with the comments posted on 2020/12/29:
I am not quite sure why but I had to set j = 6. Otherwise there were duplicates in the output...
var obj = [];
for (var i = 0; i < jsondata.length; i++)
{
var row = Object.values(jsondata[i]);
var marker = row[0];
var index = add(obj, marker);
for (var j = 6; j < row.length; j++) {
const dateObject = new Date(row[1]*1000);
var date = dateObject.toLocaleString();
obj[index].data.push({time: date, value1: row[2], value2: row[3], value3: row[4]});
}
}
console.log(obj);
[![Excel example for chart][3]][3]
I believe, it makes more sense to only display 1 marker with its values at once to keep it clearer. And I hope it is also less complicated code.
The markers (or datasets) that were made with the [answer on 2020/12/29][4] should be able to be chosen with the help of something like a dropdown. But it still needs to be implemented into the chart.js code by a dynamic solution because there will be markers added in the future.
So basically (in my undertanding) there has to be something like a loop to split the JSON data fetch with PHP into the markers (done with answer mentioned above) and something like another loop to add all the data of the markers (split from the PHP) to the chart.js datasets for displaying them as lines. Is this correct? Or is there a more comfortable way to do it?
Regarding the data, there are 3 values (value 1, value 2, value 3) for every timestamp and the next timestamp (approx. every hour) has the next 3 values and so on... The values should be displayed as the come from the database (no average or other calculation in this place).
For this use is it better to split the `PHP` into 3 "datasets" with 1 timestamp each (like: `timestamp1, value1 | timestamp1, value2 | timestamp1, value3`) or just display all values with 1 timestamp (like I did before: `timestamp1, value1, value2, value3`)? Or does something completely else suit better?
[1]: https://i.stack.imgur.com/kVZkS.png
[2]: https://www.chartjs.org/samples/latest/
[3]: https://i.stack.imgur.com/1YYCX.png
[4]: https://stackoverflow.com/a/65486900/14861343
