Display line chart with data retrieved from database using morris.js, PHP & MySQL

Question

I am working with a Bootstrap 4 template that uses morris.js for charts. I have a database that I am trying to retrieve data from and the issue is that it not being displayed dynamically on the chart.

I already worked on the query and tried to echo it in the file morris.js. I have tried solutions found on stack overflow, but I couldn't see where the issue is.

This is the index.php:

<div class="card">
  <div class="header">
     <h2>line Chart</h2>
 </div>
   <div class="body">
     <div id="m_area_chart"></div>
 </div>
</div>

This is the MySQL query:

Sample data in table tbl_water_level

ID	date	dev_eui	water_level	battery
1	2023-10-18 08:06:44	1	10	0
2	2023-10-18 09:06:44	2	12	3

$sql3 = "SELECT DATE_FORMAT(`date`, '%Y-%m-%d %H:%i') as `period`, water_level as water_level, battery as battery FROM `tbl_water_level` WHERE `date` > SUBDATE( NOW(), INTERVAL 24 HOUR)
order by date";
$result3 = mysqli_query($conn, $sql3);
$chart_data = '';
while($row3 = mysqli_fetch_array($result3))
{
 $chart_data .= "{ period:'".$row3["period"]."', water_level:".$row3["water_level"].", battery:".$row3["battery"]." }, ";
}
$chart_data = substr($chart_data, 0, -2);

The SELECT query is ok. But, there could be an issue with the while statement. Also, it is not throwing any error so far. I run the query and this is what it returned: [{"time":"13:45","water_level":"0.25","battery":"0"}]

My question is, how do I get the X-axis to display the time and the two graph lines to show water_level and battery.

This is the file morris.js: Please note that period on the x-axis is years(2000,2001,2002,etc.) initially displayed. I think it is already calculated in another JS file. I kept xkey as period, since in the MySQL query I put in the array.

<script>
$(document).ready(function () {
new Morris.Area({
 element : 'm_area_chart',
 data:[<?php echo $chart_data; ?>],
 xkey:'period',
 ykeys:['water_level', 'battery'],
 labels:['water_level', 'battery'],
 hideHover:'auto',
  pointSize: 2,
 fillOpacity: 0,
 pointStrokeColors: ['#2cbfb7', '#5dcef6', '#c0d0d6'],
 behaveLikeLine: true,
 gridLineColor: '#eeeeee',
 lineWidth: 1,
 lineColors: ['#2cbfb7', '#5dcef6', '#c0d0d6'],
 resize: true
 

});
});
</script>

This is the graph I am expecting to be displayed(I tweaked the X-axis to put time so that we could see what I want):

(https://i.stack.imgur.com/MttyG.jpg)

Update

I was able to get to it to work. The solution was to add the script into the file index.php. I also changed the date format to '%Y-%m-%d %H:%i' in the SELECT query. This was the solution given by user1191247. I have updated the query and script as required in my question.

Answer 1

The documentation lists the following in the description for the xkey option:

A string containing the name of the attribute that contains date (X) values.

Timestamps are accepted in the form of millisecond timestamps (as returned by Date.getTime() or as strings in the following formats:

• 2012
• 2012 Q1
• 2012 W1
• 2012-02
• 2012-02-24
• 2012-02-24 15:00
• 2012-02-24 15:00:00
• 2012-02-24 15:00:00.000

If you select your data from the db in the format you intend to use (column aliased as period instead of time), then you can pass the full result set direct to json_encode:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$db = new mysqli('127.0.0.1', 'user', 'pass', 'test', 3306);
$db->set_charset('utf8mb4');
$db->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

$sql = <<<'SQL'
    SELECT DATE_FORMAT(`date`, '%Y-%m-%d %H:%i') AS period, water_level, battery
    FROM tbl_water_level
    WHERE `date` > NOW() - INTERVAL 24 HOUR
    ORDER BY `date` ASC;
    SQL;

$result = $db->query($sql);
$chart_data = json_encode($result->fetch_all(MYSQLI_ASSOC));

Your question suggests that your js file is being parsed by PHP, so you can populate the data option. This is a really bad idea and partially defeats the point of having js in separate files. You can assign the JSON to a js global variable or a data attribute of the chart div, then your js file can be treated as the static asset it should be.

You replied in the comments that your "graph is not linear". Time is linear and graphing time series data with a non-linear scale does not make sense, in this case at least. If you really want your graph to be non-linear you can add parseTime: false to your options:

Set to false to skip time/date parsing for X values, instead treating them as an equally-spaced series.