Google chart date and time from database

2019-06-13 16:08发布

站内文章 / JavaScript
29 0 0

问题:

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:

回答1:

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;


回答2:

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.



标签： javascript php html datetime google-visualization
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~