How to Connect NodeMCU ESP8266 to MySQL Database

by Rishi Rishi

44K1632

Intro: How to Connect NodeMCU ESP8266 to MySQL Database

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!

17 Comments

yuminmin494 9 months ago

Why i get this error when upload the code to board?

exit status 1
Compilation error: call to 'HTTPClient::begin' declared with attribute error: obsolete API, use ::begin(WiFiClient, url)

sandikalakual 2 years ago



I got this in serial monitor...what should I do?

thesis7080 3 years 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

MdCreater 3 years 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?

erkansaglikmain1 3 years 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 ?

muhammadamin831996 3 years ago

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

mervimervao 3 years ago

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

Rishi Rishi 3 years ago

Hi

Make sure database details are correct in PHP file.

busvenc 3 years ago

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

Rishi Rishi 3 years ago

Glad you liked it :)

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

Is php file accessible from Chrome browser?

busvenc 3 years ago

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