Graphically displaying a SQLite database through PHP & additional languages

[Rich69]

Hi everyone this is my first post so I'm sorry if I've posted it in the wrong section!

 

Before I begin I'd like to point out that I'm not just here to get someone to produce what I want for me! I have a genuine interest in this forum and I'm slightly surprised and disappointed in myself that I hadn't took the time to join sooner!

 

Anyway to the point of this post: (Please bare with me I think I'll need to justify the use of SQLite)

 

I am currently undergoing a University gorup project that uses wireless sensors to collect environmental data such as, Temperature, Humidity, Light levels and Dew point. This application is programmed in Python and it collects these pieces of data (every 30 seconds) and places them into an SQLite database.

 

My job is to now graphically plot/show the data that the Python program collects and stores in the SQLite database on a web page. I'm using Ubuntu and have successfully installed LAMP, SQLite3 and the SQLite PD0 driver.

 

I have also successfully established a connection to the SQLite database as well as displaying the data on a web page (locally - thats all I need!) in the form of a simple HTML table through the use of the following PHP script:

 

<?php
  try
  {
    //open the database
    $db = new PDO('sqlite:/var/databases/307Code/python/readings.db');

    //now output the data to a simple html table...
    print "<table border=1>";
    print "<tr><td>Date & Time Recieved</td><td>Node</td><td>Temp</td><td>Hum</td><td>Light</td><td>Dew</td></tr>";
    $result = $db->query('SELECT * FROM readingstable');
    foreach($result as $row)
    {


      print "<tr><td>".$row['Recieved']."</td>";
      print "<td>".$row['Node']."</td>";
      print "<td>".$row['Temp']."</td>";
      print "<td>".$row['Hum']."</td>";
      print "<td>".$row['Light']."</td>";
      print "<td>".$row['Dew']."</td></tr>";
    }
    print "</table>";

    // close the database connection
    $db = NULL;
  }
  catch(PDOException $e)
  {
    print 'Exception : '.$e->getMessage();
  }
?>

 

What I'm now looking to do is the following:

 

1. Display the data from the SQLite database in the form of line graphs (I've seen that I may need to convert the data to XML?)

2. Make the application asynchronous, preferably every time a new entry is added to the SQLite database the line graph(s) update without a user needing to refresh the browser.

3. The SQLite database is storing a ridiculous floating point number for the time field in the database, for example: "2012-03-23 16:49:42.440818" is a entry in the SQLite database. Is there any way to omit the .440818 through the use of PHP? Or will I need to edit the Python script?

 

Also one thing to note: The person in my group who built the Python script didn't build it to make the SQLite database give each database entry a unique ID/Primary key.

 

Any tips/advice/help would be massively appreciated!

 

Regards,

 

Rich

[litebearer]

Might start here...

 

http://www.plus2net.com/php_tutorial/gd-linegp.php

http://www.mindfiresolutions.com/Creating-a-simple-Line-graph-using-PHP-without-using-Flash-668.php

 

You also may consider ALTERing your db table to implement an auto-increment id field

[Rich69]

Thanks but I'm looking more for something like Google charts but I have no idea how to take the next step. I've worked out that I can add Ajax to a div tag to make it automatically refresh all I need now is a way of representing the data through a graph.

[Rich69]

Bump/Update:

 

I now know how to make the application asynchronous with the use of AJAX.

 

In terms of graphically displaying my SQLite database; I know now its possible, and probably good for my needs to use the Google charts API. However, I think I'm right in saying I would need to get PHP to parse the data into JSON format ready to be passed through the Google charts API?

 

I have no idea how I would go about doing that so any help would be sincerely appreciated!

 

Rich

[litebearer]

http://stackoverflow.com/questions/4816270/getting-data-from-mysql-into-json-using-php

http://webos101.com/Getting_JSON_From_An_External_MySQL_Database