如何按日期为Google图表迭代数据库值

Question

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 1

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>