Saving Results in a Database

Earlier we showed how to save the temperature data in a file on the host computer. But saving the data in a database has some advantages.

PHP and the database program MySQL go together so well that our XAMPP package bundles them together. So if you are running XAMPP's Apache as your web server, and using XAMPP's PHP as your programming language, your database software is already installed and up and running.

The data we are going to store is simply the mean and standard deviation from our thermometer program. Because we don't need any text, (we just want the numbers), we can simplify our program a bit. Here is the (slightly simpler) version that just prints out a number, a comma, and another number, for line after line, every second. As before, you will need to change the CALIBRATION_OFFSET constant to the correct number for your own thermistor to guarantee accurate readings.

We have also added a class to our PHP program (the one that reads the data from the tiny computer over the USB serial port). The new class handles all the work involved in talking to the database. I am not going to go into detail on the bulk of the code in this new class -- we will treat it as a "black box" that just does the work. The part we will use is the very simple method called save_temp().

The whole program can be found here.

The parts we will be discussing are shown below:

class Temperature_Database
  {
    // ... a bunch of "black box" stuff we will ignore for now ...
    public function save_temp()
    {
      // ... more black box stuff ...
    }
  }
  $serial = new Serial();
  $db = new Temperature_Database();
  if( $serial->open( "com3:" ) )
  {
    for( ;; )
    {
      $line = $serial->readline();
      $pieces = explode( ",", $line );
      $mean = $pieces[0];
      $std_dev = $pieces[1];
      print( "Mean: " . $mean . ", Standard Deviation: " . $std_dev . PHP_EOL );
      $db->save_temp();
    }
    $serial->close();
  } 
  else
    print( "Can't open com3:" );

As before, we create an instance of our Serial class. But now we also create an instance of our new Temperature_Database class.

Inside the loop, we read a line from the serial port, as we did before. But now the line is just two numbers, separated by a comma, like this:

75.3,0.15

We use PHP's explode() function to put the numbers into the array $pieces. We use square brackets to reference elements of arrays, so $pieces[0] will hold the number 75.3, and $pieces[1] will hold the number 0.15.

The MySQL functions need to see those two numbers in the global variables $mean and $std_dev in order to store them in the database. So we copy them out of the $pieces array into those variables.

We print them out just so the user knows the program is working.

Then we call $db->save_temp() to store them in the database. (The -> operator is how PHP connects an instance of a class to methods in the class.)

Now we can run the program from a CMD window (in Windows) or a terminal window (in Linux):

php database_temperature.php

For as long as we let it run, it will capture temperature data and store it in the database.

If you don't want to watch it run, you can use

php-win database_temperature.php

on Windows, and it will quietly run in the background with no output.

On Linux, you could type

php database_temperature.php > /dev/null &

to do the same thing.