Send Plant Sensor Data to Remote Database using ESP8266

Looking for a way to remotely monitor and analyze your plant's health?

This PHP code allows you to easily collect and send sensor data from your plants with the help of an ESP8266 to a remote database.

The data is then easily accessible and ready for analysis.

Say goodbye to manual data collection and hello to efficient plant monitoring with this simple and effective solution.

Try it out today!

 

1. Create a php file and name it dbconfig.php, open for edit and add below content.

Replace the "Your...." with your data. This file stores the credentials for database connection.

<?php
	$username = "YourDataBaseUserName";
	$password = "YourDataBasePassword";
	$hostname = "YourHostName"; 
	$database = "YourDataBaseName";
?>

2. Create a php file and name it database_connection.php, open for edit and add below content.

In here we will add the php code that allows us to connect to our remote database.

<?php
	require_once 'dbconfig.php';
	$connect = new PDO($dsn, $username, $password);
?>

3. Create a php file and name it addData.php, open for edit and add below content.

In here we will add the php code that is adding the sensor values to our remote database.

<?php
	// Get Data for connection
	require_once 'dbconfig.php';
	// Create connection
	$conn = new mysqli($hostname, $username, $password, $database);
	// Check connection
	$conn->select_db($database) or die( "Unable to select database");
	$query = "INSERT INTO sensorsvalues (Room, Hub, TimeStamp, BatteryLevel, Pressure, Temperature, AirHumidity, Red, Green, Blue, Clear, UV, SoilHumidity) 
	VALUES ('".$_GET["RO"]."', '".$_GET["HB"]."', NOW(), '".$_GET["BL"]."', '".$_GET["P"]."', '".$_GET["T"]."', '".$_GET["AH"]."'
	, '".$_GET["R"]."', '".$_GET["G"]."', '".$_GET["B"]."', '".$_GET["C"]."', '".$_GET["UV"]."', '".$_GET["SH"]."')";

	if (mysqli_query($conn, $query) === TRUE) {
		echo "AddData : New record created successfully";
	} else {
		echo "AddData Error: " . $query . "<br>" . mysqli_error($conn);
	}
	$conn->close();
?>

The database Structure as a starting point for database creation

CREATE TABLE `hubs` (
  `id` int(2) NOT NULL,
  `Name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `hubs` (`id`, `Name`) VALUES
(0, 'Window'),
(1, 'TV'),
(2, 'Window');

CREATE TABLE `rooms` (
  `id` int(2) NOT NULL,
  `Name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `rooms` (`id`, `Name`) VALUES
(0, 'Bedroom'),
(1, 'Hallway'),
(2, 'Bathroom'),
(3, 'Livingroom'),
(4, 'Kitchen'),
(5, 'Washroom'),
(6, 'Storageroom');

CREATE TABLE `sensors` (
  `id` int(2) NOT NULL,
  `Name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `sensors` (`id`, `Name`) VALUES
(0, 'BMP280'),
(1, 'SI7006-A20'),
(2, 'TCS3471'),
(3, 'VEML6070'),
(4, 'Soil');

CREATE TABLE `sensorsvalues` (
  `id` int(6) NOT NULL,
  `Room` int(2) NOT NULL,
  `Hub` int(2) NOT NULL,
  `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `BatteryLevel` float(5,2) NOT NULL,
  `Pressure` float(6,2) NOT NULL,
  `Temperature` float(4,2) NOT NULL,
  `AirHumidity` double(4,2) NOT NULL,
  `Red` int(3) NOT NULL,
  `Green` int(3) NOT NULL,
  `Blue` int(3) NOT NULL,
  `Clear` int(3) NOT NULL,
  `UV` int(3) NOT NULL,
  `SoilHumidity` float(4,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The Web link for data sending will look like

http://www.YourSiteName.com/addData.php?RO=1&HB=2&TS=2019-03-11%2015:07:07&BL=99.99&P=1057.55&T=23.23&AH=55.66&R=125&G=126&B=127&C=131&UV=55&SH=44.99

RO=1 - Room number
HB=2 - Hub number
TS=2019-03-11 2015:07:07 - Time Stamp
BL=99.99 - Battery level
P=1057.55 - Air pressure
T=23.23 - Temperature
AH=55.66 - Air Humidity
R=125 - Light Red Color Level
G=126 - Green Red Color Level
B=127 - Blue Red Color Level
C=131 - Clear Light Level
UV=55 - UV Level
SH=44.99 - Soil Humidity

The Arduino IDE ESP8266 code is posted below.

#include <ESP8266WiFi.h>
#include <ESP8266HTTPClient.h>

const char *ssid = "Your2.4GHzNetworkName";
const char *password = "Your2.4GHzNetworkPassword";
const uint8_t msTime = 20;
//The end marker is not added to buffer
//Blue pill will transmit 34 bytes including markers
const uint8_t dLen = 32;
const uint8_t sMarker = 60;//<
const uint8_t eMarker = 62;//>
//Messages we get from BP
const uint8_t BP_SYNK_1 = 125;//To get from BP when is ready for first synk
const uint8_t BP_SYNK_2 = 126;//To get from BP when is ready for second synk
//Messages we send to BP
const uint8_t ESP_READY = 100;//To send to BP when ESP is ready, first synk
const uint8_t ESP_WAIT= 101;//To send to BP when ESP is ready, second synk
const uint8_t ESP_DATA_I= 102;//To confirm the 32 bytes received
const uint8_t ESP_HTTP_IR = 103;//To send to BP when HTTP request is invalid
const uint8_t ESP_HTTP_RF = 104;//To send to BP when HTTP request failed
const uint8_t ESP_DATA_O= 105;//To send to BP when data is received
const uint8_t ESP_HTTP_OK = 106;//To send to BP when HTTP request is OK

const String myPage = "http://www.YourSiteName.com/addData.php?RO=";

const uint8_t ESP_SHORT = 0;//We are expecting a short message
const uint8_t ESP_LONG = 1;//We are expecting a Long message
uint8_t ESP_EXPECT = 0;//Variable to hold the size of expected message

//array to holde the data received from ESP
uint8_t BP_DATA[dLen];
//variables that will hold the sensors values receive from ESP
uint8_t RO, HB; //2bytes for room and hub
uint16_t R, G, B, C, UV; //10 bytes for integer data Red, Green, Blue, Clear and UltraViolet
float BL, P, T, AH, SH; //16 bytes for float data for BatteryLevel, Temperature, AirHumidity, SoilHumidity

boolean ESP_Reading = false;
uint8_t ESP_Msg = 0;
uint8_t MsgLen = 0;
//uint32_t MsgTime = 0;

void setup(){
	pinMode(LED_BUILTIN, OUTPUT);
	pinMode(0, OUTPUT);
	//Serial.setRxBufferSize(256); //require ESP8266 >= 2.4.0 https://github.com/esp8266/Arduino/releases/tag/2.4.0-rc1
	Serial.begin(115200);
	delay(msTime);
	WiFi.mode(WIFI_STA);
	WiFi.persistent(false);
	WiFi.begin(ssid, password);
	//Serial.println(WiFi.macAddress());
	//MsgTime = millis();
	while (WiFi.status() != WL_CONNECTED) {
		delay(msTime);
	}
	//Serial.print("T=");Serial.println(millis() - MsgTime);
	digitalWrite(LED_BUILTIN, HIGH);
	//Serial.println("Connection established!");
	//Serial.print("IP address:\t");
	//Serial.println(WiFi.localIP().toString()); // Send the IP address of the ESP8266 to the computer
	ClearBuffer();
	//we are expecting short messages
	ESP_EXPECT = ESP_SHORT;
	sendDataToBluePill(ESP_READY);//let Blue pill know that ESP is ready to first synk
	//wait for BluePill to respond
	while(ESP_Msg != BP_SYNK_1){
		CheckBluePillData();
	}
	ClearBuffer();
}

void loop() {
	//get in synk with BP
	while(ESP_Msg != BP_SYNK_2){
	CheckBluePillData();
		//sendDataToBluePill(ESP_WAIT);//let Blue pill know that ESP is ready to get 32 bytes
	}
	ESP_EXPECT = ESP_LONG;

	//wait to get the 32bytes from BP
	while(ESP_Msg != ESP_DATA_I){
	//Check data fron serialport
		CheckBluePillData();
	}
	//turn ON the LED for transmission
	digitalWrite(LED_BUILTIN, LOW); 
	//for(uint8_t i = 0; i < 100; i++){
	//sendDataToBluePill(ESP_DATA_O);
	//}
	//prepare the data to be sent to server
	RO = BP_DATA[0];
	HB = BP_DATA[1];
	R = getInt(2);
	G = getInt(4);
	B = getInt(6);
	C = getInt(8);
	UV = getInt(10);
	BL = getFloat(12);
	P = getFloat(16);
	T = getFloat(20);
	AH = getFloat(24);
	SH = getFloat(28);
	SendData();
	//Turn OFF the LED to signal end transmission
	digitalWrite(LED_BUILTIN, HIGH);
	//Serial.println();
	//Serial.print("T2=");Serial.println(millis() - MsgTime);
	//Serial.println();
	while(1) {
		sendDataToBluePill(ESP_Msg);
	}
}

void sendInterrupt(void){
	digitalWrite(0, HIGH);
	delayMicroseconds(100);
	digitalWrite(0, LOW);
}

void sendDataToBluePill(uint8_t data){
	uint8_t arrBP[3] = {sMarker, data, eMarker};
	Serial.write(arrBP, 3);
	//delay(msTime);
}

void CheckBluePillData() {
	// receive data from BluePill and save it into BP_DATA
	if(Serial.available() > 0) {
		uint8_t DataIn = Serial.read();
		// the order of these IF clauses is significant 
		//Do we have the end marker?
		if (DataIn == eMarker) {
			//we have the end marker,do we expect more data?
			//if we expect 3 bytes then
			if(!ESP_EXPECT) {
				//if we have 1 byte in the message
				if(MsgLen == 1) {
				ESP_Reading = false;
				ESP_Msg = BP_DATA[0];
				//Serial.print("3Bytes in:");
				//Serial.println(millis() - MsgTime);
				}
			} else {
				//if we have 32 bytes in buffer
				if(MsgLen == dLen) {
					ESP_Reading = false;
					ESP_Msg = ESP_DATA_I;
					//Serial.print("32Bytes in:");
					//Serial.println(millis() - MsgTime);
				}
			} 
		}
		//if we are reading
		if(ESP_Reading) {
			BP_DATA[MsgLen] = DataIn;
			MsgLen ++;
		}
		//if we read the start marker and we are not reading
		if ((DataIn == sMarker) && !ESP_Reading) { 
			MsgLen = 0; 
			ESP_Reading = true;
			//MsgTime = millis();
		}
	}
}

void SendData() {
	HTTPClient http;
	String dataline = myPage + String(RO) + "&HB=" + String(HB) + "&TS=" + "&BL=" + String(BL) + "&P=" +String(P)
	+ "&T=" +String(T) + "&AH=" +String(AH) + "&R=" +String(R) + "&G=" +String(G) + "&B=" +String(B)
	+ "&C=" +String(C) + "&UV=" +String(UV) + "&SH=" +String(SH);
	//Serial.println(dataline);
	bool httpResult = http.begin(dataline);
	if(!httpResult) {
		ESP_Msg = ESP_HTTP_IR;
		//Serial.println("Invalid HTTP request:");
		//Serial.println(dataline);
	} else {
		int httpCode = http.GET();
		if (httpCode > 0) { // Request has been made
			//Serial.println("HTTP status: " + String(httpCode) + " Message: ");
			String payload = http.getString();
			ESP_Msg = ESP_HTTP_OK;
			//Serial.println(payload);
		} else { // Request could not be made
			ESP_Msg = ESP_HTTP_RF;
			//Serial.println("HTTP request failed. Error:" + String(http.errorToString(httpCode).c_str()));
		}
	}
	http.end();
	ClearBuffer();
}

//Serial.flush() function does not empty the input buffer. It is only relevant when the Arduino
//is sending data and its purpose is to block the Arduino until all outgoing the data has been sent.
void ClearBuffer(){
	while (Serial.available() > 0) {
		Serial.read();
	}
}

void Blink_Led(void) {
	digitalWrite(LED_BUILTIN, LOW);
	delay (msTime);
	digitalWrite(LED_BUILTIN, HIGH);
	delay (msTime);
}

uint16_t getInt(uint8_t pos){
	uint16_t retVal = ((uint16_t)BP_DATA[pos] << 8); 
	retVal |= BP_DATA[pos + 1];
	return retVal;
}

float getFloat(uint8_t pos){
	int32_t newVal = ((uint32_t)BP_DATA[pos] << 24);
	newVal |= ((uint32_t)BP_DATA[pos + 1] << 16); 
	newVal |= ((uint32_t)BP_DATA[pos + 2] << 8); 
	newVal |= BP_DATA[pos + 3]; 
	return (float)newVal/100.0f;
}

Check this article if you want to learn how to display the data on a web page Display Plant Sensor Data in webpage

Comments powered by CComment

Who’s online

We have 95 guests and no members online