I would like to use chart.js to graph data from an SQLite database queried with PHP. I've tried several online tutorials but my graph is never populated with those and most times just disappears. I have never used PHP before and am very confused with what I am doing. Most of the tutorials I'm trying to learn from are echoing the rows from the database but I need to pass them to JavaScript to be graphed. I don't know if it will be much help but here is my code:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/3.7.1/chart.min.js"></script>
<title>Graph</title>
</head>
<body>
<div>
<canvas id="myChart"></canvas>
</div>
<?php
$dir = 'sqlite:/home/pi/AQUASOLAR/SHT30/log_db/db.sht.6.db';
$dbh = new PDO($dir) or die("cannot open the database");
$query = "SELECT * FROM READING");
foreach ($dbh->query($query) as $row) {
//$r0 = $row[0];
$r0 = $row[0]
}
$dbh = null;
?>
<script>
//from db
var r0 = <?php echo $r0?>
var Temp = [1,2,3]; //y axis
var Humi = [4,5,6]; //y2 axis
var Itn = [r0]; //x axis
var graph = document.getElementById("myChart");
var line_graph = new Chart(graph, {
type: 'line',
data: {
labels: Itn,
datasets: [
{
data: Temp,
},
{
data: Humi,
}
],
},
options: {
scales: {
x: {
ticks: {
//display: false
},
},
},
},
});
</script>
</body>
</html>
If anyone can give me some pointers that would be great, I've been having a difficult time finding tutorials for this one. Thanks!
You can do something like this:
getData.php:
$dir = 'sqlite:/home/pi/AQUASOLAR/SHT30/log_db/db.sht.6.db';
$dbh = new PDO($dir) or die("cannot open the database");
$query = "SELECT * FROM READING");
$r0 = Array();
foreach ($dbh->query($query) as $row) {
$r0 = $row[0];
}
$dbh = null;
echo json_encode($r0);
index.html
<!DOCTYPE html>
<html lang="en">
..
<body>
<script>
// include jquery
$.ajax({
url: './getData.php',
type: 'GET',
success: (data) => {
const parsed = JSON.parse(data);
// Use parsed to create your chart
},
error: (err) => {
alert(err);
}
});
</script>
</body>
</html>
This is just an example of the logic that I use to resolve the problem. Maybe there are some sintax error