0

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!

Improve this question
4
  • 1
    That's a very odd date format. What does the Google charts documentation say it expects to receive for a date value? Please provide a link to any relevant docs, too. Also where are you looking to try and detect errors? Normally the browser's Console is the place to look first, for anything which relies on JavaScript Commented Jan 1, 2023 at 19:36
  • Thanks for your answer! Here you can find the requirements from google: developers.google.com/chart/interactive/docs/datesandtimes I also added a working example with "manual" data. Now I want to do the same with imported SQL data Commented Jan 1, 2023 at 19:48
  • 1
    Ok thanks. The problem is because you're producing data as a JSON string, not a JS object literal, you can't use the DateTime constructor. What you're outputting from PHP appears to imitate that, but it doesn't work because when you serialise it to JSON, it just becomes a string, not executable code. I think you need to follow the string date format described further down in the documentation: developers.google.com/chart/interactive/docs/… . Should be similar to your current attempt but without the new Commented Jan 1, 2023 at 21:53
  • I reckon that you pass your php payload to the client-side, then you parse the column of dates and instantiate the js date objects before passing the payload to the Google chart. Convert values in array to date objects jquery Commented Jan 2, 2023 at 6:14

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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'));

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.