Web-Accessible History From OpenHAB MySQL Persistence Database

My husband has wanted a quick/easy way to see the data stored in OpenHAB’s MySQL persistence database. He didn’t care for the mysql command line client. He didn’t care for PHPMyAdmin either. I’ve suggested the MyODBC client — which allows you to use MySQL databases as an ODBC data source so you can view the data in MS Access, Excel, etc. Nope – he wanted a web site.

So I put together a very quick (and ugly) PHP page that provides a list of all Items. If you click on an item, you can page through the item’s records. The index.php from the page is available here. You need a web server (I am using Apache on Fedora), PHP (I am using 5.6) and MySQLi (php-mysqlnd package).

This is a bit of paranoia on my part, but even on a page that is ONLY available internally … I don’t like to use an account with read/write access to display data. I create a new user and assign read access:

CREATE USER 'YourUserName'@'localhost' IDENTIFIED BY 'P#ssw0rdH3r3';
GRANT SELECT ON openhabdb.* to 'YourUserName'@'localhost';
FLUSH PRIVILEGES;

Then use *that* user in the php code. This example has a web server running on the database server – and you connect to the MySQL server via localhost. If your web server is located on a different host, you’ll need to create and grant ‘YourUserName’@ the web server hostname.

Leave a Reply

Your email address will not be published. Required fields are marked *