Ryan D Ryan D - 4 months ago 33
MySQL Question

How to iterate database values by date for google charts

I have seen similar questions but still do not understand how it woks.. I have this while loop looping my DB for dates, leads and sold. For each date in the DB I would like to show the daily leads and sold for each date in the DB in a line chart.

$sql = "SELECT * FROM customers WHERE source = 'website' ORDER BY date ASC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$sold = $row['sold'];
$visit= $row['visit'];
$date= $row['date'];
}else{

}
}


Here is the chart script -

<script type="text/javascript">

google.charts.setOnLoadCallback(drawChartl);

function drawChartl() {
var data = google.visualization.arrayToDataTable([
['Date', 'Leads', 'Sold'],
['1st', 6, 2],
['2nd', 3, 1],
['3rd', 2, 3],
]);

var options = {
title: 'Internet Performance',
curveType: 'function',
legend: { position: 'top' }
};

var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));

chart.draw(data, options);
}
</script>


<div id="curve_chart" style="width: 1900px; height: 500px"></div>

Answer

see following snippet...

need an array that holds all the data --> $json

then add each row to $json --> $dataRow

then write the result to the javascript --> data.addRows(<?= $jsonstring ?>);

try something like this, probably need to format the date too...

<?php
  $json = array();
  $sql = "SELECT * FROM customers WHERE source = 'website' ORDER BY date ASC";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {
          $dataRow = array(
              $row['date'],
              $row['visit'],
              $row['sold']
          );
          array_push($json, $dataRow);
      }
  }

  $jsonstring = json_encode($json);
?>

<script src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
  google.charts.load('current', {
    'callback': function () {
      var data = google.visualization.arrayToDataTable([
        [{type: 'string', label: 'Date'}, {type: 'number', label: 'Leads'}, {type: 'number', label: 'Sold'}]
      ]);

      data.addRows(<?= $jsonstring ?>);

      var options = {
        title: 'Internet Performance',
        curveType: 'function',
        legend: { position: 'top' }
      };

      var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
      chart.draw(data, options);
    },
    'packages': ['corechart']
  });
</script>

<div id="curve_chart" style="width: 1900px; height: 500px"></div>