Introduction: NODEMCU LUA ESP8266 Connect to MySQL Database

Picture of NODEMCU LUA ESP8266 Connect to MySQL Database

This instructable is not for the faint hearted as it uses XAMPP (Apache, MySQL & PHP), HTML and of course LUA. If you are confident at tackling these, read on!

I use XAMPP as it can be set up on a pen drive or your hard drive and it is configured to run right away. There are other WAMP (for Windows) and LAMP (for Linux) servers out there that will work just as well and if you are really brave, you can set up a server from scratch!

You can get XAMPP here : https://www.apachefriends.org/index.html

TIP: Rename index.php and index.html to something like index1.php and index1.html so that you get a list of the programs in the browser, rather than it jumping to the XAMPP start up screen.

Step 1: Installing and Running XAMPP

Picture of Installing and Running XAMPP

Once you have downloaded XAMPP, follow instructions on setting up and when you are finished you should get start screens like those above. You need to start Apache and MySql for the LUA program to work.

L:\xampp\xampp-control.exe

Change the drive letter (L:) to wherever you have installed XAMPP.

Step 2: MySQL

Picture of MySQL

When you have Apache & MySQL running start the cmd.exe and then type

L: and the RETURN key - or wherever you have installed XAMPP (L is the drive letter assigned to my pen drive yours will probably be different)

then

CD xampp\mysql\bin and the RETURN key.

Start up MySql by typing this at the DOS prompt (as above).

mysql -u root -p

then press RETURN when asked for a password.

Then you can use the scripts below and you should see something like above.

USE test

DROP TABLE IF EXISTS writeread;

CREATE TABLE writeread (logdata datetime,field varchar(20),value bigint(20));

INSERT INTO writeread (logdata, field, value) VALUES ('2017-01-01 06:30:10','24','67');

SELECT * FROM writeread; 

I have used the root directory without a password and the test database to create my table. This is a standard set up for MySQL with no security features enabled.

Step 3: The PHP Files

Picture of The PHP Files

There are 2 files included, 1 to write the data to the database table and 1 to read it back and display it in the browser.

For security reasons, PHP files are not allowed to be uploaded to this site, so when you download reader.txt and writer.txt, rename them to reader.php and writer.php, and load them into the htdocs folder of XAMPP.

You can test if writer.php is working by typing

localhost/writer.php?field=7&value=3    
or     
127.0.0.1/writer.php?field=7&value=3

into a browser and if everything is working you should get an extra entry in the database table.

The reader.php program lists out all the rows of the table into your browser. Run this program by double clicking it in the browser or by typing

localhost/reader.php     
or
127.0.0.1/reader.php

into the address bar of the browser.

Step 4: The LUA File

Picture of The LUA File

The file jlwriter.lua is to be on the ESP8266. I used it on a WeMos version, but there is no reason why it should not work on any ESP8266.

If you are still with me and everything has worked, you should see a screen as above.

TIP: When using MySQL pressing F3 saves you typing in the whole script again.

In the LUA program change (line 29) to the IP address of whatever your PC uses to connect to your router or modem.

conn:connect(80,'192.168.0.10')

Type ipconfig into the Command prompt (cmd.exe) to find your IP address.

Step 5: Editing Code

Picture of Editing Code

I find a good code editor is Notepad++ which can be downloaded here

https://notepad-plus-plus.org/

It can be used with many different programming languages or scripts and it's free.

I have include setup.txt to set up your MySQL table if you want to use PHP to do it instead of the method above.

Again you will need to change the file name to setup.php, then copy it into the htdocs folder.

Beware that running this file will wipe out any previous table and data.

Step 6: Conclusion

This has been a rather complicated instructable, but I did eventually get it to work. On the web, there are a few programs that are similar to this, but they are written for a DHT22 sensor. After trying these programs with limited success, I decided to gather everything I could find about MySQL written in LUA and start from scratch. After a lot of trial and error, I came up with this. I have used Apache, MySQL, PHP and HTML in the past, so had an understanding of how that side works.

These programs just scratch the surface of what you can do when connected to a MySQL database. It enables you to collect data and store it on your own PC rather than some unknown server. I hope there is enough here to give you a taste for what can be done with this powerful combination of programs.

Comments

MarcelS9 (author)2017-01-05

Nice tutorial but

"do not start up any wifi.setmode() or sta.config() etc. as the program may not work."

...is bogus. How do you expect any networking operation to succeed if the device hasn't established a WiFi connection yet? Look at http://nodemcu.readthedocs.io/en/latest/en/upload/... for a recipe how to bootstrap your application.

Also note that wifi.sta.config saves your WiFi config to flash by default: http://nodemcu.readthedocs.io/en/latest/en/modules... Hence, if the device reboots it'll automatically apply the last used config (e.g. connecting to access point).

JohnL142 (author)MarcelS92017-01-05

I hear what you are saying, but it would not work if I did connect first. I could not understand why.

I have now removed the comment as it is casing so much controversy.

MarcelS9 (author)JohnL1422017-01-05

I'm with NodeMCU firmware team and I invite you to continue this at one of the two support channels mentioned at http://nodemcu.readthedocs.io/en/latest/en/support/.

jimlyke (author)2017-01-02

Nice project, thanks for posting. Just wondered what you meant by:

"..do not start up any wifi.setmode() or sta.config() etc. as the program may not work"

How else do you connect the ESP8266??

JohnL142 (author)jimlyke2017-01-03

I have found that it does not work if you do. I spent ages trying to get the lua code to write to the database, because I always started up wifi.setmode() and sta.config() etc. If you get different results or find a better way then please let me know. I am not an expert on lua, but maybe one day...

jimlyke (author)JohnL1422017-01-03

I don't think it has anything to do with Lua. I have had some strange results on a different project of a similar form, using net module calls after using wifi commands. Removing the wifi commands allowed the net calls to work.

About This Instructable

5,128views

28favorites

License:

Bio: I like tinkering with electronics, PCs, ESP8266s and Arduinos. Would like to share what I have done as it may help others getting started or ... More »
More by JohnL142:NODEMCU LUA ESP8266 with MCP23008 8 Bit Port Expander ICNODEMCU LUA ESP8266 With MCP23017 16 Bit Port Expander ICNODEMCU LUA ESP8266 Get Weather for Your City (Updated)
Add instructable to: