Published November 27, 2018 © GPL3+

LAN WeatherStation with Web Interface and PHP/MySQL Backend

A weather station with web Interface displaying results and trends over the previous three hours.

IntermediateShowcase (no instructions)10,067

LAN WeatherStation with Web Interface and PHP/MySQL Backend

Things used in this project

Hardware components

Arduino Mega 2560

Kuman K15 Ethernet Shield W5100

SainSmart LCD Module For Arduino 20 X 4

Optional

Robojax BMP280

3.3V-5V 4 Channel Logic Level Converter

DSD Tech DHT22 AM2302 Temperature and Humidity Sensor

Story

This project is a fairly simple weather data gathering and storage program.

Weather data is recorded by the Arduino and sent to the database every 10 minutes by sending a specially crafted GET request to the web server.

Optionally, you can also send output to a 20x4 LCD.

Prerequisites:

You must have configured a web server, database and server side scripting language which can process the name/value pairs in the query string of HTTP GET requests. If you're on Windows, it's pretty easy to get up and running on a typical LAMP/WAMP/WIMP stack but getting all that working together is beyond the scope of this article.

In my case, I've configured a Windows 7 machine to run a bog standard WIMP stack -

Microsoft IIS 7.5 on Windows 7
PHP 7.1.24 - windows.php.net/download/
MySQL Community 8.0.13 - dev.mysql.com/downloads
I'm also using a copy of PHPMyAdmin to help with DB configuration - phpmyadmin.net/
the mysqli PHP extension must be enabled

Important Note:

This project has completely ignored any semblance of being secure (don't put it on the internet as is but it's fine for your LAN) and my database optimization could probably use some work.

Overview:

The focus of this article will be getting data from the sensors into our database and then extracting that data and displaying it in a way that gives us an indication of what's to come.

From this point on, this article assumes that you have a working web server, PHP and MySQL in place.

Database Configuration:

For this project I created a database named weatherdata and a table in that database named master.

We want to record 4 things in addition to a unique identifier for each database entry:

time;
temperature
barometric pressure
humidity

so we need 5 fields that I've named as follows:

uid;
timestamp
temperature
pressure
humidity

This is what it looks like with phpMyAdmin:

table fields setup

Use phpMyAdmin to create the database weatherdata. Skip creating the tables from the interface and use the script below in the MySQL command line client to create the table and fields:

CREATE TABLE master 
( 
    uid INT(11) NOT NULL AUTO_INCREMENT,  
    timestamp DATETIME NOT NULL,  
    temperature DOUBLE(8,4) NOT NULL,  
    pressure DOUBLE(8,4) NOT NULL,  
    humidity DOUBLE(8,4) NOT NULL,  
    PRIMARY KEY (uid)
);

Testing Note:

You can test the setup by manually entering WEBSERVER_IP_ADDRESS/processincoming.php?t=9.30&p=98.80&h=75.70 into the address bar in your browser and hitting enter (change the values if you want). If the server processes it correctly you'll see those values recorded in the database and it's working correctly. If it doesn't work, there's a config or setup error somewhere.

Server-Side Processing

Database input is accomplished by the Arduino sending a GET request to the web server every 10 minutes with the temperature, pressure and humidity values in the query string of the request.

In the Arduino code, the URL string gets built and sent with this:

urlString = "GET /processincoming.php?t=" + temperatureToDB + "&p=" + 
    pressureToDB + "&h=" +  humidityToDB + " HTTP/1.1"; 
sendDataToServer(urlString);

The sendDataToServer() function accepts the full string and sends it to the web server located at 192.168.1.35 on the LAN:

void sendDataToServer(String stringToSend) {    
    //make sure i'm sending what I think I'm sending    
    Serial.println(stringToSend);      
    if (client.connect(server, 80))      
    {         
        client.println(stringToSend);         
        client.println("Host: 192.168.1.35");         
        client.println("Connection: close");         
        client.println();     
    }         
        else     
    {        
        Serial.println("connection failed");     
    }
}

The full request we're sending looks like this:

GET /processincoming.php?t=9.30&p=98.80&h=75.70 HTTP/1.1

It should be obvious but t = the temperature value, p = the pressure value and h = the humidity value

One thing I'll note here is that this page request does not produce any output that would normally be sent to a browser (beyond default response headers). Once the Arduino sends the data it's done until the next cycle and doesn't need to parse a response, or even wait for one.

Tip: Give your web server a static IP address on the LAN. If you rely on DHCP, you may be find your self modifying the Arduino code to account for the host IP changing every time the server restarts.

The code included here (WeatherStation_v1_webclient_no_lcd.ino) compiles to 1304 bytes so is suitable for smaller devices.

Displaying the Data:

The file weather.php is responsible for displaying our weather data. The display page is simple HTML tables generated by the script and doesn't require any other PHP extensions beyond mysqli.

By default, this page displays data up to 3 hours old so our Arduino should have been running for at least that long to record a sufficient amount of data. If you try to run this without sufficient data points in place, you will get array errors.

The output HTML should look something like this after gathering data for at least 3 hours:

displaying weather.php on iPhone

And lastly, the included schematic has an LCD output attached - it can be ignored.

Alternative Option

In this case, don't ignore the LCD, you're going to need it. I'm also including a second Arduino program which incorporates the above with output to a 20x4 LCD.

The full description of the LCD component can be found here: https://create.arduino.cc/projecthub/ragingradish/improved-weatherstation-20x4-18dd89

The LCD part of the program has been updated to include confirmation that ethernet is up and running but is otherwise unchanged.

For sending the data to the server, the appropriate libraries were added and initialized, then a conditional was inserted into the loop:

//send data to server every 10 minutes  
if ((mainLoop == 0) || (mainLoop % 10 == 0))  
{    
    temperatureToDB = currentTemp;    
    pressureToDB = currentPressure;    
    humidityToDB = currentHumid;    
    urlString = "GET /processincoming.php?t=" + temperatureToDB + "&p=" +
       pressureToDB + "&h=" +  humidityToDB + " HTTP/1.1";    
    sendDataToServer(urlString);  
}

The code to run the LCD version is WeatherStation_20x4_v1_webclient.ino and compiles to 2090 bytes. You'll need a device with adequate memory to run it.

Code

<?php

//establish a link to the database
$link = mysqli_connect("127.0.0.1", "username", "password", "weatherdata");

//grab the values sent in the GET request
$temp = round($_GET['t'], 1);
$press = round($_GET['p'], 2);
$hum =  round($_GET['h'], 1);


//this is the query string to send to mysql.  the now() function will record the system datetime in the timestamp field
//a sensible person would be sanitizing his or her inputs before doing this
$query = "INSERT INTO master (timestamp, temperature, pressure, humidity) VALUES (NOW(), '$temp', '$press', '$hum')";

//execute the query/commit to the db
$result = mysqli_query($link, $query);

//close the connection
mysqli_close($link);

?>

#include <Wire.h>
#include <dht.h>
#include "i2c.h"
#include "i2c_BMP280.h"
#include <SPI.h>
#include <Ethernet.h>

dht DHT;
BMP280 bmp280;

//specify the temp sensor
#define DHTTYPE DHT22;
#define DHT22_PIN 6

float currentTemp;
float currentHumid;

float baroPressure;
float currentPressure;

//engineeringtoolbox.com/barometers-elevation-compensation-d_1812.html
float altitudeAdjustment = 1;

//ethernet client vars
IPAddress server(192,168,1,35); 
IPAddress ip(192, 168, 1, 36); //in case of DHCP failure
IPAddress myDns(192, 168, 1, 1);
EthernetClient client;

byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
String urlString;
String temperatureToDB;
String pressureToDB;
String humidityToDB;



void setup() {

  Serial.begin(9600); 

  //wait for sensors to be ready
  delay(3000);
  
  //Check if BMP 280 is ready
  if (bmp280.initialize()) 
  {
    Serial.println("Barometer ready");
  }
    else
  {
    Serial.println("Barometer error");
  }

  //check if DHT22 is ready
  int chk = DHT.read22(DHT22_PIN);
  switch (chk)
  {
    case DHTLIB_OK:  
    Serial.println("Temp sensor ready"); 
    break;
    
    case DHTLIB_ERROR_CHECKSUM: 
    Serial.println("Temp checksum error"); 
    break;
    
    case DHTLIB_ERROR_TIMEOUT: 
    Serial.println("Temp timeout"); 
    break;
    
    default: 
    Serial.println("Temp sensor error"); 
    break;
  }

  bmp280.setEnabled(0);
  bmp280.triggerMeasurement();

  DHT.read22(DHT22_PIN);

  if (Ethernet.begin(mac) == 0) 
  {
    Serial.print("Failed to configure Ethernet using DHCP");
    // Check for Ethernet hardware present
    if (Ethernet.hardwareStatus() == EthernetNoHardware) 
    {
      Serial.println("Ethernet shield not found");
      //show failure flag
    }
    
    if (Ethernet.linkStatus() == LinkOFF) 
    {
      Serial.println("Ethernet cable not connected.");
    }
    
    // try to congifure using IP address instead of DHCP:
    Ethernet.begin(mac, ip, myDns);
  } 
    else 
  {
      Serial.print("Got IP via DHCP:  ");
      Serial.println(Ethernet.localIP());
  }

  //wait for sensors again
  delay(3000);
}

void loop() {
  
  bmp280.triggerMeasurement();
  bmp280.getPressure(baroPressure);
  
  DHT.read22(DHT22_PIN);
  currentTemp = DHT.temperature;
  currentHumid = DHT.humidity;

  currentPressure = (baroPressure / 1000) + altitudeAdjustment;  //output kPa

  //build the string to send to the server
  temperatureToDB = currentTemp;
  pressureToDB = currentPressure;
  humidityToDB = currentHumid;

  //send the data
  urlString = "GET /processincoming.php?t=" + temperatureToDB + "&p=" + pressureToDB + "&h=" +  humidityToDB + " HTTP/1.1";
  sendDataToServer(urlString);

  //wait 10 minutes
  for (int i = 0; i < 599; i++)
  {
    delay (1000);
  }
}

void sendDataToServer(String stringToSend)
{
  Serial.println(stringToSend);
  if (client.connect(server, 80)) 
  {
    client.println(stringToSend);
    client.println("Host: 192.168.1.35");
    client.println("Connection: close");
    client.println();
  }
    else
  {
    Serial.println("connection failed");
  }
}

#include <LCD.h>
#include <LiquidCrystal_I2C.h>
//#include <Wire.h>
#include <dht.h>
#include "i2c.h"
#include "i2c_BMP280.h"
#include <SPI.h>
#include <Ethernet.h>

dht DHT;
BMP280 bmp280;

//specify the temp sensor
#define DHTTYPE DHT22;
#define DHT22_PIN 6

#define I2C_ADDR    0x27  // Define I2C Address where the PCF8574A is
#define BACKLIGHT_PIN     3
#define En_pin  2
#define Rw_pin  1
#define Rs_pin  0
#define D4_pin  4
#define D5_pin  5
#define D6_pin  6
#define D7_pin  7

LiquidCrystal_I2C  lcd(I2C_ADDR, En_pin, Rw_pin, Rs_pin, D4_pin, D5_pin, D6_pin, D7_pin);

//total delay = (lcdDelayScreen1 + lcdDelayScreen2 + lcdDelayScreen3) * loopCount (in seconds)
int lcdDelayScreen1 = 44; //seconds displaying primary info
int lcdDelayScreen2 = 8; //seconds displaying secondary info
int lcdDelayScreen3 = 8; //seconds displaying secondary info

//these all have to match
int loopCount = 60;  //total loops 
float dataPointT[60];  //temperature data points
float dataPointB[60];  //pressure data points

int mainLoop;  //primary loop
int loopBaroGraph;  //secondary loop
int loopTempGraph;  //secondary loop

float currentTemp;
float currentHumid;

float baroPressure;
float currentPressure;

float tempDifference = 0;
float tempValueComparative;

int offset;
int mt;
int mp;

float pressDifference = 0;
float pressValueComparative;

//engineeringtoolbox.com/barometers-elevation-compensation-d_1812.html
float altitudeAdjustment = 1;

float minTemp;
float maxTemp;
float minPressure;
float maxPressure;

int rangeLowGraph;
int rangeHighGraph;

float percentOfCol;
float pixelHeightPercent;
int pixelHeight;

bool firstLoopComplete = false;

//ethernet client vars
IPAddress server(192,168,1,35); 
IPAddress ip(192, 168, 1, 250);
IPAddress myDns(192, 168, 1, 1);
EthernetClient client;
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
String urlString;
String temperatureToDB;
String pressureToDB;
String humidityToDB;


//define characters for temperature indicators
byte arrowUp[8] = {
  B00100,
  B01110,
  B10101,
  B00100,
  B00100,
  B00100,
  B00100,
  B00100,
};

byte arrowDown[8] = {
  B00100,
  B00100,
  B00100,
  B00100,
  B00100,
  B10101,
  B01110,
  B00100,
};

byte equalSign[8] = {
  B00100,
  B01110,
  B10101,
  B00100,
  B00100,
  B10101,
  B01110,
  B00100,
};

byte row1[8] = {
  B00000,
  B00000,
  B00000,
  B00000,
  B00000,
  B00000,
  B00000,
  B11111,
};

byte row2[8] = {
  B00000,
  B00000,
  B00000,
  B00000,
  B00000,
  B00000,
  B11111,
  B11111,
};

byte row3[8] = {
  B00000,
  B00000,
  B00000,
  B00000,
  B00000,
  B11111,
  B11111,
  B11111,
};

byte row4[8] = {
  B00000,
  B00000,
  B00000,
  B00000,
  B11111,
  B11111,
  B11111,
  B11111,
};

byte row5[8] = {
  B00000,
  B00000,
  B00000,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
};

byte row6[8] = {
  B00000,
  B00000,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
};

byte row7[8] = {
  B00000,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
};

byte row8[8] = {
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
  B11111,
};

void setup() {

  int setupLoop;
  bool dotPosition = true;

  Serial.begin(9600); 

  lcd.begin (20,4);

  //Switch on the backlight
  lcd.setBacklightPin(BACKLIGHT_PIN,POSITIVE);
  lcd.setBacklight(HIGH);
  lcd.home ();

  //delay a few seconds so the sensors are ready
  for (setupLoop = 1; setupLoop < 8; setupLoop++)
  {
    lcd.clear();
    
    if (dotPosition)
    {
      lcd.print("Initializing .");
      lcd.setCursor(0, 1);
      lcd.print("WeatherStation v.1");
      
    }
      else
    {
      lcd.print("Initializing  .");
      lcd.setCursor(0, 1);
      lcd.print("WeatherStation v.1");
    }

    dotPosition = !dotPosition;
    delay(500);
  }

  //Check if BMP 280 is ready
  lcd.setCursor(0, 2);
  if (bmp280.initialize()) 
  {
    lcd.print("Barometer ready");
  }
    else
  {
    lcd.print("Barometer error");
  }

  lcd.setCursor(0, 3);
  
  //check if DHT22 is ready
  int chk = DHT.read22(DHT22_PIN);
  switch (chk)
  {
    case DHTLIB_OK:  
    lcd.print("Temp sensor ready"); 
    break;
    
    case DHTLIB_ERROR_CHECKSUM: 
    lcd.print("Temp checksum error"); 
    break;
    
    case DHTLIB_ERROR_TIMEOUT: 
    lcd.print("Temp timeout"); 
    break;
    
    default: 
    lcd.print("Temp sensor error"); 
    break;
  }

  //leave info on screen long enough
  delay(2000);
    
  bmp280.setEnabled(0);
  bmp280.triggerMeasurement();

  bmp280.awaitMeasurement();
  bmp280.getPressure(baroPressure);
  DHT.read22(DHT22_PIN);

  //populate baseline datapoints with startup values
  int p;
  for (p = 0; p <= loopCount - 1; p++)
  {
    dataPointT[p] = DHT.temperature;
    dataPointB[p] = (baroPressure / 1000) + altitudeAdjustment;
  }


  lcd.clear();
  if (Ethernet.begin(mac) == 0) 
  {
    lcd.print("Failed to configure Ethernet using DHCP");
    // Check for Ethernet hardware present
    if (Ethernet.hardwareStatus() == EthernetNoHardware) 
    {
      lcd.println("Ethernet shield not found");
      //show failure flag
    }
    
    if (Ethernet.linkStatus() == LinkOFF) 
    {
      lcd.print("Ethernet cable not connected.");
    }
    
    // try to congifure using IP address instead of DHCP:
    Ethernet.begin(mac, ip, myDns);
  } 
    else 
  {
      lcd.print("Got IP via DHCP:");
      lcd.setCursor(0, 1);
      lcd.print(Ethernet.localIP());
  }

  //keep display up 
  delay(2000);
}

void loop() {

  for (mainLoop = 0; mainLoop <= loopCount - 1; mainLoop++)
  {
    bmp280.awaitMeasurement();
    
    DHT.read22(DHT22_PIN);
    currentTemp = DHT.temperature;
    currentHumid = DHT.humidity;

    bmp280.getPressure(baroPressure);
    bmp280.triggerMeasurement();
    currentPressure = (baroPressure / 1000) + altitudeAdjustment;

    //read and store the array value before overwriting it
    tempValueComparative = dataPointT[mainLoop];
    dataPointT[mainLoop] = currentTemp;
    tempDifference = currentTemp - tempValueComparative;

    pressValueComparative = dataPointB[mainLoop];
    dataPointB[mainLoop] = currentPressure;
    pressDifference = currentPressure - pressValueComparative;

    //create lcd chars defined above
    lcd.createChar(0, arrowUp);
    lcd.createChar(1, arrowDown);
    lcd.createChar(2, equalSign);

    //LCD Output begin
    lcd.clear();
    lcd.print(currentTemp, 1);
    lcd.print((char)223);
    lcd.print("C ");
  
    //determine which trend indicator to display
    showTrendIndicator(tempDifference);

    lcd.print(" ");
  
    //then display the temperature diff
    lcd.print(tempDifference);
  
    //move to the next line and display humidity
    lcd.setCursor(0, 1);
    lcd.print(currentPressure, 1);
    lcd.print(" kPa ");

    showTrendIndicator(pressDifference);

    lcd.print(" ");
  
    lcd.print(pressDifference);

    lcd.setCursor(0, 2);
    lcd.print(currentHumid, 1);
    lcd.print(" % Rel Hum");

    lcd.setCursor(0, 3);
    lcd.print("Data age: ");

    //this ensures the 'data age' value displays the max after the first loop completes
    if (!firstLoopComplete)
    {
      if (mainLoop < (loopCount - 1))
      {
        lcd.print(mainLoop);
      }
        else
      {
        lcd.print(loopCount);
        firstLoopComplete = true;
      }
    }
      else
    {
      lcd.print(loopCount);
    }
    
    lcd.print(" m");   

    //send data to server every 10 minutes
    if ((mainLoop == 0) || (mainLoop % 10 == 0))
    {
      temperatureToDB = currentTemp;
      pressureToDB = currentPressure;
      humidityToDB = currentHumid;
      urlString = "GET /processincoming.php?t=" + temperatureToDB + "&p=" + pressureToDB + "&h=" +  humidityToDB + " HTTP/1.1";
      sendDataToServer(urlString);
    }

    //countdown until next screen
    screenWaitRight(lcdDelayScreen1);
    
 //+++++++++++++++Screen 1 End+++++++++++++++

    
 //+++++++++++++++Screen 2 Start+++++++++++++++

    //bar chart characters
    lcd.createChar(0, row1);
    lcd.createChar(1, row2);
    lcd.createChar(2, row3);
    lcd.createChar(3, row4);
    lcd.createChar(4, row5);
    lcd.createChar(5, row6);
    lcd.createChar(6, row7);
    lcd.createChar(7, row8);

    //initialize to something these will never be
    minTemp = 50;
    maxTemp = -50;
    //find min and max temps
    for (mt = 0; mt <= loopCount - 1; mt++)
    {
      //offset value translates a negative result for 'mainLoop - mt' into the appropriate
      //value in the array sequence
      offset = getOffsetValue(mainLoop - mt);
      if (minTemp > dataPointT[offset])
      {
        minTemp = dataPointT[offset];
      }
    }

    for (mt = 0; mt <= loopCount - 1; mt++)
    {
      offset = getOffsetValue(mainLoop - mt);
      if (maxTemp < dataPointT[offset])
      {
        maxTemp = dataPointT[offset];
      }
    }

    //this sets the min/max range that the graph will show
    rangeLowGraph = minTemp - 1.5;
    rangeHighGraph = maxTemp + 1.5;
       
    lcd.clear();
    lcd.setCursor(0, 0);
    lcd.print((char)223);
    lcd.print("C ");
    lcd.setCursor(0, 1);
    lcd.print("H");
    lcd.print(maxTemp, 1);
    lcd.setCursor(0, 2);
    lcd.print("L");
    lcd.print(minTemp, 1);

    for (loopTempGraph = 0; loopTempGraph <= loopCount - 1; loopTempGraph += 4)
    {
      offset = getOffsetValue(mainLoop - loopTempGraph);
      currentTemp = dataPointT[offset];
      //i add 50 so the values never go below zero
      currentTemp = (currentTemp + 50) - (rangeLowGraph + 50);
      percentOfCol = currentTemp / ((rangeHighGraph + 50) - (rangeLowGraph + 50));
      //32 = total pixels vertical
      pixelHeightPercent = 32 * percentOfCol;
      pixelHeight = pixelHeightPercent;
      showGraphColumn(pixelHeight, loopTempGraph / 4);
    }

    screenWaitLeft(lcdDelayScreen2);
    
 //+++++++++++++++Screen 2 End+++++++++++++++


 //+++++++++++++++Screen 3 Start+++++++++++++++
    
    //initialize to something these will never be
    minPressure = 250;
    maxPressure = 75;
    //find min and max pressure
    for (mp = 0; mp <= loopCount - 1; mp++)
    {
      offset = getOffsetValue(mainLoop - mp);
      if (minPressure > dataPointB[offset])
      {
        minPressure = dataPointB[offset];
      }
    }

    for (mp = 0; mp <= loopCount - 1; mp++)
    {
      offset = getOffsetValue(mainLoop - mp);
      if (maxPressure < dataPointB[offset])
      {
        maxPressure = dataPointB[offset];
      }
    }
   
    lcd.clear();
    lcd.setCursor(0, 0);
    lcd.print("kPa");
    lcd.setCursor(0, 1);
    //lcd.print("H");  //doesn't fit on screen :(
    lcd.print(maxPressure, 1);
    lcd.setCursor(0, 2);
    //lcd.print("L"); //doesn't fit on screen :(
    lcd.print(minPressure, 1);

    for (loopBaroGraph = 0; loopBaroGraph <= loopCount - 1; loopBaroGraph += 4)
    {   
      offset = getOffsetValue(mainLoop - loopBaroGraph);     
      currentPressure = dataPointB[offset];
      currentPressure = currentPressure - (minPressure - 1.5);
      percentOfCol = currentPressure / ((maxPressure + 1.5) - (minPressure - 1.5));
      pixelHeightPercent = 32 * percentOfCol;
      pixelHeight = pixelHeightPercent;
      showGraphColumn(pixelHeight, loopBaroGraph / 4);   
    }
    
    screenWaitLeft(lcdDelayScreen3);
  
  } //main for loop end 
}

void sendDataToServer(String stringToSend)
{
  Serial.println(stringToSend);
  if (client.connect(server, 80)) 
  {
    client.println(stringToSend);
    client.println("Host: 192.168.1.35");
    client.println("Connection: close");
    client.println();
  }
    else
  {
    // if you didn't get a connection to the server:
    Serial.println("connection failed");
  }
}

int getOffsetValue(int z)
{
  if (z < 0)
  {
    return z + 60;
  }
    else
  {
    return z;
  }
}

void screenWaitRight(int secondsR)
{
  int a;
  for (a = secondsR; a >= 0; a--)
  {
    lcd.setCursor(18, 3);
    if(a >= 10)
    {
      lcd.print(a);
    }
      else
    {
      lcd.print(" ");
      lcd.print(a);
    }
    delay(1000);
  }
}

void screenWaitLeft(int secondsL)
{
  int b;
  for (b = secondsL; b >= 0; b--)
  {
    lcd.setCursor(0, 3);
    if(b >= 10)
    {
      lcd.print(b);
    }
      else
    {
      lcd.print(b);
      lcd.print(" ");
    }
    delay(1000);
  }
}


void showTrendIndicator(float val)
{
  if (val > 0)
  {
    lcd.write(byte(0));
  }
    else if (val < 0)
  {
    lcd.write(byte(1));
  }
    else
  {  
    lcd.write(byte(2));
  }
}
 

void showGraphColumn(int pixelHeight, int c)
{
  switch (pixelHeight)
  {
    case 1:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(0));
    break;
    
    case 2:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(1));
    break;

    case 3:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(2));
    break;

    case 4:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(3));
    break;

    case 5:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(4));
    break;

    case 6:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(5));
    break;

    case 7:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(6));
    break;

    case 8:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    break;

    case 9:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(0));
    break;

    case 10:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(1));
    break;

    case 11:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(2));
    break;

    case 12:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(3));
    break;

    case 13:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(4));
    break;

    case 14:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(5));
    break;

    case 15:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(6));
    break;

    case 16:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));
    break;

    case 17:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(0));
    break;

    case 18:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(1));
    break;

    case 19:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(2));
    break;

    case 20:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(3));
    break;

    case 21:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(4));
    break;

    case 22:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(5));
    break;

    case 23:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(6));
    break;

    case 24:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));
    break;

    case 25:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(0));
    break;

    case 26:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(1));
    break;

    case 27:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(2));
    break;

    case 28:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(3));
    break;

    case 29:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(4));
    break;

    case 30:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(5));
    break;

    case 31:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(6));
    break;

    case 32:
    lcd.setCursor(c + 5, 3);
    lcd.write(byte(7));
    
    lcd.setCursor(c + 5, 2);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 1);
    lcd.write(byte(7));

    lcd.setCursor(c + 5, 0);
    lcd.write(byte(7));
    break;
  }
}

<?php

$link = mysqli_connect("127.0.0.1", "username", "password", "weatherdata");

//in the query I want to split the contents of the timestamp field and just take the time portion
//$query = "SELECT CONCAT(HOUR(timestamp), ':', MINUTE(timestamp)) as dbTime, temperature, pressure, humidity FROM master ORDER BY uid DESC LIMIT 24";
$query = "SELECT uid, TIME(timestamp) as dbTime, temperature, pressure, humidity FROM master ORDER BY uid DESC LIMIT 24";

//query above is executed
//i'm not doing any error checking'
$result = mysqli_query($link, $query);

//declare arrays
//not really necessary
$timestamps = array();
$temperatures = array();
$pressure = array();
$humidity = array();
$pixelHeights = array();
$pixelHeightsP = array();

//18 cols @ 10 min intervals = 3 hrs, indexed from 0
$columnRange = 17;

//dump the db results into separate arrays so we can use them later 
while ($db = mysqli_fetch_array($result))
{
	//chop off the seconds portion of the time value
  $pieces = explode(':', $db['dbTime']);
	$formattedTime = $pieces[0] . ':' . $pieces[1];
	
  $timestampsTime[] = $formattedTime;
	$temperatures[] = $db['temperature'];
	$pressure[] = $db['pressure'];
	$humidity[] = $db['humidity'];
	$uid[] = $db['uid'];
}

//find the min and max temps in the range and
//define the scale of the graph to display
$mintemp = 50;
$maxtemp = -50;
for ($i = 0; $i <= $columnRange; $i++)
{
	if ($mintemp > $temperatures[$i])
	{
		$mintemp = $temperatures[$i];
		$minTempTime = $timestampsTime[$i];
	} 
	
	if ($maxtemp < $temperatures[$i])
	{
		$maxtemp = $temperatures[$i];
		$maxTempTime = $timestampsTime[$i];
	}
}

$rangeHigh = $maxtemp + 1;
$rangeLow = $mintemp - 1;

//calculate the height for each data point
//I add 50 to keep all values above zero, the difference between amounts remains the same
//The bars are then displayed as 10px wide and x% high using a single pixel as a base
//I got my pixel graphics from this site: 1x1px.me
for ($i = 0; $i <= $columnRange; $i++)
{
	$currentTemp = ($temperatures[$i] + 50) - ($rangeLow + 50);
	$pixelHeights[$i] = round($currentTemp / (($rangeHigh + 50) - ($rangeLow + 50)) * 100);
}


//pressure calculations, same as temperature
$minpress = 150;
$maxpress = 50;
for ($i = 0; $i <= $columnRange; $i++)
{
	if ($minpress > $pressure[$i])
	{
		$minpress = $pressure[$i];
		$minPressTime = $timestampsTime[$i];
	} 
	
	if ($maxpress < $pressure[$i])
	{
		$maxpress = $pressure[$i];
		$maxPressTime = $timestampsTime[$i];
	}
}

$rangeHighP = $maxpress + 1;
$rangeLowP = $minpress - 1;

$pressureChange = $pressure[0] - $pressure[$columnRange];

for ($i = 0; $i <= $columnRange; $i++)
{
	$currentPressure = $pressure[$i] - $rangeLowP;
	$pixelHeightsP[$i] = round($currentPressure / ($rangeHighP - $rangeLowP) * 100);
}

//calculate rates of change for temperature and pressure
$rateOfChangePressure1 = ($pressure[0] - $pressure[5]); 
$rateOfChangePressure3 = ($pressure[0] - $pressure[$columnRange]) / 3; 
$rateOfChangeTemp1 = ($temperatures[0] - $temperatures[5]);

//determines the rising/falling/equality graphic
//graphics came from flaticon.com/authors/elegant-themes
function showArrowGraphic($val) {
  
  if ($val > 0)
  {
    $arrowGraphic = 'up-sign.png';
  }
    elseif ($val < 0)
  {
    $arrowGraphic = 'down-arrow-outline.png';
  }
    else
  {
    $arrowGraphic = 'up-and-down-arrow.png';
  }
  return $arrowGraphic;

}



/*
All calculations are done above so HTML output starts after the closing PHP tag 
From this point on, PHP is interspersed as needed
*/
?>

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>LAN WeatherStation v.1</title>
</head>

<body>
<h1>LAN WeatherStation v.1</h1>
<h2>Current conditions:</h2>

<!--       Summary table            -->

<table>
  <tr>
    <td width='200'><b>Temperature:</b></td><td><?php echo round($temperatures[0], 1); ?> &deg;C</td>
  </tr>
  <tr>
    <td><b>Barometric pressure:</b> </td><td><?php echo round($pressure[0], 2); ?> kPa 
      <img src='<?php echo $arrow = showArrowGraphic($pressureChange); ?>' alt='arrow'></td>
  </tr>
  <tr>
    <td><b>Relative humidity:</b></td><td><?php echo round($humidity[0], 1); ?> %</td>
  </tr>
  <tr>
    <td valign='bottom' height='25'><b>&deg;C rate of change (1 hr)</b></td><td valign='bottom' height='25'><?php echo round($rateOfChangeTemp1, 1); ?> &deg;C/hr 
      <img src='<?php echo $arrow = showArrowGraphic($rateOfChangeTemp1); ?>' alt='arrow'></td>
  </tr>
  <tr>
    <td><b>kPa rate of change (1 hr)</b></td><td><?php echo round($rateOfChangePressure1, 2); ?> kPa/hr 
      <img src='<?php echo $arrow = showArrowGraphic($rateOfChangePressure1); ?>' alt='arrow'></td>
  </tr>
  <tr>
    <td><b>kPa rate of change (3 hrs)</b></td><td><?php echo round($rateOfChangePressure3, 2); ?> kPa/hr 
      <img src='<?php echo $arrow = showArrowGraphic($rateOfChangePressure3); ?>' alt='arrow'></td>
  </tr>
  <tr>
    <td valign='bottom' height='25' colspan='2'>Last updated at <?php echo $timestampsTime[0]; ?></td>
  </tr>
</table>


<br>

<!--       Temperature trend            -->

<table style='border: 1px solid black;'>
  <tr>
    <td colspan='20'><b>Temperature trend (past 3 hours)</b></td>
  </tr>
  <tr>
    <td colspan='20'>&nbsp;</td>
  </tr>
  <tr>
	<td width='150'>
    <b>Current:</b> <?php echo round($temperatures[0], 1); ?> &deg;C <br>
	  <b style='color:red'>High:</b> <?php echo round($maxtemp, 1); ?> &deg;C <br>
	  <b style='color:blue'>Low:</b> <?php echo round($mintemp, 1); ?> &deg;C
	</td>
	<!-- add empty column for spacing -->
	<td width='10'>
	  &nbsp;
	</td>
	
  <?php
    
    //Here we loop over the array of temperature values. 
    //The corresponding values for pixelHeights is at the same index in its array so we stretch our single pixel 
    //graphic to be 10px wide and $pixelHeights[x] high
    //We also determine which column will be high and low for the range and set the colours accordingly
    for ($i = 0; $i <= $columnRange; $i++)
    {
      if ($temperatures[$i] == $maxtemp)
      {
        echo "<td valign='bottom'><img src='red.png' height='" . $pixelHeights[$i] . "' width='10' alt='pixel'></td> \n";
      }
        elseif ($temperatures[$i] == $mintemp)
      {
        echo "<td valign='bottom'><img src='blue.png' height='" . $pixelHeights[$i] . "' width='10' alt='pixel'></td> \n";
      }
        else
      {
        echo "<td valign='bottom'><img src='black.png' height='" . $pixelHeights[$i] . "' width='10' alt='pixel'></td> \n";
      }
    }
  ?>
		
	</tr>
	<tr>
	 <td colspan='2'>&nbsp;</td>
	 <td colspan='9'><img src='up-sign.png' width='12' alt='arrow'>&nbsp;<?php echo $timestampsTime[0]; ?></td>
	 <td colspan='9' align='right'><?php echo $timestampsTime[$columnRange]; ?>&nbsp;<img src='up-sign.png' width='12' alt='arrow'></td>
	</tr>
</table> 


<br>

<!--       Barometric trend            -->

<table style='border: 1px solid black;' >
  <tr>
    <td colspan='20'><b>Barometric pressure trend (past 3 hours)</b></td>
  </tr>
  <tr>
    <td colspan='20'>&nbsp;</td>
  </tr>
  <tr>
  <td width='150'>
	  <b>Current:</b> <?php echo round($pressure[0], 2); ?> kPa <br>
	  <b style='color:red'>High:</b> <?php echo round($maxpress, 2); ?> kPa <br>
	  <b style='color:blue'>Low:</b> <?php echo round($minpress, 2); ?> kPa
	</td>
	<!-- add empty column for spacing -->
  <td width='10'>
	  &nbsp;
	</td>
	
  <?php
    
    //same as for temperature above
    for ($i = 0; $i <= $columnRange; $i++)
    {
      if ($pressure[$i] == $maxpress)
      {
        echo "<td valign='bottom'><img src='red.png' height='" . $pixelHeightsP[$i] . "' width='10' alt='pixel'></td> \n";
      }
        elseif ($pressure[$i] == $minpress)
      {
        echo "<td valign='bottom'><img src='blue.png' height='" . $pixelHeightsP[$i] . "' width='10' alt='pixel'></td> \n";
      }
        else
      {
        echo "<td valign='bottom'><img src='black.png' height='" . $pixelHeightsP[$i] . "' width='10' alt='pixel'></td> \n";
      }
    }
  ?>
		
	</tr>
  <tr>
    <td colspan='2'>&nbsp;</td>
    <td colspan='9'><img src='up-sign.png' width='12' alt='arrow'>&nbsp;<?php echo $timestampsTime[0]; ?></td>
    <td colspan='9' align='right'><?php echo $timestampsTime[$columnRange]; ?>&nbsp;<img src='up-sign.png' width='12' alt='arrow'></td>
	</tr>
</table>

</body>
</html>

LAN WeatherStation with Web Interface and PHP/MySQL Backend

Things used in this project

Hardware components

Story

Prerequisites:

Important Note:

Overview:

Database Configuration:

Server-Side Processing

Displaying the Data:

Alternative Option

Schematics

Weather Station v1

Code

processincoming.php

WeatherStation_v1_webclient_no_lcd.ino

WeatherStation_20x4_v1_webclient.ino

weather.php

Credits

Ross

Comments

Embed the widget on your own site

LAN WeatherStation with Web Interface and PHP/MySQL Backend

LAN WeatherStation with Web Interface and PHP/MySQL Backend

Things used in this project

Hardware components

Story

Prerequisites:

Important Note:

Overview:

Database Configuration:

Server-Side Processing

Displaying the Data:

Alternative Option

Schematics

Weather Station v1

Code

processincoming.php

WeatherStation_v1_webclient_no_lcd.ino

WeatherStation_20x4_v1_webclient.ino

weather.php

Credits

Ross

Comments

Related channels and tags