#12 — ESP32 Insert Data into MySQL Database using PHP

Webpage on the next level!

Photo by Luca Bravo on Unsplash

Over the course of this publication, we’ve been making web servers on the local network, which means that it’s accessible only when the device and the ESP32 are connected to the same network. To create something that’s fully functional anywhere we are, we’re going to host the web page and the database on a hosting service. This requires you to have a hosting account that allows you to store sensor readings. Gratefully, a friend of mine Hardy Valenthio has an old domain that he doesn’t use anymore for the class to borrow (yaaaay). And because we’re all staying at our homes (and you should too, by the way 😉), the class figured that it’ll be great if we collect our readings in Hardy’s webpage so we can see the current temperature and pressure in each of our hometowns.

Because Hardy’s already done the hosting part and the making of the database user and password, I’m gonna continue from there. If you want to read more, check out the randomnerdtutorials.com’s tutorial.

(You know what you’re gonna need. The ESP32 board, the BMP180, and their supporting devices. Wire them correctly and you’re good to go.)

1. PHP Script HTTP Post

Basically, the PHP script will be responsible for receiving incoming requests from the ESP32 and inserting the data into a MySQL database. There are two scripts that I’m going to make — I’ll be naming it the 501-esp-data.php and the 501-post-esp-data.php. The 501s are just a sign to differentiate my files and my friends’ files. Head to the File Manager of your chosen hosting service and create those two files on the public_html folder.

Open file manager

Create them files

In the post-esp-data.php, we’re going to create a PHP script to invoke HTTP post and insert the newly added sensor readings into the MySQL table.

In the esp-data.php, we’re going to create a PHP script to display database content to the webpage.

Both the PHP scripts are modified from the randomnerdtutorials.com to suit the need for two display values: temperature and pressure. The BMP180 can’t detect humidity.

Replace the $dbname, $username, and $password with your database credentials.

2. The code

Back to the Arduino IDE, me and my partner, Kevin Cahyadi Giri, modified the code so that it can read temperature and pressure from BMP180 and displays the data correctly on the webpage. Here’s the modified code.

A few things to notice:

• We can set the apiKeyValue variable to a random string. Anyone that knows the API key can publish data to the database.
• The loop() is where we actually make the HTTP POST request every 30 seconds with the latest BMP180 readings.
• Change the sensorName and sensorLocation according to each sensor location. Mine was named Carissa(Bogor) because that’s where my sensor is :)
• Lastly, change the ssid and the password according to your Wi-Fi credentials. Provide the servername with http://<your domain name>/post-esp-data.php

3. :)

I uploaded the code and opened the serial monitor at baud rate 115200. Everytime a data is successfully added to the database, there would be an HTTP Response code: 200 message on the monitor.

Successfully sent

Open the webpage at the servername and look at all the data!

We did it!

Here’s a GIF of what the page looked like when I screenshotted it. It’s so fun to see many different sensor readings from different sensor devices in different areas!

Many different inputs!