PHP Chart - how to call the BD and adapt the variables to the chart?

Question

Good, I’m here with a problem in adapting the variables I call from the database to the graph .

<?php
$labelsJS = array();
$valsJS = array();

$graph=$conn->query("SELECT * FROM lucro")or die("error");

while($row=$graph->fetch_assoc() and $i<=$graph->num_rows){
  $data=date('M',strtotime($row['data']));

    $labelsJS[] = $data;
    $valsJS[] = $row['lucro'];

   $i++;
}

$labelsJS = json_encode($labelsJS);
$valsJS = json_encode($valsJS);
    // This is a simple example on how to draw a chart using FusionCharts and PHP.
    // We have included includes/fusioncharts.php, which contains functions
    // to help us easily embed the charts.
    require("includes/fusioncharts.php");
    // Create the chart - Column 2D Chart with data given in constructor parameter 
    // Syntax for the constructor - new FusionCharts("type of chart", "unique chart id", "width of chart", "height of chart", "div id to render the chart", "type of data", "actual data")
    $columnChart = new FusionCharts("column2d", "ex1", "100%", 400, "chart-1", "json", '{  
                    "chart":{  
                      "caption":"Harry\'s SuperMart",
                      "subCaption":"Top 5 stores in last month by revenue",
                      "numberPrefix":"$",
                      "theme":"ocean"
                    },
                    "data":[  
                      { 
                         "label":"Bakersfield Central",
                         "value":"880000"
                      },
                      {  
                         "label":"Garden Groove harbour",
                         "value":"730000"
                      },
                      {  
                         "label":"Los Angeles Topanga",
                         "value":"590000"
                      },
                      {  
                         "label":"Compton-Rancho Dom",
                         "value":"520000"
                      },
                      {  
                         "label":"Daly City Serramonte",
                         "value":"330000"
                      }
                    ]
                }');
    // Render the chart
    $columnChart->render();
    ?>

Only this later stays inside a script. So maybe the best one was by the variable labelsJS in a variable of JS.

ATTENTION: have to be in such a way that the process is automatic whenever I manipulate the database.

Answer 1

Try changing your code to the following structure:

while($row=$graph->fetch_assoc() and $i<=$graph->num_rows){
  $data=date('M',strtotime($row['data']));

    $data[$i]['label'] = $data;
    $data[$i]['value'] = $row['lucro'];

   $i++;
}

With only one variable containing the values, convert it to a JSON:

$data = json_encode($data);

In Chart, add the variable data:

$columnChart = new FusionCharts("column2d", "ex1", "100%", 400, "chart-1", "json", '{  
                    "chart":{  
                      "caption":"Harry\'s SuperMart",
                      "subCaption":"Top 5 stores in last month by revenue",
                      "numberPrefix":"$",
                      "theme":"ocean"
                    },
                    "data": $data
                }');
    // Render the chart
    $columnChart->render();