I can't dislay a Column Chart (Google Chart - Server Side)

Question

I try to use Google Charts in one of my projects. My SQL is working good, but I can't get the chart, maybe i need to mention that my data is a number (0.00). I am using my POSTGRESQL DB (server side)

Here's my code:

  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript">
  google.charts.load('current', {'packages':['corechart','bar']});
  google.charts.setOnLoadCallback(drawChart);

  function drawChart() {
    var data = google.visualization.arrayToDataTable([
      ['niveau','me1','me2','me3','me4','me5','me6'],

    <?php
        $result = $pdo->query ("SELECT  
                niveau.nom_niveau AS niveau,
                ROUND (( SUM(CASE WHEN mesure.e1 IS NOT NULL THEN mesure.pp*mesure.e1 ELSE null END) / SUM(CASE WHEN mesure.e1 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me1,
                ROUND (( SUM(CASE WHEN mesure.e2 IS NOT NULL THEN mesure.pp*mesure.e2 ELSE null END) / SUM(CASE WHEN mesure.e2 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me2,
                ROUND (( SUM(CASE WHEN mesure.e3 IS NOT NULL THEN mesure.pp*mesure.e3 ELSE null END) / SUM(CASE WHEN mesure.e3 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me3,
                ROUND (( SUM(CASE WHEN mesure.e4 IS NOT NULL THEN mesure.pp*mesure.e4 ELSE null END) / SUM(CASE WHEN mesure.e4 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me4,
                ROUND (( SUM(CASE WHEN mesure.e5 IS NOT NULL THEN mesure.pp*mesure.e5 ELSE null END) / SUM(CASE WHEN mesure.e5 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me5,
                ROUND (( SUM(CASE WHEN mesure.e6 IS NOT NULL THEN mesure.pp*mesure.e6 ELSE null END) / SUM(CASE WHEN mesure.e6 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me6
        FROM mesure , puits, niveau , gisement
        where mesure.cm = puits.cm
        and mesure.num_niveau = niveau.id_niveau
        and gisement.id_gisement = puits.num_gisement
        group by niveau.nom_niveau");

        foreach($result AS $row) {
            echo "['".$row['niveau']."',".$row['me1'].",".$row['me2'].",".$row['me3'].",".$row['me4'].",".$row['me5'].",".$row['me6']."], "; 
        }
    ?>
    ]);

    var options = {
        chart: {
        title: '',
        subtitle: 'Moyenne Pondérée des éléments chimiques en chaque niveau phoshaté' }
    };

    var chart = new google.charts.Bar(document.getElementById('columnchart_material'));
    chart.draw(data, google.charts.Bar.convertOptions(options));       
  }
  </script>

Here's my data table:

I hope someone can help me out on this.

are there any errors in the browser's console? (press F12 on most) — WhiteHat, Jun 15 '20 at 15:04

score 1 · Accepted Answer · answered Jun 15 '20 at 15:53

here, you are using a single quote / apostrophe to indicate the first column value is a string...

echo "['".$row['niveau']."'," ...

however, if you look at row 3 in the data table, the value ends with a apostrophe...

Couche 0'

this is causing the error.

to resolve, you will either need to remove the apostrophe from the data table value...

Couche 0

or reverse the use of single and double quotes in the echo statement.

foreach($result AS $row) {
    echo '["'.$row["niveau"].'",'.$row["me1"].','.$row["me2"].','.$row["me3"].','.$row["me4"].','.$row["me5"].','.$row["me6"].'], '; 
}

assuming there are no data table values that include a double quotes.

this is why it is not recommended to build the json string manually.
instead, build the data as an array, then encode it on the html page.

function drawChart() {
  var data = google.visualization.arrayToDataTable(
    <?php

      $result = $pdo->query ("SELECT
        niveau.nom_niveau AS niveau,
        ROUND (( SUM(CASE WHEN mesure.e1 IS NOT NULL THEN mesure.pp*mesure.e1 ELSE null END) / SUM(CASE WHEN mesure.e1 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me1,
        ROUND (( SUM(CASE WHEN mesure.e2 IS NOT NULL THEN mesure.pp*mesure.e2 ELSE null END) / SUM(CASE WHEN mesure.e2 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me2,
        ROUND (( SUM(CASE WHEN mesure.e3 IS NOT NULL THEN mesure.pp*mesure.e3 ELSE null END) / SUM(CASE WHEN mesure.e3 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me3,
        ROUND (( SUM(CASE WHEN mesure.e4 IS NOT NULL THEN mesure.pp*mesure.e4 ELSE null END) / SUM(CASE WHEN mesure.e4 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me4,
        ROUND (( SUM(CASE WHEN mesure.e5 IS NOT NULL THEN mesure.pp*mesure.e5 ELSE null END) / SUM(CASE WHEN mesure.e5 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me5,
        ROUND (( SUM(CASE WHEN mesure.e6 IS NOT NULL THEN mesure.pp*mesure.e6 ELSE null END) / SUM(CASE WHEN mesure.e6 IS NOT NULL THEN mesure.pp ELSE null END)) ,2) AS me6
      FROM mesure , puits, niveau , gisement
      where mesure.cm = puits.cm
      and mesure.num_niveau = niveau.id_niveau
      and gisement.id_gisement = puits.num_gisement
      group by niveau.nom_niveau");

      // create data array
      $data = [];
      $data[] = ['niveau','me1','me2','me3','me4','me5','me6'];
      foreach($result AS $row) {
        $data[] = [$row['niveau'], $row['me1'], $row['me2'], $row['me3'], $row['me4'], $row['me5'], $row['me6']];
      }

      // write data array to page
      echo json_encode($data);
    ?>
  );

  var options = {
      chart: {
      title: '',
      subtitle: 'Moyenne Pondérée des éléments chimiques en chaque niveau phoshaté' }
  };

  var chart = new google.charts.Bar(document.getElementById('columnchart_material'));
  chart.draw(data, google.charts.Bar.convertOptions(options));
}

furthermore, I would recommend not combining php with html / javascript.
separate the php and html into separate files. then use ajax to get the data from php.
see this answer for an example...

How to automatically update Google chart

Yes. Thank you very much for the help and the advice to. – Sefiane Jun 15 '20 at 15:58 — Sefiane, Jun 15 '20 at 15:58

I can't dislay a Column Chart (Google Chart - Server Side)

1 Answers1