Raspberry Pi Temperature Sensor Web Server - Part 3 (Scheduling temperature readings and PHP script)

Now wouldn't it be nice to have our python script work autonomously, putting a temperature reading into the database every 5 minutes. To do this we will use Crontab, which is a handy unix tool to schedule jobs. A good explanation of what crontab is can be found here.

To open Crontab enter the following in your terminal.

crontab -e

Inside the Crontab file enter the following at the bottom. This simply runs our Python script every 5 minutes. If you have used different file and folder names, adjust them accordingly.

*/5 * * * * /home/pi/tempLog/readTempSQL.py

At its current state this will not work though because the readTempSQL.py script isn't yet executable so the Cronjob will fail. To make the Python script executable, firstly enter the following at the top of the readTempSQL.py file.

#!/usr/bin/env python

This is a 'shebang' line, which Wikipedia will tell you "In computing, a shebang (also called a hashbang, hashpling, pound bang, or crunchbang) refers to the characters "#!" when they are the first two characters in an interpreter directive as the first line of a text file. In a Unix-like operating system, the program loader takes the presence of these two characters as an indication that the file is a script, and tries to execute that script using the interpreter specified by the rest of the first line in the file."

Now we just need to change the file permission to executable.

sudo chmod +x readTempSQL.py

To test that the file is now executable, navigate to your tempLog directory and enter

./readTempSQL.py

The script should run and print an output to the terminal window. Congratulations the file is now executable. Check your database after 10 minutes or so to make sure your Cronjob is working as when the script is run by Crontab the output is not displayed in the terminal window.

Now lets make a php script to return our database data. Change directory to /var/www and make a new file called temperaturejson.php.

sudo nano temperaturejson.php

Enter the following into that new file.

<?php
  
$username="root";
$password="password";
$database="temp_database";
  
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
  
$query="SELECT * FROM tempLog";
$result=mysql_query($query);
  
$num=mysql_numrows($result);
  
mysql_close();
  
$tempValues = array();
  
$i=0;
while ($i < $num)
{
        $dateAndTemps = array();
        $datetime = mysql_result($result,$i,"datetime");
        $temp = mysql_result($result,$i,"temperature");
  
        $dateAndTemps["Date"] = $datetime;
        $dateAndTemps["Temp"] = $temp;
  
        $tempValues[$i]=$dateAndTemps;
        $i++;
}
  
echo json_encode($tempValues);
  
?>

This new php script gets the data from our database and then loops through the results putting them into an array where the key is either "Date" or "Temp" and the corresponding object is the result from the database.You don't really need to give the objects keys but I think it makes it much easier when getting the data into the app. These arrays are then added to a larger array holding each reading of Date and Temp, which is then encoded into json and output using echo.

Now test this out. Using any browser go to the following address (substituting my IP for yours of course!). If you don't get the desired output firstly check you can get to the test page on the Raspberry Pi. If you can try changing the echo to just output a string to test the page. Then check your database to make sure you actually have data to display.

http://192.168.0.11/temperaturejson.php

If all has worked correctly you will get an output similar to this

[{"Date":"2014-12-28 17:26:20","Temp":"18.90"},{"Date":"2014-12-28 17:27:05","Temp":"18.90"},{"Date":"2014-12-28 17:27:52","Temp":"18.90"},{"Date":"2014-12-28 17:30:39","Temp":"19.00"},{"Date":"2014-12-28 17:31:02","Temp":"18.90"},{"Date":"2015-01-04 22:29:24","Temp":"18.60"},{"Date":"2015-05-14 20:56:07","Temp":"21.80"},{"Date":"2015-05-17 19:55:05","Temp":"22.90"},{"Date":"2015-05-17 19:56:17","Temp":"22.90"},{"Date":"2015-05-17 20:06:18","Temp":"23.00"},{"Date":"2015-05-17 20:47:03","Temp":"23.20"}]

That is a wrap for the Raspberry Pi stuff. Now we move on to making an iOS app to do something with our data.

← Back to home