How to Connect NodeMCU ESP8266 to MySQL Database

Introduction: How to Connect NodeMCU ESP8266 to MySQL Database

About: I am a professional blogger from India.

MySQL is a widely used relational database management system (RDBMS) that uses structured query language (SQL). At some point, you may want to upload Arduino/NodeMCU sensor data to the MySQL database. In this Instructable, we will see how to connect NodeMCU ESP8266 to the MySQL database.

Here I am going to use 000webhost to host MySQL database because of simplicity and free availability. However, you can use any platform with LAMP (Linux, Apache, MySQL/MariaDB, PHP) stack installed on it. Even you can use XAMPP to host MySQL database locally on your Windows PC.

In this instructable, I am not going to use any sensor. I will just increment two variables and insert them into the database. However, you can connect any sensor with your board.

Requirements:-

  1. NodeMCU ESP8266 development board
  2. Free version of 000webhost account (or MySQL installed on localhost)
  3. Filezilla FTP client (free version)

Step 1: Create Web Application


  • Navigate to 000webhost.com and login to your account.
  • Locate Create New Site button on the top right corner of the window.
  • Enter desired site name and password and then hit the create button. (Note down site password in a safe place because we are going to use it in upcoming steps).
  • Proceed to Manage Website option.

Step 2: Create MySQL Database

Navigate to Tools >> Database Manager and then create a new database.

After successfully creating the database, proceed to Manage >> PhpMyAdmin.


Step 3: Create MySQL Database Table

  • Locate and click on the database name in the left panel of PhpMyAdmin Window (as shown in screenshot a).
  • Enter table name and number of columns (let it be 5). Then hit the Go button.
  • Create columns (as per schema shown in screenshot b) and then hit save button.

Alternatively, you can create table by running the below command:-

CREATE TABLE `id13263538_sumodb`.`nodemcu_table` ( `id` INT(10) NOT NULL AUTO_INCREMENT , `val` FLOAT(10) NOT NULL , `val2` FLOAT(10) NOT NULL , `date` DATE NOT NULL , `time` TIME NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

Step 4: Download and Edit PHP Files

  • Download dbwrite.php and dbread.php file from Github (or download attached files).
  • Update database details and table name in dbwrite.php and dbread.php (as shown in screenshot).

Step 5: Upload PHP Files to the Server

  • Navigate to Manage Website >> Website Settings >> General.
  • Note down hostname, username, port and password (password is same as site password created in step1).
  • Use these details to connect to the server using Filezilla FTP client (as shown in screenshot).
  • Navigate to public_html folder and upload dbwrite.php and dbread.php files.

Step 6: Edit and Upload Arduino (.ino) File to NodeMCU ESP8266

  • Navigate to Manage Website >> Website Settings >> General and note down Website_Name (site URL).
  • Edit .ino file to replace example.com with the name of your site. Also don't forget to update WiFi SSID and password.
  • Finally, Upload code to NodeMCU.

Step 7: Check Connection to MySQL Database

Once code is uploaded to NodeMCU, it will start sending data to MySQL database.

Visit "example.com/dbread.php" to view database values.

Hope you find this tutorial helpful. Enjoy!

Arduino Contest 2020

Participated in the
Arduino Contest 2020

1 Person Made This Project!

Recommendations

  • First Time Author Contest

    First Time Author Contest
  • Make it Fly Challenge

    Make it Fly Challenge
  • Backyard Contest

    Backyard Contest

12 Comments

0
thesis7080
thesis7080

2 months ago

I received -1 in the serial monitor. last month I have the same project that u made sending data from nodemcu to the 000webhost but now, its not working anymore. so I tried your code but still I get -1 in the serial monitor

0
MdCreater
MdCreater

Question 6 months ago

This is about sending data to mysql db. What about reading column values from mysql db in code. Can we fetch data from mysql db?

0
erkansaglikmain1
erkansaglikmain1

8 months ago

Thank you for your post. But when i tried for my site i took 302 error code from esp8266 and i couldn't access to dbread.php file. How can i fix it ?

0
muhammadamin831996
muhammadamin831996

9 months ago on Step 7

can you help me combine with the water level and pump that you had make it

1
mervimervao
mervimervao

9 months ago

Pas d'erreur mais les informations ne s'affichent pas dans la base de données

0
Rishi Rishi
Rishi Rishi

Reply 9 months ago

Hi

Make sure database details are correct in PHP file.

1
busvenc
busvenc

Question 10 months ago

Very nice project, but i still have error code 424.

0
Rishi Rishi
Rishi Rishi

Answer 10 months ago

Glad you liked it :)

Not sure what may be causing problem.....

Is php file accessible from Chrome browser?

1
busvenc
busvenc

Reply 10 months ago

Hi. Thank you for your answer. I had error in .ino code. Code Is working. Thank you very much. Jan

0
Rishi Rishi
Rishi Rishi

Reply 10 months ago

You're Welcome :)

0
Rishi Rishi
Rishi Rishi

Reply 1 year ago

Thanks!