Google chart date and time from database

2019-06-13 16:01发布

站内问答 / JavaScript
1790 2 5

I am working on a google chart experiment. I earlier had a problem to display date with time in x axis. And a user gave me this example in jsbin: http://jsbin.com/yaqew/1/edit where you have the ability to see date and time for each of the dots. The problem i came into was to implement that in my solution:

Phpcode:

   <?php
            $con=mysql_connect("localhost","root","") or die("Failed to connect with database!!!!");
        mysql_select_db("chart", $con);

        $sth = mysql_query("SELECT * FROM googlechart");

        $rows = array();
        //flag is not needed
        $flag = true;
        $table = array();

        $table['cols'] = array(

        array('label' => 'Time', 'type' => 'date'),
        array('label' => 'Date', 'type' => 'date'),
        array('label' => 'PH',      'type' => 'number'),
        array('label' => 'temperature','type' => 'number'), 
        array('label' => 'Chlorine','type' => 'number'),
        );

        $rows = array();

        while($r = mysql_fetch_assoc($sth)) {

        // assumes dates are in the format "yyyy-MM-dd"
        $dateString = $r['Date'];
        $dateArray = explode('-', $dateString);
        $year = $dateArray[0];
        $month = $dateArray[1] - 1; // subtract 1 to convert to javascript's 0-indexed months
        $day = $dateArray[2];

        // assumes time is in the format "hh:mm:ss"
        $timeString = $r['Time'];
        $timeArray = explode(':', $timeString);
        $hours = $timeArray[0];
        $minutes = $timeArray[1];
        $seconds = $timeArray[2];
        echo $dateString."<br>";
        echo $timeString."<br>";
        $temp = array();
        $temp[] = array('v' => "Date($year, $month, $day, $hours, $minutes, $seconds)"); 
        $temp[] = array('v' => (string) $r['PH']);
        $temp[] = array('v' => (string) $r['temperature']);
        $temp[] = array('v' => (string) $r['Chlorine']);

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

        }

        $table['rows'] = $rows;
        $jsonTable = json_encode($table);
         echo $jsonTable;

Html/javascript: 

    <html>
  <head>

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = new google.visualization.DataTable(<?=$jsonTable?>);



        var options = {
        /*width: 900, height: 900, */
          title: 'Visualization',
          curveType: 'function', 
           legend: { position: 'bottom' },
           pointSize: 12,
        vAxis: {title: "Values", titleTextStyle: {italic: false}},
        hAxis: {title: "Time", titleTextStyle: {italic: false}},
        explorer: { 
                actions: ['dragToZoom', 'rightClickToReset'], 
                axis: 'vertical'
            }


        };

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);


      }
    </script>
  </head>

This is how my database (phpmyadmin) looks like:

enter image description here

2条回答
Deceive 欺骗
2楼-- · 2019-06-13 16:30

I found out a way of doing this in the database so you get exactly the information you need for doing this, without all the code wizardry. I'll share it here in case someone else stumbles upon this question and likes to do it this way.

So this is how my query looks like since I had one column with type datetime:

SELECT 
    CONCAT(DATE_FORMAT(date, '%Y, '),
            DATE_FORMAT(date, '%c') - 1,
            DATE_FORMAT(date, ', %e, %H, %i, %S')) AS date
FROM
    my_table

I have tested this query, and it works, but I guess the one for OP's would look something like this:

SELECT 
    CONCAT(DATE_FORMAT(Date, '%Y, '),
        DATE_FORMAT(Date, '%c') - 1,
        DATE_FORMAT(Date, ', %e, '),
        DATE_FORMAT(Time, '%H, %i, %S')) AS date,
    amount
FROM
    my_table

For a sample of the PHP code look at @asgallant post. This is a slight variation to his code:

$temp[] = array('v' => "Date(your_preferred_method_for_fetching_the_query_above)");

Note: credit also goes to @asgallant as I came upon this idea after I first did it this way.

Questions and suggestions are welcome.

查看更多
0人赞 添加讨论(0) 举报
Animai°情兽
3楼-- · 2019-06-13 16:48

You need to input both date and time into your DataTable, as a 'datetime' data type:

$table['cols'] = array(
    array('label' => 'Time', 'type' => 'datetime'),
    array('label' => 'PH', 'type' => 'number'),
    array('label' => 'temperature','type' => 'number'), 
    array('label' => 'Chlorine','type' => 'number'),
);

$rows = array();

while($r = mysql_fetch_assoc($sth)) {
    // assumes dates are in the format "yyyy-MM-dd"
    $dateString = $r['Date'];
    $dateArray = explode('-', $dateString);
    $year = $dateArray[0];
    $month = $dateArray[1] - 1; // subtract 1 to convert to javascript's 0-indexed months
    $day = $dateArray[2];

    // assumes time is in the format "hh:mm:ss"
    $timeString = $r['Time'];
    $timeArray = explode(':', $timeString);
    $hours = $timeArray[0];
    $minutes = $timeArray[1];
    $seconds = $timeArray[2];

    $temp = array();
    $temp[] = array('v' => "Date($year, $month, $day, $hours, $minutes, $seconds)"); 
    $temp[] = array('v' => (string) $r['PH']);
    $temp[] = array('v' => (string) $r['temperature']);
    $temp[] = array('v' => (string) $r['Chlorine']);

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

$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)