Because of some reasons (discussed here), the decision was made to build an own database to store sensor data instead of using one at Thingspeak or Pachube.
In order to build an own database, PHP and SQL were learned using online tutorials [http://www.w3schools.com] and a book [Learning SQL, O’Reilly]. PHP is a tool for making dynamic and interactive Web pages while SQL is a language for accessing databases. The database and the tables were configured using phpMyAdmin on a Apache webserver with PHP 5.3 and MySQL 5.5 installed. The code has been gradually built up through trial and error.
Figure 1 is a schematic that explains what is happening on the server. The hub connects to an executable PHP-file on the webserver and sends the sensordata together with the url. When this url is for example /store.php?user=Stijn&value=30, a value of ‘30’ for a user called ‘Stijn’ is stored in the table ‘data’ together with a timestamp. The structure of the table ‘data’ is shown in Figure 2. It contains the columns ‘time’, ‘user’ and ‘value’. This table is used to calculate the average power consumption of every user. The file calc.php does those calculations and is executed every minute with the aid of ‘cron’, a time-based job scheduler on the server. All those averages are stored in different tables (avg1, avg15, …) and are used to calculate the instructions for each competitors display. The hub receives the latest available instructions immediately after sending the sensordata. The maximum delay is one minute.