I use chart.js on a page and I want to use data stored in a SQL database as data for chart.js
Here's what the chart.js snippet looks like:
// Chart Data
var chartData = {
labels: ["Jänner", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"],
datasets: [{
label: "Wert Verkaufter Waren 2022 - Standorte kumuliert",
//Data for Graph
data: [65, 59, 80, 81, 56, 55, 40],
fill: false,
borderDash: [5, 5],
borderColor: "#346200",
pointBorderColor: "#346200",
pointBackgroundColor: "#FFF",
pointBorderWidth: 2,
pointHoverBorderWidth: 2,
pointRadius: 4,
}]
};
The data values [65, 59, 80, 81, 56, 55, 40] is what i want to replace with data from my database.
Can I archive this with the use of php?
Of course you can achieve this with PHP.
There are two options how to do this:
The first method would be something like this. Please remember that this is a quick sketch that does not include error handling, security best practises, etc.
<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
$result = $mysqli->query("SELECT wert FROM verkaeufe_monatlich;");
$data = $result->fetch_all();
$mysqli->close();
?>
<html>
<!-- Your normal webpage html content -->
<script>
var chartData = {
labels: ["Jänner", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"],
datasets: [{
label: "Wert Verkaufter Waren 2022 - Standorte kumuliert",
//Data for Graph
data: <?php echo json_encode($data); ?>,
fill: false,
borderDash: [5, 5],
borderColor: "#346200",
pointBorderColor: "#346200",
pointBackgroundColor: "#FFF",
pointBorderWidth: 2,
pointHoverBorderWidth: 2,
pointRadius: 4,
}]
};
</script>
</html>
For the second method, create a separate PHP script that roughly contains the database logic and JSON encoding as above and call it from your script like this:
<script>
fetch('/database-query.php')
.then(response => response.json())
.then(jsonData => {
const chartData = {
labels: ["Jänner", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"],
datasets: [{
label: "Wert Verkaufter Waren 2022 - Standorte kumuliert",
data: jsonData,
fill: false,
borderDash: [5, 5],
borderColor: "#346200",
pointBorderColor: "#346200",
pointBackgroundColor: "#FFF",
pointBorderWidth: 2,
pointHoverBorderWidth: 2,
pointRadius: 4,
}]
};
// do stuff with chartData
})
.catch(error => {
console.error(error);
});
</script>