How to read a temperature probe, save the value to a MySQL database and then show the value on a webpage or just show the value with no database.

 

See this link for how to setup a DS18B20+ thermal probe with a Raspberry Pi, these instructions  are the best that i have come across for this and will cover the bullet points below :

• Enabling the 1 wire features in the Raspbian OS
• Physically connecting the probe via a resistor to the RPi
• Instructions on how to read the probe and a script example

https://www.modmypi.com/blog/ds18b20-one-wire-digital-temperature-sensor-and-the-raspberry-pi

Once you have followed the above steps you should have a working temperature probe, that’s great.. but what can you do with it now!?

For the piTank/piGrow projects,  I started off by just using the script provide in the instructions above, modifying it slightly and using that to display the temperature value on the piTank web-page. Using some JavaScript you can make the box’s refresh every x seconds, running the probe read script and showing a new value without the need for a page reload.  I call this a ‘live’ reading.

This is how a live reading is taken:

Create a PHP file and copy the below code into it.  I saved this as temp1.php.

<?php
# Program:    piGrow
# Version:    1.0
# Probe:    P1 - Canopy

        //File to read
        $file = '/sys/bus/w1/devices/*DEVICE-ID*/w1_slave';
 
        //Read the file line by line
        $lines = file($file);
 
        //Get the temp from second line 
        $temp = explode('=', $lines[1]);
 
        //Setup some nice formatting (i.e. 21,3)
        $temp = number_format($temp[1] / 1000, 1, '.', '');
 
        //And echo that temp
        echo "Canopy Temp <br>";
        echo $temp . "C";
?>

You will need to replace *DEVICE-ID*  with the device ID/serial number of the probe that you have.

Upload the file to your web server and allow the relevant permissions.

Use the <?php include(“temp1.php”);?> syntax to display the probes value in your webpage (index.php).

To get this value to update every 60000  milliseconds (60 seconds) without reloading the page, Place the following Java Script inside the head of the page and ensure the <?php include (” “);?> has an object ID.

var auto_refresh = setInterval(function()
{ 
$('#temp1').load('temp1.php').fadeIn("slow");
}, 60000);

Basically the JS says reload this ID with this PHP script every minute.

<div class="tempbox" id="temp1"><?php include("temp1.php");?></div>

That was great, but I now wanted to log the temperature values to a database.  With the values logged to a DB I can then query the DB and use the latest or average values in different action scripts ,turning heaters/pumps/air/filters on and off. This value I call a stored reading.

This is how I take a stored reading

This presumes you already have a MySQL instance setup andrunning, with a username/password and DB ready.

Create a bash script to read the temp value and and hold it for the next step of the script, this next step sends the live value to the MySQL DB creating a stored reading:

Basically, import some bits for the script to run, grab a value, connect and send to DB along with the date and time, everything goes right end as normal or if something goes wrong, send a close signal to  the DB and error out.  Note, some excess code is in there to aid debugging. Have a play!

You will need to  update the Device ID of the thermal probe, DB connection details, Table details and check everything over yourself.

#!/usr/bin/env python
# Program:    piGrow
# Version:    1.0
# Probe:    1 

print "Script : temp1log.py : Canopy : Start"

import os
import time
import datetime
import glob
import MySQLdb
import sys
from time import strftime

os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')
temp_sensor = '/sys/bus/w1/devices/**DEVICE ID*/w1_slave'

db = MySQLdb.connect("localhost","USERNAME","PASSWORD","DATABSE" )
cur = db.cursor()

def temp_raw():
    f = open(temp_sensor, 'r')
    lines = f.readlines()
    f.close()
    return lines
    
def read_temp():
    lines = temp_raw()
    while lines[0].strip()[-3:] != 'YES':
        time.sleep(1)
        lines = temp_raw()
    temp_output = lines[1].find('t=')
    if temp_output != -1:
        temp_string = lines[1].strip()[temp_output+2:]
        temp_c = float(temp_string) / 1000.0
        return temp_c
        
while True:
    temp = read_temp()
    print temp
    datetimeWrite = (time.strftime("%Y-%m-%d ") + time.strftime("%H:%M:%S"))
    print datetimeWrite
    sql = ("INSERT INTO temp1 (datetime,temperature) VALUES (%s,%s)",(datetimeWrite,temp))
    try:
        print "Writing to database..."
        # Execute the SQL command
        cur.execute(*sql)
        # Commit your changes in the database
        db.commit()
        print "Write Complete"

    except:
        # Rollback in case there is any error
        db.rollback()
        print "Failed writing to database"

    cur.close()
    db.close()
    break
    
print "Script : temp1log.py : End"
sys.exit

Once the value has been saved you can display it on your webpage using the same method as the live reading, just the PHP file that is called has a different content:

This PHP file will query the MySQL database for the last entered row of that table and then output the value.

<?php
# Program:    piGrow
# Version:    1.0
# Probe:    1

$mysqli = new mysqli("localhost", "USERNAME", "PASSWORD", "DATABASE");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}
$query = "SELECT temperature FROM temp1 ORDER BY id DESC LIMIT 0, 1";
$result = $mysqli->query($query);
/* numeric array */
$row = $result->fetch_array(MYSQLI_NUM);
printf ($row[0]);
/* free result set */
$result->free();
/* close connection */
$mysqli->close();
?>

Then just call the PHP file using the same PHP include syntax as in the live reading

Finally, set temp1log.py script to run every minuted in cron, all done!

pi@pigrow:~ $ sudo crontab -e

Add the following line to the bottom of the file.

*/1 * * * * python /home/pi/scripts/temp1log.py &