Help with line chart from MySQL database table and PHP webserver

1,073 views
Skip to first unread message

Andrew Grimm

unread,
Aug 14, 2016, 3:42:24 PM8/14/16
to Google Visualization API

Hi Guys:


I'm looking for some help on creating a basic line chart on my hosted web server I built using a Raspberry Pi. I'm running a LAMP (Linux, Apache, MySQL, PHP) system. I have a Python script that continuously runs and collects the weather data from various sensors and in 5 minute intervals, writes the data into the appropriate columns into the database table. Then web server who is running the PHP script, retrieves the appropriate data from the table and displays it into a web format. 


The screen shot above shows the weather data being displayed and updating every 5 minutes. So now the next thing I want to do is to add a basic line graph (using google charts) that connects to my database and then displays that information at the bottom. For starters, lets say I just want to graph the temperature (y-axis) vs. the time (x-axis). This data is already stored and available in my database table (see below).



So, if you look at my table columns, you can see I have the temperature readings (column = temperature) and then the date/time (column = stamp). So the goal is to take my current code in my index file (file used on web-server) and the code to create this simple graph. 


Lastly, I've included a copy of my current PHP code that displays the data. I'm very illiterate on this interface I'm trying to build but any help is greatly appreciated. 









Thanks,

Andrew 



Emile

unread,
Aug 18, 2016, 8:40:23 AM8/18/16
to Google Visualization API
<?php

$hostname = "localhost";
$username = "xxxxxx";
$password = "xxxxxx";
$database = "ecb";
$tabel    = "eurorates";

// Create connection
$conn = mysqli_connect($hostname, $username, $password, $database);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

//set array variable 
$results = array();

//talk to the db
$sql="SELECT * FROM eurorates WHERE currency = 'GBP' ORDER BY date DESC limit 100";
$result = mysqli_query($conn, $sql);

//count the rows and fields
$totalRows = mysqli_num_rows($result);
$totalFields = mysqli_num_fields($result);

//start the loop
for ( $i = 0; $i < $totalRows; ++$i ) {

//make it 2 dim in case you change your order
  $results[$i] = mysqli_fetch_array($result); 
 }

?>

<html>
  <head>
    <script type="text/javascript" src="http://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();

        data.addColumn( 'string', 'Datum' );
        data.addColumn( 'number', 'Rate' );

        data.addRows(100);
        
        <?php
 
 $i = 0;
 $numofloops = 100;

 while($i < $numofloops){
          
 echo "data.setValue($i, 0, '" . $results[$i]["date"] . "');";
 echo "data.setValue($i, 1,  " . $results[$i]["rate"] . ");";

 $i++; 
          }
        ?>
   
        var options = {title: 'Echangerate EUR - GBP',
                       vAxis: {title: "Rate"},
                       hAxis: {title: "Date"},
      colors: ['red','#004411'] 
};

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw( data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 1500px; height: 800px;"></div>
  </body>
</html>

graph.png
Message has been deleted

andrew....@gmail.com

unread,
Aug 20, 2016, 3:50:01 PM8/20/16
to Google Visualization API
@Emily:

I copied your exact code, changed the database info to my credentials, changed the SQL query to the fetch the data I needed, and I get a blank page when opening. There is no graph shown. Is there something I'm missing? 

Thanks,
Andrew

Daniel LaLiberte

unread,
Aug 21, 2016, 8:44:13 AM8/21/16
to Google Visualization API
To debug this, replace your chart type with Table, since a Table should be able to display any data you give it.  If you can't get that far, then you need to look in the JavaScript debugger for your browser to find out what it says.  If you have a JavaScript syntax error, for example, you won't get very far.  

If nothing shows up in the browser when you view the page source, then you probably have a problem on your server side.
Hope that helps get you started.


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
To post to this group, send email to google-visualization-api@googlegroups.com.
Visit this group at https://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/af0126ef-d873-4380-b4d9-487ab6fd72a5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--

Matthew Vidangos

unread,
Oct 18, 2017, 3:51:55 PM10/18/17
to Google Visualization API
good evening

i copied the code and adapted it with my database but only draws the last entry i dont know what im doing wron



<?php

$hostname = "localhost";
$username = "waru";
$password = "olairhead154";
$database = "inmueble";
$tabel    = "inmuebles";


// Create connection
$conn = mysqli_connect($hostname, $username, $password, $database);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

//set array variable
$results = array();

//talk to the db
$sql="SELECT count(*) as Total ,fecha  FROM inmuebles GROUP BY fecha ORDER BY fecha DESC limit 100";

$result = mysqli_query($conn, $sql);
//count the rows and fields
$totalRows = mysqli_num_rows($result);
$totalFields = mysqli_num_fields($result);

//start the loop
 while ($row = mysqli_fetch_assoc($result)) {
                    $results[] = $row;
                    echo $results;

    }



?>

<html>
  <head>
    <script type="text/javascript" src="http://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();

        data.addColumn( 'string', 'Fecha' );
        data.addColumn( 'number', 'Total' );


        data.addRows(100);
       
        <?php
 
   $i = 0;
   $numofloops = 100;

   while($i < $numofloops){
         
   echo "data.setValue($i, 0, '" . $results[$i]["fecha"] . "');";
   echo "data.setValue($i, 1,  " . $results[$i]["Total"] . ");";


   $i++;
          }
        ?>
  
        var options = {title: 'Echangerate EUR - GBP',
                       vAxis: {title: "Total"},
                       hAxis: {title: "Fecha"},
          colors: ['red','#004411']
      };

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

        chart.draw( data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 1500px; height: 800px;"></div>
  </body>
</html>



To post to this group, send email to google-visua...@googlegroups.com.



--

Reply all
Reply to author
Forward
0 new messages