Multi line Google Line Chart, using data from mySQ

2019-05-10 01:53发布

站内问答 / PHP
766 1 4

I'm going through a bit of a learning process, in order to create a small database backed reporting system for my company.

The intent is to draw a multi line chart using Google Charts, based on a mysql database.

I've managed to get the data to echo from the mysql database, but it's not generating the chart. All I get is the echo, and a blank space where the chart should be. The echo is shown for debugging purposes.

Here is the code :

<?php include 'confile.php';

$qry = "SELECT time,p1,p2,p3,p4 from $db WHERE date = '2016-03-02' ORDER BY time ASC";
$result = $conn->query($qry);

if($result === FALSE) {
    echo mysql_errno($result) .": ". mysql_error($result) ."/n";
    die(mysql_error());
}

$rows = array();
$table = array();
$table['cols'] = array(
                        array('label' => 'Time', 'type' => 'datetime'),
                        array('label' => 'Probe 1', 'type' => 'number'),
                        array('label' => 'Probe 2', 'type' => 'number'),
                        array('label' => 'Probe 3', 'type' => 'number'),
                        array('label' => 'Probe 4', 'type' => 'number')
                        );

while($r = mysqli_fetch_assoc($result)) {

    $temp = array();
    $temp[] = array($r['time']);

    $temp[] = array($r['p1']);
    $temp[] = array($r['p2']);              
    $temp[] = array($r['p3']);
    $temp[] = array($r['p4']);

    $rows[] = array('c' => $temp);  
}

$table['rows'] = $rows;

$jsonTable = json_encode($table);
echo $jsonTable;
?>
<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {

        var data = new google.visualization.DataTable(<?$jsonTable?>);
        var options = {
            title: 'Recorded Temperatures',
            legend: {position: 'bottom' },
            width: 800,
            height: 600
        }; 

        var chart = new google.visualization.Table(document.getElementById('curve_chart'));
        chart.draw(data, options);  
      }

    </script>
  </head>
  <body>
    <div id="curve_chart" style="width: 900px; height: 500px"></div>
  </body>
</html>

And this is the 'echo' output

{"cols":[{"label":"Time","type":"datetime"},{"label":"Probe 1","type":"number"},{"label":"Probe 2","type":"number"},{"label":"Probe 3","type":"number"},{"label":"Probe 4","type":"number"}],"rows":[{"c":[["03:02:07"],["270.26"],["298.40"],["111.54"],["228.06"]]},{"c":[["03:28:42"],["273.23"],["190.43"],["245.69"],["283.21"]]},{"c":[["07:26:04"],["144.33"],["217.26"],["206.53"],["167.68"]]},{"c":[["12:13:20"],["153.15"],["277.23"],["167.20"],["240.88"]]}]}

This is test data, using a test query on the db. Once I understand the formatting to render the chart, it will be setup to allow the user to select which date to view, etc.

This was the closest existing question I can find, but doesn't seem to answer the question.

Not able to generate a Google Chart using MySQL table data as the data source

Following the answer of @MickMackusa, I managed to hack this together to get it to work, by ensuring the mysql/php array was output in a manner acceptable to Google Charts.

Thanks to @MickMacUSA for his assistance.

The final, working code, is below.

<?php include 'confile.php';

$qry = "SELECT time,p1,p2,p3,p4 from $db WHERE date = '2016-04-16' ORDER BY time ASC";
$result = $conn->query($qry);

if($result === FALSE) {
    echo mysqli_errno($result) .": ". mysqli_error($result) ."/n";
    die(mysqli_error());
}
    $i = 0; //iteration counter - start at 0

    $totalRows = mysqli_num_rows($result); // we need this to know when to change the output
    $targetRows = $totalRows - 1; //row indies start from 0, not 1.

    foreach ($result as $row){ 

        $comTime = str_replace(":",",",$row['time']); // for each row, remove the : and put , in its place
        if ($targetRows == $i) { // if the index is the same value as the target (ie, it's the last row)...

            $temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."]". PHP_EOL;
            } else {
            $temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."],". PHP_EOL;
            }
        $i = $i + 1; 
        $rows[] = $temp; 
    }

 $table = $rows;
 $data = implode($table); //format the table as a single string, with line returns

//echo $i;
//echo $data;

?>
<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
  </head>
  <body>
    <div id="chart" style="width: 900px; height: 500px"></div>

    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart(){
        var data = new google.visualization.DataTable();
            data.addColumn('timeofday','Time'); 
            data.addColumn('number','Probe 1');
            data.addColumn('number','Probe 2');
            data.addColumn('number','Probe 3');
            data.addColumn('number','Probe 4');

            data.addRows([              
                <?php echo $data; ?> //dump the result into here, as it's correctly formatted   
            ]);

        var options = {
            title: 'Recorded Temperatures',
            legend: { position: 'bottom' },
            width: 900,
            height: 500,
            hAxis: { format: 'hh:mm:ss' }
        }; 

    var chart = new google.visualization.LineChart(document.getElementById('chart'));
      chart.draw(data, options);    
      }
    </script>
  </body>
</html>

1条回答
时光不老，我们不散
2楼-- · 2019-05-10 02:25

Your number values must be formatted differently and you want timeofday not datetime.

According to: https://developers.google.com/chart/interactive/docs/reference#dataparam

Format your data to look like this:

{cols:
    [
        {"label":"Time","type":"timeofday"},
        {"label":"Probe 1","type":"number"},
        {"label":"Probe 2","type":"number"},
        {"label":"Probe 3","type":"number"},
        {"label":"Probe 4","type":"number"}
    ],
rows:
    [
        {c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
        {c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
        {c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
        {c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
    ]
}

And you must echo it in the javascript:

change:

<?$jsonTable?>

to:

<?php echo $jsonTable; ?>

And put your javascript code block just before your </body> tag.

This is the full working code using the above data format that I tested on my server:

<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
</head>
<body>
    <div id="curve_chart" style="width: 900px; height: 500px"></div>

    <script type="text/javascript">
    google.charts.load('current', {'packages':['corechart']});
    google.charts.setOnLoadCallback(drawChart);

    function drawChart(){
        var data = new google.visualization.DataTable(
            {cols:[
                {"label":"Time","type":"timeofday"},
                {"label":"Probe 1","type":"number"},
                {"label":"Probe 2","type":"number"},
                {"label":"Probe 3","type":"number"},
                {"label":"Probe 4","type":"number"}
            ],
            rows:[
                {c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
                {c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
                {c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
                {c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
            ]
        });

        var options = {
            title: 'Recorded Temperatures',
            legend: { position: 'bottom' },
            width: 900,
            height: 500,
            hAxis: { format: 'hh:mm:ss' }
        };

        var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
        chart.draw(data, options);   
    }
    </script>
</body>
</html>

This is an alternative format that will be simpler/clearer/easier to build/comprehend using your mysqli results:

<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
</head>
<body>
    <div id="curve_chart" style="width: 900px; height: 500px"></div>

    <script type="text/javascript">
    google.charts.load('current', {'packages':['corechart']});
    google.charts.setOnLoadCallback(drawChart);

    function drawChart(){
        var data = new google.visualization.DataTable();
            data.addColumn('timeofday','Time');
            data.addColumn('number','Probe 1');
            data.addColumn('number','Probe 2');
            data.addColumn('number','Probe 3');
            data.addColumn('number','Probe 4');
        data.addRows([
            [[03,02,07],270.26,298.40,111.54,228.06],
            [[03,28,42],273.23,190.43,245.69,283.21],
            [[07,26,04],144.33,217.26,206.53,167.68],
            [[12,13,20],153.15,277.23,167.20,240.88]
        ]);

        var options = {
            title: 'Recorded Temperatures',
            legend: { position: 'bottom' },
            width: 900,
            height: 500,
            hAxis: { format: 'hh:mm:ss' }
        };

        var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
        chart.draw(data, options);   
    }
    </script>
</body>
</html>

See the SO Demo provided by WhiteHat:

google.charts.load('current', {
  callback: drawChart,
  packages: ['corechart', 'table']
});

function drawChart() {
  var data = new google.visualization.DataTable({cols: [
      {"label":"Time","type":"timeofday"},
      {"label":"Probe 1","type":"number"},
      {"label":"Probe 2","type":"number"},
      {"label":"Probe 3","type":"number"},
      {"label":"Probe 4","type":"number"}
    ],
    rows: [
      {c:[{v:[03,02,07],f:'03:02:07'},{v:270.26},{v:298.40},{v:111.54},{v:228.06}]},
      {c:[{v:[03,28,42],f:'03:28:42'},{v:273.23},{v:190.43},{v:245.69},{v:283.21}]},
      {c:[{v:[07,26,04],f:'07:26:04'},{v:144.33},{v:217.26},{v:206.53},{v:167.68}]},
      {c:[{v:[12,13,20],f:'12:13:20'},{v:153.15},{v:277.23},{v:167.20},{v:240.88}]}
    ]
  });

  var table = new google.visualization.Table(document.getElementById('chart_0'));
  table.draw(data);

  var options = {
      title: 'Recorded Temperatures',
      legend: {position: 'bottom' },
      width: 800,
      height: 600,
      hAxis: {
        format: 'hh:mm:ss'
      }
  };
  var chart = new google.visualization.LineChart(document.getElementById('chart_1'));
  chart.draw(data, options);
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_0"></div>
<div id="chart_1"></div>

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)