{"id":26614,"date":"2024-06-27T13:55:27","date_gmt":"2024-06-27T06:55:27","guid":{"rendered":"https:\/\/tom.ji42.com\/?p=26614"},"modified":"2024-06-27T13:55:27","modified_gmt":"2024-06-27T06:55:27","slug":"sending-data-from-the-esp8266-to-mysql-directly","status":"publish","type":"post","link":"https:\/\/tom.tomwork.net\/?p=26614","title":{"rendered":"Sending data from the ESP8266 to MySQL directly"},"content":{"rendered":"

Introduction<\/h2>\n

The ESP8266 is a popular programmable microcontroller with a lot of features. One of its main features in the ability to directly connect the microcontroller to WiFi. This gives u the ability to control the microcontroller over the internet using http, MQTT and many other ways. But it also gives u the ability to send data to the internet. In this guide i am going to explain to you how you can connect a ESP8266 based controller with an BME280 directly to an MySQL database without any other connections or servers in-between.<\/p>\n

Parts required<\/h2>\n

-NodeMCU (Other ESP8266 based controllers will also work)<\/p>\n

-BME280 sensor (Other sensors will also work but u have to modify the code)<\/p>\n

-Breadboard<\/p>\n

-Jumper wires<\/p>\n

Schematic<\/h2>\n
\"\"<\/figure>\n
\n\n\n\n\n\n\n\n
NodeMCU\/ESP8266<\/strong><\/td>\nBMP280<\/strong><\/td>\n<\/tr>\n
3,3v\/5v<\/td>\nVIN<\/td>\n<\/tr>\n
GND<\/td>\nGND<\/td>\n<\/tr>\n
D1 (SCL)<\/td>\nSCL<\/td>\n<\/tr>\n
D2 (SDA)<\/td>\nSDA<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

Code<\/h2>\n

We\u2019re gonna program the ESP8266 using the Arduino IDE. You need to install the ESP8266 board in to the IDE and know how to program to it. For this you can follow the following tutorial:<\/p>\n

https:\/\/randomnerdtutorials.com\/installing-esp8266-nodemcu-arduino-ide-2-0\/<\/a><\/p>\n

To be able to connect to MySQL and to use the BME280 you need to install some libraries. Follow the next steps to install these libraries:<\/p>\n

-Adafruit BME280 Library (version 2.2.2)<\/p>\n

-MySQL MariaDB_Generic (version 1.7.2)<\/p>\n

Open the Arduino IDE and go to\u00a0Sketch -> Include Library -> Manage Libraries.<\/strong>\u00a0Here search for the above Libraries and install them one by one.<\/p>\n

After installing the required libraries and making sure your Arduino IDE is set-up copy the following code to your Arduino IDE.\u00a0Don\u2019t upload it yet<\/strong>. You first need to make some changes to make it work!<\/p>\n

\n
\n
\n
\n
\/*\r\n * ioTechProjects.com 2022\r\n * \r\n * ESP8266 to MySQL tutorial\r\n * \r\n * u are free to edit this code\r\n *\/<\/span>\r\n \r\n#include <Wire.h><\/span>\r\n#include <SPI.h><\/span>\r\n#include <MySQL_Generic.h><\/span>\r\n#include <Adafruit_Sensor.h><\/span>\r\n#include <Adafruit_BME280.h><\/span>\r\n\r\n\/\/ Defining BME280 sensor<\/span>\r\n#<\/span>define<\/span> SEALEVELPRESSURE_HPA (<\/span>1013.25<\/span>)<\/span>\r\nAdafruit_BME280<\/span> bme;<\/span> \/\/ I2C<\/span>\r\n\r\n\/\/ MySQL Debug Level from 0 to 4<\/span>\r\n#define<\/span> _MYSQL_LOGLEVEL_      1<\/span>\r\n#define<\/span> MYSQL_DEBUG_PORT      Serial<\/span>\r\n\r\n\/\/ Network Info<\/span>\r\nchar<\/span> ssid[<\/span>]<\/span> =<\/span> \"WIFI_SSID\"<\/span>;<\/span>\r\nchar<\/span> pass[<\/span>]<\/span> =<\/span> \"WIFI_PASSWORD\"<\/span>;<\/span>\r\n\r\n\/\/ Database Adress<\/span>\r\nchar<\/span> server[<\/span>]<\/span> =<\/span> \"HOSTNAME OR IP\"<\/span>;<\/span>\r\nuint16_t<\/span> server_port =<\/span> PORT;<\/span>\r\n\r\n\/\/ Database Account<\/span>\r\nchar<\/span> user[<\/span>]<\/span>         =<\/span> \"DATABASE_NAME\"<\/span>;<\/span>\r\nchar<\/span> password[<\/span>]<\/span>     =<\/span> \"DATABASE_PASSWORD\"<\/span>;<\/span>\r\n\r\n\/\/Database Name<\/span>\r\nchar<\/span> database[<\/span>]<\/span> =<\/span> \"DATABASE_NAME\"<\/span>;<\/span>\r\nchar<\/span> table[<\/span>]<\/span>    =<\/span> \"DATABASE_TABLE_NAME\"<\/span>;<\/span>\r\n\r\n\/\/ Default insert data values<\/span>\r\ndouble<\/span><\/span> temperature =<\/span> 0<\/span>;<\/span>\r\ndouble<\/span><\/span> humidity =<\/span> 0<\/span>;<\/span>\r\ndouble<\/span><\/span> pressure =<\/span> 0<\/span>;<\/span>\r\n\r\n\/\/ MySQL something<\/span>\r\nMySQL_Connection<\/span> conn<\/span>(<\/span>(<\/span>Client *<\/span>)<\/span>&<\/span>client)<\/span>;<\/span>\r\nMySQL_Query *<\/span>query_mem;<\/span>\r\n\r\nvoid<\/span><\/span> setup<\/span>(<\/span>)<\/span>\r\n{<\/span>\r\n  \/\/ Initialize status LED<\/span>\r\n  pinMode<\/span>(<\/span>LED_BUILTIN,<\/span> OUTPUT)<\/span>;<\/span>\r\n  \r\n  \/\/ Setup Serial<\/span>\r\n  Serial.<\/span>begin<\/span>(<\/span>115200<\/span>)<\/span>;<\/span>\r\n\r\n  \/\/ Begin WiFi section<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\"================================================\"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>String<\/span>(<\/span>\"Connecting to \"<\/span>)<\/span> +<\/span> ssid)<\/span>;<\/span>\r\n  WiFi.<\/span>begin<\/span>(<\/span>ssid,<\/span> pass)<\/span>;<\/span>\r\n  while<\/span> (<\/span>WiFi.<\/span>status<\/span>(<\/span>)<\/span> !=<\/span> WL_CONNECTED)<\/span>\r\n  {<\/span>\r\n    delay<\/span>(<\/span>500<\/span>)<\/span>;<\/span>\r\n    Serial.<\/span>print<\/span>(<\/span>\".\"<\/span>)<\/span>;<\/span>\r\n  }<\/span>\r\n\r\n  \/\/ Print out info about the WiFi connection<\/span>\r\n  Serial.<\/span>print<\/span>(<\/span>\"Connected to network. My IP address is: \"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>WiFi.<\/span>localIP<\/span>(<\/span>)<\/span>)<\/span>;<\/span>\r\n\r\n  \/\/ Test MySQL connection<\/span>\r\n  Serial.<\/span>print<\/span>(<\/span>\"Connecting to SQL Server @ \"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>print<\/span>(<\/span>server)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>String<\/span>(<\/span>\", Port = \"<\/span>)<\/span> +<\/span> server_port)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>String<\/span>(<\/span>\"User = \"<\/span>)<\/span> +<\/span> user +<\/span> String<\/span>(<\/span>\", PW = \"<\/span>)<\/span> +<\/span> password +<\/span> String<\/span>(<\/span>\", DB = \"<\/span>)<\/span> +<\/span> database)<\/span>;<\/span>\r\n\r\n  \/\/ Testing BME280 Sensor<\/span>\r\n  unsigned<\/span> status;<\/span>\r\n  status =<\/span> bme.<\/span>begin<\/span>(<\/span>0x76<\/span>)<\/span>;<\/span>\r\n  if<\/span> (<\/span>!<\/span>status)<\/span> {<\/span>\r\n    Serial.<\/span>println<\/span>(<\/span>\"Could not find a valid BME280 sensor, check wiring, address, sensor ID!\"<\/span>)<\/span>;<\/span>\r\n    Serial.<\/span>print<\/span>(<\/span>\"SensorID was: 0x\"<\/span>)<\/span>;<\/span> Serial.<\/span>println<\/span>(<\/span>bme.<\/span>sensorID<\/span>(<\/span>)<\/span>,<\/span> 16<\/span>)<\/span>;<\/span>\r\n    Serial.<\/span>print<\/span>(<\/span>\"        ID of 0xFF probably means a bad address, a BMP 180 or BMP 085\\n\"<\/span>)<\/span>;<\/span>\r\n    Serial.<\/span>print<\/span>(<\/span>\"   ID of 0x56-0x58 represents a BMP 280,\\n\"<\/span>)<\/span>;<\/span>\r\n    Serial.<\/span>print<\/span>(<\/span>\"        ID of 0x60 represents a BME 280.\\n\"<\/span>)<\/span>;<\/span>\r\n    Serial.<\/span>print<\/span>(<\/span>\"        ID of 0x61 represents a BME 680.\\n\"<\/span>)<\/span>;<\/span>\r\n    while<\/span> (<\/span>1<\/span>)<\/span> delay<\/span>(<\/span>10<\/span>)<\/span>;<\/span>\r\n  }<\/span>\r\n}<\/span>\r\n\r\n\r\nvoid<\/span><\/span> loop<\/span>(<\/span>)<\/span>\r\n{<\/span>\r\n  \/\/ Turn on status LED<\/span>\r\n  digitalWrite<\/span>(<\/span>LED_BUILTIN,<\/span> LOW)<\/span>;<\/span>\r\n\r\n  \/\/ Measuring data<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\"================================================\"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\"Measuring data...\"<\/span>)<\/span>;<\/span>\r\n  delay<\/span>(<\/span>500<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>)<\/span>;<\/span>\r\n  \r\n  temperature =<\/span> bme.<\/span>readTemperature<\/span>(<\/span>)<\/span>;<\/span>\r\n  pressure =<\/span> bme.<\/span>readPressure<\/span>(<\/span>)<\/span> \/<\/span> 100.0F<\/span>;<\/span>\r\n  humidity =<\/span> bme.<\/span>readHumidity<\/span>(<\/span>)<\/span>;<\/span>\r\n  \r\n  Serial.<\/span>print<\/span>(<\/span>\"Temperature = \"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>print<\/span>(<\/span>temperature)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\" \u00b0C\"<\/span>)<\/span>;<\/span>\r\n\r\n  Serial.<\/span>print<\/span>(<\/span>\"Pressure = \"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>print<\/span>(<\/span>pressure \/<\/span> 100.0F<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\" hPa\"<\/span>)<\/span>;<\/span>\r\n\r\n  Serial.<\/span>print<\/span>(<\/span>\"Humidity = \"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>print<\/span>(<\/span>humidity)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\" %\"<\/span>)<\/span>;<\/span>\r\n\r\n  Serial.<\/span>println<\/span>(<\/span>\"================================================\"<\/span>)<\/span>;<\/span>\r\n\r\n  \/\/ change Insert Query with new data<\/span>\r\n  String<\/span> INSERT_SQL =<\/span> String<\/span>(<\/span>\"INSERT INTO \"<\/span>)<\/span> +<\/span> database +<\/span> \".\"<\/span> +<\/span> table +<\/span> \" (temperature, humidity, pressure) VALUES (\"<\/span> +<\/span> temperature +<\/span> \",\"<\/span> +<\/span> humidity +<\/span> \",\"<\/span> +<\/span> pressure +<\/span> \")\"<\/span>;<\/span>\r\n\r\n  \/\/ Insert data in to database<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\"Connecting to MySQL...\"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>)<\/span>;<\/span>\r\n  if<\/span> (<\/span>conn.<\/span>connectNonBlocking<\/span>(<\/span>server,<\/span> server_port,<\/span> user,<\/span> password)<\/span> !=<\/span> RESULT_FAIL)<\/span>\r\n  {<\/span>\r\n    delay<\/span>(<\/span>500<\/span>)<\/span>;<\/span>\r\n    \/\/ Initiate the query class instance<\/span>\r\n    MySQL_Query<\/span> query_mem =<\/span> MySQL_Query<\/span>(<\/span>&<\/span>conn)<\/span>;<\/span>\r\n\r\n    if<\/span> (<\/span>conn.<\/span>connected<\/span>(<\/span>)<\/span>)<\/span>\r\n    {<\/span>\r\n      Serial.<\/span>println<\/span>(<\/span>INSERT_SQL)<\/span>;<\/span>\r\n\r\n      \/\/ Execute the query<\/span>\r\n      \/\/ KH, check if valid before fetching<\/span>\r\n      if<\/span> (<\/span> !<\/span>query_mem.<\/span>execute<\/span>(<\/span>INSERT_SQL.<\/span>c_str<\/span>(<\/span>)<\/span>)<\/span> )<\/span>\r\n        Serial.<\/span>println<\/span>(<\/span>\"Insert error\"<\/span>)<\/span>;<\/span>\r\n      else<\/span>\r\n        Serial.<\/span>println<\/span>(<\/span>\"Data Inserted.\"<\/span>)<\/span>;<\/span>\r\n    }<\/span>\r\n    else<\/span>\r\n    {<\/span>\r\n      Serial.<\/span>println<\/span>(<\/span>\"Disconnected from Server. Can't insert.\"<\/span>)<\/span>;<\/span>\r\n    }<\/span>\r\n    conn.<\/span>close<\/span>(<\/span>)<\/span>;<\/span>                     \/\/ close the connection<\/span>\r\n  }<\/span>\r\n  else<\/span>\r\n  {<\/span>\r\n    Serial.<\/span>println<\/span>(<\/span>\"\\nConnect failed. Trying again on next iteration.\"<\/span>)<\/span>;<\/span>\r\n  }<\/span>\r\n\r\n  \/\/ Turn off status LED<\/span>\r\n  digitalWrite<\/span>(<\/span>LED_BUILTIN,<\/span> HIGH)<\/span>;<\/span>\r\n\r\n  \/\/ Sleep till next data measure and insert<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\"================================================\"<\/span>)<\/span>;<\/span>\r\n  Serial.<\/span>println<\/span>(<\/span>\"\\nSleeping...\"<\/span>)<\/span>;<\/span>\r\n  delay<\/span>(<\/span>5000<\/span>)<\/span>;<\/span>\r\n}<\/span><\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
Code changes<\/h2>\n
Now you need to change the following values in the code to your values.<\/p>\n
After u done this you can upload the code. It should now connect to your WiFi network and connect to your database. Now there is only one thing for us to do and set up the right database structure so that our ESP8266 can write data to MySQL.<\/p>\n
\/\/ Network Info<\/span>\r\nchar<\/span> ssid[<\/span>]<\/span> =<\/span> \"WIFI_SSID\"<\/span>;<\/span>\r\nchar<\/span> pass[<\/span>]<\/span> =<\/span> \"WIFI_PASSWORD\"<\/span>;<\/span>\r\n\r\n\/\/ Database Adress<\/span>\r\nchar<\/span> server[<\/span>]<\/span> =<\/span> \"HOSTNAME OR IP\"<\/span>;<\/span>\r\nuint16_t<\/span> server_port =<\/span> PORT;<\/span>\r\n\r\n\/\/ Database Account<\/span>\r\nchar<\/span> user[<\/span>]<\/span>         =<\/span> \"DATABASE_NAME\"<\/span>;<\/span>\r\nchar<\/span> password[<\/span>]<\/span>     =<\/span> \"DATABASE_PASSWORD\"<\/span>;<\/span>\r\n\r\n\/\/Database Name<\/span>\r\nchar<\/span> database[<\/span>]<\/span> =<\/span> \"DATABASE_NAME\"<\/span>;<\/span>\r\nchar<\/span> table[<\/span>]<\/span>    =<\/span> \"DATABASE_TABLE_NAME\"<\/span>;<\/span><\/code><\/pre>\n
Database requirements<\/h2>\n
Our ESP8266 needs a MySQL database to store it\u2019s data. We\u2019re now going to set up the structure of the database. Assuming you already have an MySQL phpmyadmin host or set up your own server.<\/p>\n
If u have not, make sure you have an MySQL phpmyadmin server before you move on. Also make sure\u00a0Legacy Authentication<\/strong>\u00a0Is enabled! Otherwise the code is not gonna work.<\/p>\n
I suggest u use the following tutorial if u gonna host your own:\u00a0https:\/\/www.osradar.com\/how-to-install-phpmyadmin-on-windows-10\/<\/a><\/p>\n
 <\/p>\n
Database structure<\/h3>\n
id -> type: INT -> select A_I (auto increment)<\/p>\n
timestamp -> type: DATETIME -> select standard value: CURRENT_TIMESTAMP<\/p>\n
temperature -> type: DOUBLE<\/p>\n
humidity -> type: DOUBLE<\/p>\n
pressure -> type: DOUBLE<\/p>\n
\"\"<\/figure>\n
Wrapping Up<\/h2>\n
Now power on the board, Everything should now work!<\/p>\n
You now made an ESP8266 based sensor node who directly talks to an MySQL Database! Feel free to leave a comment below when u need extra information or troubleshooting help.<\/p>\n
Here are some demonstration pictures of the project:<\/p>\n
\"\"
The Hardware<\/figcaption><\/figure>\n
\"\"
Data debug in Arduino IDE<\/figcaption><\/figure>\n
\"\"
MySQL database<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
Introduction The ESP8266 is a popular programmable microcontroller with a lot of features. One of its main features in the ability to directly connect the microcontroller to WiFi. This gives u the ability to control the microcontroller over the internet using http, MQTT and many other ways. But it also gives u the ability to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[13],"tags":[],"class_list":["post-26614","post","type-post","status-publish","format-standard","hentry","category-13"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6cOVM-6Vg","_links":{"self":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts\/26614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=26614"}],"version-history":[{"count":2,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts\/26614\/revisions"}],"predecessor-version":[{"id":26727,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts\/26614\/revisions\/26727"}],"wp:attachment":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=26614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=26614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=26614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}