Raspberry Pi and SQLite3 and a Web Server

Remember when I thought it would be easy to get something out of an SQLite3 data base and put it on a screen with a web server?  I don't care how old I get, I'm still way too naive.  This turned out to be a royal pain in the bottom.  It started out when I went looking for a language to do it in.  Since I haven't ever used php, I decided to try it out.  It took me almost three hours of research to figure out what I needed to do to get it to work with the web server, and then another couple of hours to get php to work with SQLite3.  Yes, there were examples on the web, most of them were incoherent, and most of the rest of them were wrong.

What happens is that all the authors assume you know something about what your doing.  Well, that wasn't the case.  They also assume you know about permissions, file locations and such things for ALL the variations of unix out there.  That wasn't the case either.  They try desperately to give enough detail, but too often, leave something crucial out that kills the effort.  To add another level of complication, the unix variants change pretty rapidly, so if you hit a blog post over a few months old, be wary of it; it may not work anymore.  If you don't give up though, you'll find some lonely little post in an obscure forum that has it all reduced down to a simple one line command that actually works.

So, after I got enough stuff installed to actually write code, I had to learn enough php to do something.  I put together a tiny bit of code to open the database and immediately had my first failure.  Did you know that a php module absolutely has to have a file name that ends with .php or it won't work?  I do ... now.  After chasing down that problem, I started adding lines and fixing misunderstandings and stepped through about 8 hours of failure until I actually got up a web page with data I had taken from my thermostats in python, put in a database using sql, retrieved in php, and put up on a web page in html.

It may not look like much, but I'm sure proud of it:

The code to get this out of the data base I described in the previous post looks like this:

The php Module

<?php
# This is about the minimal debugging
# I could find that was easy to use
ini_set('display_errors', 'On');
error_reporting(E_ALL|E_STRICT);

# This is the database open call, it returns a
# database object that has to be use going forward
$db = new SQLite3('/home/pi/database/desert-home');

# you do a query statment that return a strange
# SQLite3Result object that you have to use
# in the fetch statement below
$result = ($db->query('SELECT * FROM thermostats;'));
#print_r($result); # I wanted to see what it actually was

# The fetch call will return a boolean False if it hits
# the end, so why not use it in a while loop?
#
# The fetchArray() call can return an 'associated' array
# that actually means give you back an array of ordered
# pairs with name, value.  This is cool because it means
# I can access the various values by name. Each call to
# fetchArray return one row from the thermostats table
while ($res = $result->fetchArray(SQLITE3_ASSOC)){
        #var_dump($res);
        print ("<strong>" . $res["location"] ." thermostat,</strong><br />");
        print ("Currently: " . $res["status"] . " <br \>");
        print ("Temperature: " . $res["temp-reading"] . " <br \>");
        print ("Settings are: <br \>");
        print ("Mode: " . $res["s-mode"] . " <br \>");
        print ("Temperature: " . $res["s-temp"] . " <br \>");
        print ("Fan: " . $res["s-fan"] . " <br \>");
        print ("<br>");
}
$db->close(); # I opened it, I should close it
?>

Php is an odd language, but it has some real strengths.  At some point I may post about the ones that I noticed and fell in love with, but there are several thousand articles out there that describe its strengths and weaknesses so I'd just be adding chaff if I went into it too much.

So, now I have one device being read, recorded, and monitored.  There are a lot more devices, and I still have to think about controlling them.

I'm starting to understand why more people aren't doing things like this around their own home.