Introduction: ESP8266: DHT22 to MYSQL and HighCharts

Picture of ESP8266: DHT22 to MYSQL and HighCharts

I will tell you how I did to get an ESP8266-01 to read data from DHT22 and send it to an MYSQL database then view the data with HighCharts

Features that I will add later is HighStocks

You need below items:

DHT22

ESP8266 ESP-01

ESP8266flasher, also called NODEMCU FIRMWARE PROGRAMMER, attached in Step 1

USB->TTL

LUA Loader, attached in Step 3

If you want to use the sleep function you need to solder before start, see step 2

Links:

GITHUB for NODEMCU

NODEMCU website

HighCharts demo

EDIT 150826: Updated INIT.LUA. added "--" before "end)" line after -- node.dsleep(sleep*60*1000*1000)

EDIT 150924: new ESP8266Flasher version, corrected errors in readdht22.lua, renamed to readdht22_test.lua for testing purpose

EDIT 151005: updated readdht22_test.lua(removed timer) and readdht22.lua(change so timer repeats)

Step 1: Flash ESP8266 With Firmware

Picture of Flash ESP8266 With Firmware

Connect:

ESP8266 -> TTL

VCC -> VCC

CH_PD -> VCC

GND -> GND

GPIO0 -> GND

TX -> RX

RX -> TX

The USB-TTL that I used had the possibility to select power, I used 3.3V

Plug-in USB-TTL to your computer, wait until your PC have installed drivers for USB-TTL

Start ESP8266Flasher.

I used version 1.0.5619.5450 of ESP8266Flasher EDIT 20150924

I used version 0.9.5 20150318 firmware, build-in ESP8266Flasher

Config->INTERNAL://NODEMCU

Advanced->Baudrate 230400, Flash size 4MByte, Flash speed 40MHz, SPI Mode DIO

Select COM-port and press Flash. If the connection is correct you will get a QR-code at bottom left

When it is ready, green check appear in bottom left, close ESP8266Flasher

Step 2: Connect ESP8266 to DHT22

Picture of Connect ESP8266 to DHT22

Unplug USB-TTL, so the power is disconnected, we dont want to damage anything

Disconnect GPIO0 from GND

Connect:

ESP8266 -> DHT22

VCC -> VCC

GPIO2 -> Data

GND -> GND

If you want to use Sleep function you need to solder RESET to leg 8 on ESP8266 chip

Step 3: Upload Test Code to ESP8266

Picture of Upload Test Code to ESP8266

Before we send any data to an MYSQL we must test so that the ESP8266 can read from DHT22

I used LUA Loader version 0.87

Plug-in USB-TTL to your computer

Start LUA Loader

Select COM-port, Settings->Comm Port Settings

Click on Connect, upper right field should be green if you have connection

Click on Restart, middle right side

You should now see some text poping up, if not you have something wrong with the firmware in ESP8266 or the connection is not correct

To upload a file click on Upload file, bottom right

Upload dht22.lua and readdht22_test.lua

dht22.lua: to communication to dht22 sensor

readdht22_test.lua: read data from sensor and print it to the screen in LUA

When you have uploaded those two files select readdht22_test.lua from the yellow section and click dofile

Now you should see two lines

Humidity: xx.x %

Temperature: xx.x deg C

For every press on dofile you should see values from sensor

Step 4: Prepare MYSQL Database

I am using a WAMPServer 2.5 with Apache 2.4.9, PHP 5.5.12 and MYSQL 5.6.17

I don't know so much about PHP and MYSQL so please comment if some information is missing or wrong

Create a user named "user" and the password must be "password", these can be changed later on if wanted

Attached is an export from the database that I am using

Database name is dht22 and datatable name is temphumi

column 1 is date

column 2 is temp

column 3 is humidity

Step 5: Test PHP File

This is my dht22.php, download dht22.txt and rename to dht22.php, it seems to be blocked to download .php files, save it in the root of www on your webserver

change IP adress inside file, now it is 123.123.123.123, change to IP where your webserver is installed

test it in your webserver by typing, e.g. "123.123.123.123/dht22.php?temp=20&humi=30" [ENTER]

open your MYSQL database and now you must see a date, temp and humi values

Step 6: Upload Final Code to ESP8266

change WiFi settings in top of file, SSID, Password and IP

readdht22.lua: read data from sensor and write to MYSQL

if you want to use Sleep function you have to remove -- from init.lua file

--tmr.alarm(1, gsleep*1000, 0, function()

-- print("timer1 trigger dsleep")

-- node.dsleep(sleep*60*1000*1000)

Step 7: VIew Data With HighCharts

Download files. Save .js files to a sub folder named JS and txt files in the folder above JS, rename txt to php

Access temphumi.php with a web browser, if everything is OK you should see data that ES8266 has sent

will be updated soon

Comments

NihalG1 (author)2016-07-05

Can you make a video of this tutorial ...

Thanks in Advance...

YoussefM23 (author)2016-05-15

hi, thanx for this very interesting post :) , I just wonder where is your webserver ? is it the same PC that you're working on , or some external Webserver. I just installed XAMPP in my PC and for the IP that I use to execute php files I use "localhost" instead of "123.123.123.123" that you're using, what do I have to do to work with it... and thanx in advance

NiltonN1 (author)2016-04-22

Hi , I'm having the following problem if use LUALoader to run readdht22_test.lua , it does not return the temperature test and humudidade . I downloaded the Espplorer as recommended by one of the visitors, and send it returns dht22.lua > dofile ( " dht22.lua " ) ; dht22.lua : 12 : table index is nil

DarinM (author)2016-03-29

You might want to update the PHP script, as it uses the long outdated "mysql" functions. Now you are supposed to use "mysqli". Other than that, this is a really well written Instructable.

kubik256 (author)2016-02-17

Finally I get it working... DHT22 is OK :)
I build custom nodeMCU firmware in: http://nodemcu-build.com/
Flashed float version with build in support for DHT modules, after that there is no need to use dht22.lua :) ...and test.lua is this:
---------------------------
pin = 4

status, temp, humi, temp_dec, humi_dec = dht.read(pin)

if status == dht.OK then

print("DHT Temperature:"..temp..";".."Humidity:"..humi)

elseif status == dht.ERROR_CHECKSUM then

print( "DHT Checksum error." )

elseif status == dht.ERROR_TIMEOUT then

print( "DHT timed out." )

end
---------------------------
For uploading LUA files I used ESPlorer at http://esp8266.ru/esplorer/

It's much more user friendly than LuaLoader... and it's much more faster :)

kubik256 (author)2016-02-16

Hi flopp999,


I have some trouble at Step 3. Firmware has been flashed successfully, both LUA files were also uploaded with no problems, but if I press dofile I get only "dofile(readdht22_test.lua)" and date-time but no values.

Is it possible to somehow test DHT22 sensor? Or, write some RAW data from it instead of calculating temp and humi?

Many thanks ;)

kubik256 (author)2016-02-11

For creating MySQL table better use this code:
-------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `dht22` (
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`temp` varchar(255) NOT NULL,

`humi` varchar(255) NOT NULL,

PRIMARY KEY (`dt`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-------------------------------------------------------------------------------------------
Yours exported table will also work, but you have there exported one row of data.
Also very strange stuff is the primary key and never ever use DATE for filed NAME. It is the name of MySQL function and may couse troubles in the future ;)

flopp999 (author)kubik2562016-02-12

Thanks kubik256. As you may understand I am very new to MySQL, very good information.

Do you mean that primary key cannot be "DATE" column or it is the name of column that is strange as primary key?

*filed do you mean field?

kubik256 (author)flopp9992016-02-12

I mean field ;)

A key can be that field, but you should better choose another name and don't use names of MySQL inner functions, coz it can cause some troubles ;)

And in your sql file it is not PRIMARY KEY... but you have there these two lines at the bottom of a table spec:
----------------------------------------------
UNIQUE KEY `datum_2` (`date`),
KEY `datum` (`date`)
----------------------------------------------
But there is no field with name "datum_2" ;)
Better use something like this:

PRIMARY KEY (`dt`)

And also quotes can be omitted if you are using short names with no special chars ;) After that it can look like this:

PRIMARY KEY (dt)

The PRIMARY KEY have to be used, because Unique key columns are allowed to hold NULL values. The values in a PRIMARY KEY column, however, can never be NULL ....and you don't want any NULL value in date-time stamp ;)

Sandbird (author)2015-12-10

hmm why is this guy connecting the last pin for deep sleep ?

http://www.bntdumas.com/2015/07/23/how-to-battery-powered-temperature-and-humidity-sensors/

Sandbird (author)Sandbird2015-12-10

I am an idiot. You are using the 8th pin. I was focusing on the 7. ... Sorry man. Makes sense now :)

flopp999 (author)Sandbird2015-12-10

Hi, you better ask him.

Sandbird (author)flopp9992015-12-10

I am just concerned that maybe there are different versions of this wifi shield, because i've never seen the deep sleep hack done with the 7th pin.

eminrg (author)2015-11-01

I tried everything but the thing does not want to send data to the webserver

when i print the cod in the command line of the esp it works , but when the script runs it it does not

flopp999 (author)eminrg2015-11-16

I dont know what the problem can be. It works for me and others.

Try to empty the ESP and rewrite the code again to ESP

MadisV (author)2015-11-05

how can i change from 6sec update to 1min? is from tmr.alarm(2, 12000, 1, function() sendData() end ) ?

flopp999 (author)MadisV2015-11-16

Please read more about tmr.alarm here

http://www.nodemcu.com/docs/timer-module/

12000, means 12 seconds

mhtechz (author)2015-07-18

Great article, thanks.

I found the php script not working, i re-wrote it (adding the chipid as an additional argument), find it here: http://pastebin.com/LsAiGsjQ
Moreover, on init.lua you disabled the deep sleep function but the script contain an error (please remove a "end)" from it or re-enable the deep sleep function. Even correcting this error the script result in a esp8266 starting, sending a single read and then nothing else, probably because you used the deep speep function to "restart" the module after each reading... Can you correct it?

Many thanks again for this guide.

flopp999 (author)mhtechz2015-10-05

Can you please tell me what was not working with the PHP script, I cant find the error.

flopp999 (author)mhtechz2015-08-26

I have now updated the INIT.LUA, thanks.

I will look at the other error.

kd7eir (author)2015-09-26

I made the following change to readdht22.lua to have the readings to update every six seconds, otherwise it only read the dht22 once and then stopped.

original - tmr.alarm(2, 12000, 0, function() sendData() end )

new - tmr.alarm(2, 12000, 1, function() sendData() end )

flopp999 (author)kd7eir2015-10-05

Thanks for seeing this. I have today update the files.

hisolei (author)2015-09-21

Thanks for this very good tutorial!!

But i get a error message, what could be the reason?

ERROR Message:

Executing readdht22.lua

PANIC: unprotected error in call to Lua API (readdht22.lua:23: attempt to call global 'sendData' (a nil value))

$J›C”ý3jÕþ¹RG’AÂNS­Ø¦ÿXHø

NodeMCU 0.9.5 build 20150318 powered by Lua 5.1.4

*** You've got 3 sec to stop timer 0 ***

flopp999 (author)hisolei2015-09-24

I found the error and have now correct it.

Please notice that I have changed name for the files when you test the sensor

Thanks for letting me know about your problem

hisolei (author)flopp9992015-09-27

Thank you very much flopp999 for your efforts- well done!!

BrunoO5 (author)2015-09-06

Hi,

It is given an error :s on step 2, on readdht22.lua file.

Please could you say what's going on?

PANIC: unprotected error in call to Lua API (readdht22.lua:23: attempt to call global 'sendData' (a nil value))

flopp999 (author)BrunoO52015-09-24

I found the error and have now correct it.

Please notice that I have changed name for the files when you test the sensor

Thanks for letting me know about your problem

bluestreak101 (author)2015-06-09

Thanks for uploading this. I'm trying to build something similar and have so far built something that uses an Arduino Pro Mini 3.3v. I take the reading and send it to thingspeak.com, but im having issues with power draw.

Im using a single 18650 3.7v battery along with a charging/protection board. I also have a 1.5w 12v solar panel connected to a buck convertor. The buck convertor is plugged into the charger. It all then passes through a boost convertor to raise the voltage to 5v which is then regulated to the 3.3v the ESP and Arduino need to run. Im certain this is probably the most inefficient way to get this to run so I was looking at alternatives.

I came across your instructable when I was searching for ways to lower my power consumption by eliminating the Arduino aspect and only using the ESP8266.

Now my question for you is on my Arduino board I have to put a resister between the VCC and Data pins on the DHT22 before I can get any response from it. Do you need to do the same thing here?

Do you happen to know a way to regulate 3.7v to 3.3 so I can remove the regulator and boost convertor completely?

flopp999 (author)bluestreak1012015-08-26

No, i dont have to use a resistor for DHT22.

Maybe this can help you to regulate to 3.3? http://www.ebay.com/itm/AMS1117-3-3V-DC-DC-Step-Down-Voltage-Regulator-Adapter-Convertor-AMS1117-3-3-/131588593936?hash=item1ea34a9910

About This Instructable

81,574views

124favorites

License:

More by flopp999:ESP8266: DHT22 to MYSQL and HighCharts
Add instructable to: