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!
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:
Code: Select all
<?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();
}
?>
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