How do I update a Google Gauge with MySQL data?

[ANDREW EDWARDS]

Hi,

HELLLLLP!!!

I am doing a project where I have to update a Temperature Gauge and a Chart with data from a database (MySQL). I have created a webpage, created the database table, used a gauge from Google Charts but I do not how to get the gauge to work using the data from the database.

Here is the code for the gauge, I need to know what type of code that I have to use (AJAX, JSON etc) and then how do I get the result in to the gauge.

<script type="text/javascript">

google.charts.load('current', {'packages':['gauge']});

google.charts.setOnLoadCallback(drawChart);

function drawChart() {

        var data = google.visualization.arrayToDataTable([

          ['Label', 'Value'],

          ['Temp°C', 19], <!--Change temperature here-->

          

        ]);

<!--size of the gauge and some detail that can be altered here-->

        var options = {

          width: 400, height: 120,

 blueFrom: 0, blueTo: -10,

 greenFrom: 0, greenTo:20,

          redFrom: 40, redTo: 100,

          yellowFrom:20, yellowTo: 40,

          minorTicks: 5

        };

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

        chart.draw(data, options);

        setInterval(function() {

          data.setValue(0, 1, 40 + Math.round(jsonData));

          chart.draw(data, options);

        }, 500);

       

}

</script>

I have been going crazy for the past week so any help or guidance will be much appreciated.

Thanks

[Gabriel Mergulhao]

<script type="text/javascript">

google.charts.load('current', {'packages':['gauge']});

google.charts.setOnLoadCallback(drawChart);

function drawChart() {

        var data = google.visualization.arrayToDataTable([

          ['Label', 'Value'],

          ['Temp°C', <?php

                            

                            $sql1 = "SELECT temperature from datalogger WHERE sensor_id='1' ORDER BY date_time DESC LIMIT 1";

                            $query1 = mysqli_query($GLOBALS['SQL'], $sql1);

if (mysqli_num_rows($query1) > 0) {

    // output data of each row

    while($row = mysqli_fetch_assoc($query1)) {

        echo $row["temperature"];

    }

} else {

    echo "0 results";

}], <!--Change temperature here-->

          

        ]);

<!--size of the gauge and some detail that can be altered here-->

        var options = {

          width: 400, height: 120,

 blueFrom: 0, blueTo: -10,

 greenFrom: 0, greenTo:20,

          redFrom: 40, redTo: 100,

          yellowFrom:20, yellowTo: 40,

          minorTicks: 5

        };

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

        chart.draw(data, options);

    

</script>

//////////////////////////

//////////////////////////

Adjust sql1 to your needs and global SQL to your DB info.

[kenhes]

Did you get this working Andrew? Gabriel, I'm not sure what you mean by: adjust global SQL to your DB info.

[Gabriel Mergulhaoo]

create a file sql.php and add following:

<?php

    $DB_HOST = 'localhost';//Host of the mysql

    $DB_USERNAME = 'root';//Username for mysql database

    $DB_PASSWORD = '';//Password for mysql database

    $DB_DATABASE = 'test';//Name of the database

    $DB_LINK = mysqli_connect($DB_HOST, $DB_USERNAME, $DB_PASSWORD); //Create a link to the database

    if (!$DB_LINK) { //if unable to connect to the database

        die('Unable to connect to the database...'); //stop all scripts and kill the page

    }

    //After connection, put a database into the scope

    mysqli_select_db($DB_LINK, $DB_DATABASE) or die(trigger_error("Unable to select the database: " . mysqli_error($DB_LINK)));

    //Transfer global data from DB_LINK to SQL

    $GLOBALS['SQL'] = $GLOBALS['DB_LINK'];

?>

edit username, password and database then back to the script from previous post:

 mysqli_query($GLOBALS['SQL'], $sql1);

that function executes the query. "$GLOBALS['SQL']" is the DB link and $sql1 is the query saved.

[Gabriel Mergulhaoo]

dont forget to add at the very beginning of the php file which contains the gauge code

require_once("GLOBALS.php");

[kenhes]

Thanks Gabriel. I'll give this a try.

[kenhes]

One more thing: I'm connecting this to a sensor which is constantly updating the Mysql table. Will this code always be updating with the sensor results?

On Tuesday, April 10, 2018 at 6:20:39 PM UTC+1, Gabriel Mergulhaoo wrote: