Reading from a Database

Now that we have the temperature data in our database, we can write a program to read it back out and display it in a web browser.

This is how we get around the problem of the tiny computer getting reset every time someone opens the serial port connection to it. One program connects to the serial port once, and reads the data into a database forever, without ever closing the serial line. The other reads from the database, instead of contacting the tiny computer directly.

There are several advantages to this. When data is in a database, we can ask for it in many ways. We can write a database query that asks for just the last minute's worth of data. Or we can ask for the last 200 samples, the first 1,000 samples, or the samples from last week. And, since the computer sending the data to the database knows what time it is, each record can have a time-stamp. We can even extract the data we want and output it into the "comma separated value" format used by spreadsheets, so we can do further processing or graphing.

The PHP program to read the last minute's worth of data and present it on a web page is here.

The program is actually fairly simple. At the heart of it, it simply connects to the database, and selects all the data recorded in the last minute. This is all done in the constructor for the Read_Database_Temperatures class. The MySQL code for selecting the records is:

SELECT id, mean, std_dev, reg_date FROM temps WHERE reg_date >= (CURTIME() - INTERVAL 1 MINUTE)

Each record has four data items: an ID (simply a counter that increases by one for each new record), the mean temperature, the standard deviation, and a time-stamp called reg_date. We select those four things from every record that matches the criterion after the WHERE. That is, all records from the current time, back to one minute ago.

The result is a list of "rows", where each of our four data items represent "columns" of data. The function output() fetches each row one at a time, and formats the data in an HTML table for the web page.

There were 55 records collected in the last minute. That is because our tiny computer takes 200 readings, and then does some processing, and then waits for a full second before looping back to do it again. That means each reading takes just a little longer than a second, so we don't get a full 60 of them per minute. (Of course, if that bothers you, you can delay 999 milliseconds, or 998, or whatever number comes closest to making the readings happen every second, but the two clocks on the two computers will never be perfectly in sync, so every once in a while a reading will have a time-stamp that jumps a second, or there will be two samples recorded in the same second. But there will always be the same actual delay between samples, so while it might look like we missed a sample, we really haven't.)