How to save DHT11 Temperature Data into Database with Wemos D1 Mini

Hello friends. How are you?. Today we are going to learn something amazing. In my previous tutorial, you learned that How to get temperature data from DHT11 and show it on the serial monitor, but today we will go ahead and will do something advanced. We will save hardware that we used in our previous tutorial to build a new project. If you don’t visit that page please visit it here:

http://ice786pk.com/?p=540

OK, now we need some more libraries for this tutorial.

1. ESP8266HTTPClient.h

2. ESP8266WiFi.h

3. SimpleDHT.h

4. DHT.h

There are libraries that you need for this project. You also need some PHP files that will responsible for adding and fetching data into the database. First of all, I am giving you code for Arduino.

Code for Arduino

//include libraries
#include <ESP8266HTTPClient.h>
#include <ESP8266WiFi.h> 
#include <SimpleDHT.h>
#include <dht.h>
#define dht_apin D2 // Analog Pin sensor is connected to
 
dht DHT;
float get_temperature,get_humidity;
//Access point credentials
const char* ssid = "Password lyna hay";
const char* pwd = "Google@007";
const char* host = "http://192.168.8.103";
String get_host = "http://192.168.8.103";
int sensor;
float temperature ,refvoltage;
int temp;
int humd;
 byte temperat = 0;
  byte humidity = 0;
int ledPin = D2; //connected to pin 7 in nodemcu 1.0 esp-12e module
WiFiServer server(80);  // open port 80 for server connection

void setup() 
{
  Serial.begin(115200); //initialise the serial communication
  delay(20);



  int pinDHT11 = D2;
SimpleDHT11 dht11;
  // read without samples.
 
  int err = SimpleDHTErrSuccess;
  if ((err = dht11.read(pinDHT11, &temperat, &humidity, NULL)) != SimpleDHTErrSuccess) {
 
    return;
  }
    
  // DHT11 sampling rate is 1HZ.
  
  //defining the pins, i/p and o/p
  pinMode(ledPin, OUTPUT);
  pinMode(A0, INPUT);
  digitalWrite(ledPin, LOW);

   refvoltage = 2.25;   //reference voltage for temperature calculation
        
  //connecting to wifi
  Serial.println();
  Serial.println();
  Serial.print("Connecting to wifi ");
  Serial.println(ssid);

  WiFi.begin(ssid, pwd);
  
  //attempt to connect to wifi
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(2500);
Serial.print("."); //progress with dots until the wifi is connected
  }
    Serial.println("");

    //while connected print this
    Serial.println("Wifi connected");

    //starting the server
    server.begin();
    Serial.println("Server started.");

    //get the ip address and print it
    Serial.print("This is your ip address: ");
    Serial.print("http://");
    Serial.print(WiFi.localIP());
    Serial.println("/");  
}

void loop()
{
     WiFiClient client = server.available();
    if (!client)
    {
      return;
    }
    
    //wait for the data to be sent from client
    Serial.println("New client connection");
    while(!client.available())
    {
      delay(1);
    }
  DHT.read11(dht_apin);
   
get_temperature =   DHT.temperature;

get_humidity    =   DHT.humidity;
    //Read the first line of the request
    String request = client.readStringUntil('\r');
    Serial.println(request);
    client.flush();
    
   int value = LOW; // set the thermometer off initially
   sensor = analogRead(A0);
   temperature = (refvoltage * sensor * 100) / 1023; //converting degree to celcius
   
    //check the index of the browser and act accordingly 
    if (request.indexOf("/Thermo=ON") != -1) 
    {
        digitalWrite(ledPin, HIGH);
        value = HIGH;
        sensor = analogRead(A0);
        temperature = (refvoltage * sensor * 100) / 1023; //converting degree to celcius
       
        //save the data to mysql, access the php file to write
        HTTPClient http;
        String url = get_host+"/temperature/add.php?temp="+String(get_temperature)+"&humidity="+String(get_humidity);
        Serial.println(url);     
        http.begin(url);
       
        //GET method
        int httpCode = http.GET();
        if(httpCode > 0)
        {
          Serial.printf("[HTTP] GET...code: %d\n", httpCode);
          if(httpCode == HTTP_CODE_OK)
          {
              String payload = http.getString();
              Serial.println(payload);
          }
       }
       else
       {
            Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str());
       }
          http.end();
          delay(3000);
   }

    if (request.indexOf("/Thermo=OFF") != -1)
    {
      digitalWrite(ledPin, LOW);
      value = LOW;
    } 
      temp =(int)temperat; 
    
      //Return the response
      client.println("HTTP/1.1 200 OK");
      client.println("Content-Type: text/html");

      if (request.indexOf("/AlwaysOn") != -1) 
      {
        digitalWrite(ledPin, HIGH);
        value = HIGH;
        sensor = analogRead(A0);
        temperature = (refvoltage * sensor * 100) / 1023; //converting degree to celcius
        temp = (int)temperat; 
        client.println("Refresh: 5");
      }

      client.println("");
      client.println("<!DOCTYPE HTML>");
      client.println("<html>");
      client.println("<body style=background-color:skyblue> </body>");
      client.println("<style> h1 {text-align: center}</style>");
      client.println("<style> h3 {text-align: center}</style>");
      client.println("<style> b {text-align: center}</style>");
      client.println("<style> p {text-align: center}</style>");
      client.println("<head><style>div.relative{position:relative; left:200px; height:20px; width:350px; border:2px solid #73AD21;}</style></head>");
      client.println();
    
      //client.println("<br>");
      client.println("<br><br><h1>IOT Final Project: THERMOMETER</h1><br><br>");
      //client.println("<h3>Probable LM35 analog reading: <h3>");
      //client.print(sensor); //analog reading of the sensor
      //client.print("<br>");
      
      client.print("<h3>Thermometer's current status: </h3>");
      if(request.indexOf("/AlwaysOn") != -1)
      {
        client.print("<h3>Always On<h3>");
        client.println("<h3>Current Temperature: <h3>");
        client.print(get_temperature);
        client.print("<b> deg C.</b>");
                  client.println("<h3>Last measured Humidity: <h3>"); //print the last value of temp
          client.print(get_humidity);

        //save the data to mysql, with http client
      HTTPClient http;
      String url = get_host+"/temperature/add.php?temp="+String(get_temperature)+"&humidity="+String(get_humidity);
      Serial.println(url);     
      http.begin(url);
  /* client.print(String("GET ") + url + " HTTP/1.1\r\n" +
               "Host: " + host + "\r\n" + 
               "Connection: close\r\n\r\n");*/
      //using GET method to write to sql
      int httpCode = http.GET();
      if(httpCode > 0)
      {
          Serial.printf("[HTTP] GET...code: %d\n", httpCode);
            if(httpCode == HTTP_CODE_OK)
            {
              String payload = http.getString();
              Serial.println(payload);
            }
      }
         else
         {
            Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str());
         }
          http.end();
       //delay(3000);
    }
        
     else if(value == HIGH)
     {
          client.print("<h3>On Once<h3>");
          //client.println("<br>");
          client.println("<h3>Current Temperature: <h3>");
          client.print(get_temperature);
          client.print("<b> deg C.</b>");
          //delay(100);
      }
      else if(value == LOW)
      {
          client.print("<h3>Off<h3>");
         // client.println("<br>"); 
          client.println("<h3>Last measured temperature: <h3>"); //print the last value of temp
          client.print(get_temperature);
          client.print("<b> deg C.</b>");
          client.println("<h3>Last measured Humidity: <h3>"); //print the last value of temp
          client.print(get_humidity);
           client.print("<b> %.</b>");

      }
 
      client.println("<br><br>");
      //create buttons
      client.println("<a href=\"/Thermo=ON\"\"><button>Turn On Once </button></a>");
      client.println("<a href=\"/AlwaysOn\"\"><button>Keep It On </button></a>");
      client.println("<a href=\"/Thermo=OFF\"\"><button>Turn Off </button></a><br />");
      
      client.println("<h3>Check the Temperature records by following the below link: <h3>");
      client.println("<p>"+get_host+"/temperature/show.php</p>");
      
      // if there are incoming bytes available from the server, read them and print them:
     if (client.available()) 
     {
       char c = client.read();
       client.print(c);
     }
  
      client.println("</html>");
      
      client.println();
      delay(1);
      Serial.println("Client disconnected!");
      Serial.println("");
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

//include libraries

#include <ESP8266HTTPClient.h>

#include <ESP8266WiFi.h>

#include <SimpleDHT.h>

#include <dht.h>

#define dht_apin D2 // Analog Pin sensor is connected to

dht DHT;

float get_temperature,get_humidity;

//Access point credentials

const char* ssid = "Password lyna hay";

const char* pwd = "Google@007";

const char* host = "http://192.168.8.103";

String get_host = "http://192.168.8.103";

int sensor;

float temperature ,refvoltage;

int temp;

int humd;

byte temperat = 0;

byte humidity = 0;

int ledPin = D2; //connected to pin 7 in nodemcu 1.0 esp-12e module

WiFiServer server(80); // open port 80 for server connection

void setup()

{

Serial.begin(115200); //initialise the serial communication

delay(20);

int pinDHT11 = D2;

SimpleDHT11 dht11;

// read without samples.

int err = SimpleDHTErrSuccess;

if ((err = dht11.read(pinDHT11, &temperat, &humidity, NULL)) != SimpleDHTErrSuccess) {

return;

}

// DHT11 sampling rate is 1HZ.

//defining the pins, i/p and o/p

pinMode(ledPin, OUTPUT);

pinMode(A0, INPUT);

digitalWrite(ledPin, LOW);

refvoltage = 2.25; //reference voltage for temperature calculation

//connecting to wifi

Serial.println();

Serial.print("Connecting to wifi ");

Serial.println(ssid);

WiFi.begin(ssid, pwd);

//attempt to connect to wifi

while (WiFi.status() != WL_CONNECTED)

{

delay(2500);

Serial.print("."); //progress with dots until the wifi is connected

}

Serial.println("");

//while connected print this

Serial.println("Wifi connected");

//starting the server

server.begin();

Serial.println("Server started.");

//get the ip address and print it

Serial.print("This is your ip address: ");

Serial.print("http://");

Serial.print(WiFi.localIP());

Serial.println("/");

}

void loop()

{

WiFiClient client = server.available();

if (!client)

{

return;

}

//wait for the data to be sent from client

Serial.println("New client connection");

while(!client.available())

{

delay(1);

}

DHT.read11(dht_apin);

get_temperature = DHT.temperature;

get_humidity = DHT.humidity;

//Read the first line of the request

String request = client.readStringUntil('\r');

Serial.println(request);

client.flush();

int value = LOW; // set the thermometer off initially

sensor = analogRead(A0);

temperature = (refvoltage * sensor * 100) / 1023; //converting degree to celcius

//check the index of the browser and act accordingly

if (request.indexOf("/Thermo=ON") != -1)

{

digitalWrite(ledPin, HIGH);

value = HIGH;

sensor = analogRead(A0);

temperature = (refvoltage * sensor * 100) / 1023; //converting degree to celcius

//save the data to mysql, access the php file to write

HTTPClient http;

String url = get_host+"/temperature/add.php?temp="+String(get_temperature)+"&humidity="+String(get_humidity);

Serial.println(url);

http.begin(url);

//GET method

int httpCode = http.GET();

if(httpCode > 0)

{

Serial.printf("[HTTP] GET...code: %d\n", httpCode);

if(httpCode == HTTP_CODE_OK)

{

String payload = http.getString();

Serial.println(payload);

}

else

{

Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str());

}

http.end();

delay(3000);

}

if (request.indexOf("/Thermo=OFF") != -1)

{

digitalWrite(ledPin, LOW);

value = LOW;

}

temp =(int)temperat;

//Return the response

client.println("HTTP/1.1 200 OK");

client.println("Content-Type: text/html");

if (request.indexOf("/AlwaysOn") != -1)

{

digitalWrite(ledPin, HIGH);

value = HIGH;

sensor = analogRead(A0);

temperature = (refvoltage * sensor * 100) / 1023; //converting degree to celcius

temp = (int)temperat;

client.println("Refresh: 5");

}

client.println("");

client.println("<!DOCTYPE HTML>");

client.println("<html>");

client.println("<body style=background-color:skyblue> </body>");

client.println("<style> h1 {text-align: center}</style>");

client.println("<style> h3 {text-align: center}</style>");

client.println("<style> b {text-align: center}</style>");

client.println("<style> p {text-align: center}</style>");

client.println("<head><style>div.relative{position:relative; left:200px; height:20px; width:350px; border:2px solid #73AD21;}</style></head>");

client.println();

//client.println("<br>");

client.println("<br><br><h1>IOT Final Project: THERMOMETER</h1><br><br>");

//client.println("<h3>Probable LM35 analog reading: <h3>");

//client.print(sensor); //analog reading of the sensor

//client.print("<br>");

client.print("<h3>Thermometer's current status: </h3>");

if(request.indexOf("/AlwaysOn") != -1)

{

client.print("<h3>Always On<h3>");

client.println("<h3>Current Temperature: <h3>");

client.print(get_temperature);

client.print("<b> deg C.</b>");

client.println("<h3>Last measured Humidity: <h3>"); //print the last value of temp

client.print(get_humidity);

//save the data to mysql, with http client

HTTPClient http;

String url = get_host+"/temperature/add.php?temp="+String(get_temperature)+"&humidity="+String(get_humidity);

Serial.println(url);

http.begin(url);

/* client.print(String("GET ") + url + " HTTP/1.1\r\n" +

"Host: " + host + "\r\n" +

"Connection: close\r\n\r\n");*/

//using GET method to write to sql

int httpCode = http.GET();

if(httpCode > 0)

{

Serial.printf("[HTTP] GET...code: %d\n", httpCode);

if(httpCode == HTTP_CODE_OK)

{

String payload = http.getString();

Serial.println(payload);

}

else

{

Serial.printf("[HTTP] GET... failed, error: %s\n", http.errorToString(httpCode).c_str());

}

http.end();

//delay(3000);

}

else if(value == HIGH)

{

client.print("<h3>On Once<h3>");

//client.println("<br>");

client.println("<h3>Current Temperature: <h3>");

client.print(get_temperature);

client.print("<b> deg C.</b>");

//delay(100);

}

else if(value == LOW)

{

client.print("<h3>Off<h3>");

// client.println("<br>");

client.println("<h3>Last measured temperature: <h3>"); //print the last value of temp

client.print(get_temperature);

client.print("<b> deg C.</b>");

client.println("<h3>Last measured Humidity: <h3>"); //print the last value of temp

client.print(get_humidity);

client.print("<b> %.</b>");

}

client.println("<br><br>");

//create buttons

client.println("<a href=\"/Thermo=ON\"\"><button>Turn On Once </button></a>");

client.println("<a href=\"/AlwaysOn\"\"><button>Keep It On </button></a>");

client.println("<a href=\"/Thermo=OFF\"\"><button>Turn Off </button></a><br />");

client.println("<h3>Check the Temperature records by following the below link: <h3>");

client.println("<p>"+get_host+"/temperature/show.php</p>");

// if there are incoming bytes available from the server, read them and print them:

if (client.available())

{

char c = client.read();

client.print(c);

}

client.println("</html>");

client.println();

delay(1);

Serial.println("Client disconnected!");

Serial.println("");

}

Code it too long, I know, but it has covered everything that you need for this project. In this code you have to change some data.

const char* ssid = “My Internet is not free“;
const char* pwd = “@f2d22“;
const char* host = “http://192.168.8.103“;
String get_host = “http://192.168.8.103“;

You have to change ssid, pwd, host and get_host values that are bold (Host is your localhost IP, for this, you need to install XAMPP or WAMP server).

OK now it is perfect, so let’s move.

Now you need a server that like XAMPP or WAMP and a database. I will not guide you for server installation. You can watch videos on this topic (sorry for that).

I assumed that you have finished installation of XAMPP or WAMP and now you are ready for rest of the work. Now you have to create a database and import below database file into your database:

temps

But remember that unzip this file first and the import. Now create a folder in root of your server (or as you want) and download following three files and copy and paste it into that folder that you have created. I assumed you created a folder named temperature, so put these three files in temperature folder. Files are here:

temperature.zip

Note:- This is zip file and has three files in it, after extracting files put all three files into temperature folder and also change database connection information into inc.inc file included into zip file. Here is also source code if you don’t want to download files:

Code for inc.inc

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "temperature";

// Create connection
$conn = new mysqli($servername, $username,$password, $dbname);
?>

<?php

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "temperature";

// Create connection

$conn = new mysqli($servername, $username,$password, $dbname);

Code for add.php

<?php
include_once('inc.inc');

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
date_default_timezone_set("Asia/Karachi");
$val = $_GET['temp'];
$humd = $_GET['humidity'];
$date = date('Y-m-d');
$time = date('H:i:s');
echo $date;
$sql = "INSERT INTO temps(temp,humidity,dated,time) VALUES ($val,$humd,'$date','$time');";

if ($conn->query($sql) === TRUE) {
    echo "Temperature Saved Successfully!";
} else {
    echo "Error:" . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

<?php

include_once('inc.inc');

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

date_default_timezone_set("Asia/Karachi");

$val = $_GET['temp'];

$humd = $_GET['humidity'];

$date = date('Y-m-d');

$time = date('H:i:s');

echo $date;

$sql = "INSERT INTO temps(temp,humidity,dated,time) VALUES ($val,$humd,'$date','$time');";

if ($conn->query($sql) === TRUE) {

echo "Temperature Saved Successfully!";

} else {

echo "Error:" . $sql . "<br>" . $conn->error;

}

$conn->close();

Code for show.php

<html>
<head>
<meta http-equiv="refresh" content="5">
</head> 
<body>
<?php
include_once('inc.inc');

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM temps ORDER by id DESC LIMIT 50";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
	echo "<table border='1' width='30%'>
    <th>ID</th><th>TEMP(deg C)</th><th>Humidity (%)</th><th>DATE & TIME</th>";
    while($row = $result->fetch_assoc()) {
		echo "<tr>";
		echo "<td>".$row['id']."</td>";
		echo "<td>".$row['temp']."</td>";
		echo "<td>".$row['humidity']."</td>";
		echo "<td>".$row['dated']." | " .$row['time']. "</td>";
		echo "</tr>";
    }
	echo "</table>";
} else {
    echo "0 results";
}
$conn->close();
?>
</body>
</html>

<html>

<head>

</head>

<body>

<?php

include_once('inc.inc');

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "SELECT * FROM temps ORDER by id DESC LIMIT 50";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

// output data of each row

echo "<table border='1' width='30%'>

<th>ID</th><th>TEMP(deg C)</th><th>Humidity (%)</th><th>DATE & TIME</th>";

while($row = $result->fetch_assoc()) {

echo "<tr>";

echo "<td>".$row['id']."</td>";

echo "<td>".$row['temp']."</td>";

echo "<td>".$row['humidity']."</td>";

echo "<td>".$row['dated']." | " .$row['time']. "</td>";

echo "</tr>";

}

echo "</table>";

} else {

echo "0 results";

}

$conn->close();

</body>

</html>

Now upload your project to Arduino and open Serial Monitor of Arduino. After a short time you see some information on the screen in which you can find your temporary IP address of Arduino to show up your desired data. It will also show you the status of your code. You can also visit your show.php file via your Local IP to check database entries.

Thanks for reading this tutorial. I hope you enjoyed this tutorial. If you have any question, please feel free to comment and ask any question. See you in next tutorial.

6 comments

Jay
January 11, 2018 at 5:54 am

Hi, it’s great info.
I tried to follow your example, and my nodemcu with DHT11 is working well and the web shows current temp and humidity data as well. However, those data does not go into mySQL DB.
I tried to change the String URL (->/pi/home/temperature), but not working.
I’m using Raspberry pi3 and put its IP address into the sketch code, and I didn’t change inc.inc file (servername, username, etc.)
Any advices?
Thank you.

Mahdi
September 30, 2018 at 12:53 am

Hi Mohammad

I have a problem

Notice: Undefined index: temp in C:\xampp\htdocs\temperature\add.php on line 9

Notice: Undefined index: humidity in C:\xampp\htdocs\temperature\add.php on line 10
2018-09-30Error:INSERT INTO temps(temp,humidity,dated,time) VALUES (,,’2018-09-30′,’05:49:51′);
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘,’2018-09-30′,’05:49:51′)’ at line 1

Which is to store data in the database

thanks a lot

Mahdi

Bart
December 10, 2018 at 3:57 pm

The measurements stop after a min. or 5. is it possible to make it run continuously without stopping for like a week? also the clock isn’t correct. it is 4 hours to far.

tasha
January 25, 2019 at 8:41 am

Can i get the schematic diagram on how you setup the DHT11 and NodeMcu?