Chartjs-Use data from SQL database in .js file with PHP

by

3👍

✅

Of course you can achieve this with PHP.

There are two options how to do this:

  1. Add PHP code inline to your JavaScript data within your webpage.
  2. Create an extra PHP script that fetches the data from your database and outputs it as JSON. The JavaScript then can fetch the JSON from your extra PHP script.

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>

Leave a comment