CODEWITHSUNDEEP
phpsqldatetimecharts
DanielD
DanielD

Reputation: 1

Using mysql data for google chart

I have data from my raspberry pi weather station, which are pushed on my own mysql server. Data is looking like this:

SQL Data

With this data I want to create a google chart to visualize. My problem is that I cannot use datetime, because google is expecting kind of special format. Since days I am reading tons of articles on stackoverflow, but there is not one example in the internet, which really works.

What I have done until now is:

1) Reading Data from SQL and formatting to JSON

    $conn = mysqli_conn--ect('192.168.178.39', '-', '---!', 'wetterstation');
    $sql = 'SELECT cur_datum, Temperatur_DPS310 FROM Outdoor';
    $result = mysqli_query($conn, $sql);
    

    foreach($result as $r) {
          $temp = array();
          // The following line will be used to slice the chart

          $temp[] = array('cur_datum' => 'Date('.date('Y',strtotime($r['cur_datum'])).',' . 
                                         (date('n',strtotime($r['cur_datum'])) - 1).','.
                                         date('d',strtotime($r['cur_datum'])).','.
                                         date('H',strtotime($r['cur_datum'])).','.
                                         date('i',strtotime($r['cur_datum'])).','.
                                         date('s',strtotime($r['cur_datum'])).')'); 

          // Values of the each slice

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

$table['rows'] = $rows;
// convert data into JSON format
$jsonTable = json_encode($table);

--> JSON Format seems to be ok JSON Format Check

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

// Load the Visualization API and the piechart package.
google.load('visualization', '1.0', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);


// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function drawChart() {
        var data = new google.visualization.DataTable(<?php echo $jsonTable; ?>);

        var options = {
          title: 'Testest',
          curveType: 'function',
          legend: { position: 'bottom' },
          tooltip: {isHtml: true}
        };

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

</script>
</head>
<body>
   <!--Div that will hold the pie chart-->
   <div id="chart_div" style="width:400; height:300"></div>
 </body>
 </html>

--> No google chart visible, normally that means there is a bug.... unfortunately google dont let me see the error meassage, maybe I'm just too stupid to show the error message ... :)

With the following example I was able to create a google chart:

var dataTable = new google.visualization.DataTable();
 dataTable.addColumn('datetime', 'Time');
 dataTable.addColumn('number', 'Price (Euro)');
 dataTable.addRows([
    [new Date(2014, 6, 2,  9,  0, 0, 0), 21.40],
    [new Date(2014, 6, 2, 11,  0, 0, 0), 21.39],
    [new Date(2014, 6, 2, 13,  0, 0, 0), 21.20],
    [new Date(2014, 6, 2, 15,  0, 0, 0), 21.22],
    [new Date(2014, 6, 2, 17,  0, 0, 0), 20.99],
    [new Date(2014, 6, 2, 17, 30, 0, 0), 21.03],
    [new Date(2014, 6, 3,  9,  0, 0, 0), 21.05],
    [new Date(2014, 6, 3, 11,  0, 0, 0), 21.07],
    [new Date(2014, 6, 3, 13,  0, 0, 0), 21.10],
    [new Date(2014, 6, 3, 15,  0, 0, 0), 21.08],
    [new Date(2014, 6, 3, 17,  0, 0, 0), 21.05],
    [new Date(2014, 6, 3, 17, 30, 0, 0), 21.00],
    [new Date(2014, 6, 4,  9,  0, 0, 0), 21.15],
    [new Date(2014, 6, 4, 11,  0, 0, 0), 21.17],
    [new Date(2014, 6, 4, 13,  0, 0, 0), 21.25],
    [new Date(2014, 6, 4, 15,  0, 0, 0), 21.32],
    [new Date(2014, 6, 4, 17,  0, 0, 0), 21.35],
    [new Date(2014, 6, 4, 17, 30, 0, 0), 21.37],
 ]);

 // Instantiate and draw our chart, passing in some options.
 // var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
 var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

 var options = {
    title    : 'AEX Stock: Nationale Nederlanden (NN)',
    width    : 1400,
    height   : 540,
    legend   : 'true',
    pointSize: 5,
    vAxis: { title: 'Price (Euro)', maxValue: 21.50, minValue: 20.50 },
    hAxis: { title: 'Time of day (Hours:Minutes)', format: 'HH:mm', gridlines: {count:9} }
 };

 var formatNumber = new google.visualization.NumberFormat(
    {prefix: '', negativeColor: 'red', negativeParens: true});

 var formatDate = new google.visualization.DateFormat(
    { prefix: 'Time: ', pattern: "dd MMM HH:mm", });

 formatDate.format(dataTable, 0);
 formatNumber.format(dataTable, 1);

 chart.draw(dataTable, options);

--> Manual data works with this format. Now I want to create that format with SQL data Example with manual data

Second try to imitate manual data:

if ($result !== false) {
    $output = Array();
    while ($row = mysqli_fetch_assoc($result)) {
        $DateTimeArray = $row["cur_datum"];
        $MYvalue1 = $row["Temperatur_DPS310"];
    
        $date = date('Y-m-d', strtotime($DateTimeArray));
        $time = date('H:i:s', strtotime($DateTimeArray));

        $dateArray = explode('-', $date);
        $year = $dateArray[0];
        $month = $dateArray[1] - 1; // adjust for javascript's 0-indexed months
        $day = $dateArray[2];

        $timeArray = explode(':', $time);
        $hours = $timeArray[0];
        $minutes = $timeArray[1];
        $seconds = $timeArray[2];
        
        $output[] = "[new Date($year,$month,$day,$hours,$minutes,$seconds), $MYvalue1]";
    }
}

Output looks exactly like the manual data: Output of second try

Error in console: Console Error

Please help!

Upvotes: 0

Views: 530

Answers (2)

Bryan Ward
Bryan Ward

Reputation: 67

Looks like I'm way too late, but for what it's worth, I think this line in your drawChart() function

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

should instead be

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

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33813

I'd suggest that you json_encode the raw db query results rather than performing peculiar datetime manipulations within PHP - what does The following line will be used to slice the chart mean? The format held in the database looks perfectly fine to be used with Google Charts / DataTable - javascript will be able to cast the string values returned into usable date objects and also cast the temperature values as floats.

In a test (using own data to emulate) the JSON printed to the page is like this:

const json=[
    {
        "Temperatur_DPS310": "194",
        "cur_datum": "2022-10-31 15:54:00"
    },
    {
        "Temperatur_DPS310": "150",
        "cur_datum": "2022-11-02 16:08:00"
    },
    {
        "Temperatur_DPS310": "143",
        "cur_datum": "2022-11-02 16:09:21"
    },
    {
        "Temperatur_DPS310": "153",
        "cur_datum": "2022-11-02 16:21:14"
    } //.........etc ( temperatures as fictitious )

And the page that queries the db and renders the chart

<?php

    #dbconn required
    
    $sql='SELECT `cur_datum`, `Temperatur_DPS310` FROM `Outdoor`';
    $res=$db->query( $sql );
    $data=array();
    
    while( $rs=$res->fetch_assoc() )$data[]=$rs;
    $json=json_encode( $data, JSON_PRETTY_PRINT );
?>
<html>
    <head>
        <script src='https://www.gstatic.com/charts/loader.js'></script>
        <script>

            google.load('visualization', '1.0', {'packages':['corechart']});
            google.setOnLoadCallback( drawChart );

            <?php
                printf('
                const json=%s;
                ',$json
                );
            ?>


            function drawChart() {
                // create the empty datatable and add two columns
                let dataTbl = new google.visualization.DataTable();
                    dataTbl.addColumn('datetime', 'Time');
                    dataTbl.addColumn('number', 'Price (Euro)');
                    
                // recordset has two columns per record
                // create each record as an array and let the dataTbl add them
                Object.keys( json ).forEach(key=>{
                    let row=json[ key ];
                    let rs=[
                        new Date( row.cur_datum ),
                        parseFloat( row.Temperatur_DPS310 )
                    ];
                    dataTbl.addRow( rs )
                })
                    
                let options = {/* a mix of optios from the question */
                    title    : 'AEX Stock: Nationale Nederlanden (NN)',
                    width    : 1400,
                    height   : 540,
                    legend   : 'true',
                    curveType: 'function',
                    pointSize: 5,
                    vAxis: { title: 'Price (Euro)', maxValue: 21.50, minValue: 20.50 },
                    hAxis: { title: 'Time of day (Hours:Minutes)', format: 'HH:mm', gridlines: {count:9} },
                    tooltip: {isHtml: true}
                };

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

        </script>
    </head>
    <body>
        <div id='chart_div' style='width:1500; height:600'></div>
    </body>
</html>

The above code, using own data, rendered the following chart:

Example chart

Upvotes: 1

Related Questions