Inserting Data from an ESP32 to a MySQL database using PHP and Arduino IDE

Hello there!

Welcome to my 4th and last article today! I'll say this one more time: Today I am writing 4 articles. Each about experiments with the ESP32. This one, is the last (finally). All of these experiments (as well as the 2 previous ones I did: this and this one) were held during this awful pandemic. To complete them me and my 2 friends went to several GoogleMeets. Sometimes, my equipment failed sometimes theirs did, so I apologise for the poor documentation job. Alright here we go!

These are the 4 experiments I'm writing about:
1. Plotting Real Time Sensor Readings on a web server from an ESP32
2. Publishing Sensor Readings from an ESP32 to Google Sheets
3. Receiving data on an ESP32 from an HTML form on a web server
4. Inserting and Displaying recorded sensor readings from an ESP to a MySQL database
All of them are based on https://randomnerdtutorials.com.

Enjoy this last project!

The basic idea of this project is to make measurements using a weather sensor on the ESP32 and periodically insert the recorded data to a database where all of the recordings (from several contributors) can be viewed. So, no more local network bounds!

Step 1 - Hardware Requirements

For this project me and my two friends decided to switch back to my BME280 sensor because we had trouble using the DHT11 (again), but on some circumstances actually none really work here. I'll tell you more about it later.

Step 2 - Hosting the PHP Application and MySQL Database

The point of this step is to have a domain name and hosting account where we can store and afterwards visualise sensor readings from the ESP32. It will be able to receive data from anywhere at any time.

this is a high level overview created by randomnerdtutorials:


Next what had to be done was preparing the MySQL Database, this includes:
1. Creating a database and user
2. Naming the database
3. Logging in as the user
4. Creating a SQL table

Thanks to our classmate Hardy Valenthio all of the above was done and he also let us use a domain he owns.

Step 3 - PHP Script - HTTP POST

In this step we want to be able to insert recorded data to the MySQL database. A PHP script will be responsible to receive incoming requests from our ESP32 and then insert it to the database.



Here you can see all the post-esp-data.php files of lots of my classmates. The one named "vcs-post-esp-data.php" is ours.

Here you can find the template to this php file.

Step 4 - PHP Script - Display Database Content

In this step we want to be able to display the database content. We're gonna create a new php file (which you can also see in the picture before) called "esp-data.php".

 Here you can find the template to this php file.

Step 5 - Circuit and Code

5.1 Circuit

The circuit we're using (again) is going tp be the typical BME280 circuit. Remember when I said my BME280 was damaged? Well it still is, but then the DHT11 also decided not to work on this last project. Bummer! But then we tried the BME again (holding the SCL and SDA wirings manually) and magically it worked (well, except for the numbers were a little off).

At this point the measurements were still normal, I ran a normal BME280test and got results like this:

5.2 Code

Moving on to the sketch we'll upload to the ESP32. Here's the original randomnerdtutorials template for this code.

Here is ours. 

We made sure to set the network credentials and change the serverName to Hardy's.


Check out how we named my loaction : " Yumna(Greenland Mansion)"

Step 5 - Observe!

After the code uploads we will get periodically messages that will show us data recorded and the HTTP response we get. If you take a closer look the numbers went really off of what they were supposed to show. We speculate that every time the SDA, SCL communication pins fail to meet (probably caused by my shaky fingers) the numbers went crazy.



However, here we can see that the recordings were successfully inserted to the database, my location was successfully recorded 3 times at this moment:




Here is the display of the database!!






That's all from me today!!! Goodbye for now!