Dynamic line chart with chart.js and PHP

user_14861343 0
Viewed 2366

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
chart.js dynamic-programming mysql php javascript
3 Answers

In my opinion, multi-level arrays in javascript are more painful because you must do an array-object combo or have a fixed cube:

See https://www.quora.com/Why-cant-I-push-values-into-this-2D-array-in-JavaScript

Hence why PHP, or even in SQL, it would be easier to build your data set. However, I chose to endure and here you go:

 var jsondata = [
 {"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"},
 ];

// default obj for testing
var obj = [{
    id : 1, 
    label: 'MARKER01', 
    stamp: '',
    data: [],
}];

for (var i = 0; i < jsondata.length; i++) 
{
    var row = Object.values(jsondata[i]);
    var marker = row[0];  
    var index = add(obj, marker);
    obj[index].stamp = new Date( row[1] *1000);
    for (var j = 2; j < row.length; j++) {
       obj[index].data.push(row[j]);
    }
   
}

console.log(obj);

/**
 * add function thanks to: https://stackoverflow.com/questions/15997879/get-the-index-of-the-object-inside-an-array-matching-a-condition
 */
function add(arr, name) {
  var { length } = arr;
  var id = length + 1;
  var index = arr.findIndex(x => x.label === name);
  if (index === -1) {
    arr.push({ id, label: name, stamp: '', data: []})
    index = arr.findIndex(x => x.label === name);
  } 
  console.log(index);
  return index;
}

// You can test with console.log(add(obj, 'test'));

Ouput

 Object { id: 1, label: "MARKER01", stamp: Sun Dec 13 2020 08:00:00 GMT-0500 (Eastern Standard Time), data: Array ["3.02", "3.03", "3.16", "3.72", "2.93", "3.00", "3.10", "3.06", "2.98", "3.10", "3.06", "2.98"] },
 Object { id: 2, label: "MARKER03", stamp: Sun Dec 13 2020 08:00:00 GMT-0500 (Eastern Standard Time), data: Array ["2.30", "2.41", "2.74", "2.30", "2.41", "2.74", "2.29", "2.37", "2.74", "2.29", "2.37", "2.74"] },
 Object { id: 3, label: "MARKER07", stamp: Sun Dec 13 2020 13:33:20 GMT-0500 (Eastern Standard Time), data: Array ["2.74", "4.26", "4.26", "2.75", "4.26", "4.26"] },
 Object { id: 4, label: "MARKER00", stamp: Sun Dec 13 2020 13:33:20 GMT-0500 (Eastern Standard Time), data: Array ["5.64", "2.09", "1.30", "5.65", "2.09", "1.30"] }
]```
user_60255500

Using your code:

Try parseInt when adding the values to your array:

for (var i = 0; i < data.length; i++) {
    var date = new Date( data[i].timestamp *1000);
    time.push(date.toLocaleString());
    value1.push(parseInt(data[i].a));
    value2.push(parseInt(data[i].b));
    console.log(parseInt(data[i].b));
    
}

Debugging

On the php side, make sure you convert your numbers from string to inter, on the js side you can also do:

var jsonString = '[{"x":1.5}, {"x":2.7}]';

var obj  = JSON.parse(jsonString);
console.log('obj:'); console.log(obj);
// outputs obj array: [{x: 1.5} {x: 2.7}]

var dataset1 = $.map(obj, function(val, i){
    console.log('x: ' + val.x);
    return val.x;
});
console.log('dataset1 :'); console.log(dataset1);
//ouputs new array with just values: (2) [1.5, 2.7]

With Your Full Code

chartjs

var data = [
{"timestamp":"1607943100","a":"1.5", "b":"2.81", "c":"7.81"},
{"timestamp":"1607943200","a":"2.7", "b":"8.10", "c":"3.10"},
{"timestamp":"1607943300","a":"1.3", "b":"1.77", "c":"4.77"},
{"timestamp":"1607943400","a":"5.2", "b":"4.71", "c":"2.71"},
{"timestamp":"1607943500","a":"9.4", "b":"4.43", "c":"7.43"},
{"timestamp":"1607943600","a":"6.5", "b":"3.26", "c":"9.26"},
];

//get the line chart canvas
var ctx = $("#myChart");

//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(parseInt(data[i].a));
    value2.push(parseInt(data[i].b));
    value3.push(parseInt(data[i].c));
    
    // some debugging
    console.log(parseInt(data[i].b));
}

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: 'Value3',
            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
});
user_60255500

For formatting date and time:

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.20.1/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.1/Chart.min.js"></script>
<canvas id="myChart"></canvas>

<script>
var s1 = {
  label: 's1',
  borderColor: 'blue',
  data: [
    { x: 1606943100000, y: 1.5 },
    { x: 1607943200000, y: 2.7 },
    { x: 1608943300000, y: 1.3 },
    { x: 1609943400000, y: 5.2 },
    { x: 1610943500000, y: 9.4 },
    { x: 1611943600000, y: 6.5 },
  ]
};

var s2 = {
  label: 's2',
  borderColor: 'red',
  data: [
    { x: 1604953100000, y: 4.5 },
    { x: 1607963200000, y: 2.7 },
    { x: 1608973300000, y: 3.3 },
    { x: 1613983400000, y: 4.2 },
    { x: 1620993500000, y: 7.4 },
    { x: 1632043600000, y: 6.5 },
  ]
};

var ctx = document.getElementById('myChart').getContext('2d');
var chart = new Chart(ctx, {
  type: 'line',
  data: { datasets: [s1, s2] },
  options: {
    scales: {
      xAxes: [{
        type: 'time',
        time: {
          unitStepSize: 500,
          unit: 'hour', // check displayFormat to match
          displayFormats: {
            hour: 'hA', 
            day: 'YYYY-MM-DD',
            month: 'YYYY-MM'
          }
        }
      }]
    }
  }
});
</script>
user_60255500
Related