In our recent past discussions, we have send data from ESP32 Arduino to IBM Watson IoT by either using MQTT or by using HTTP POST. In the same way, we have discussed how with CouchDB we can send data from ESP32 Arduino. Also, we have discussed different types of cloud database for IoT. MySQL is widely used database for PHP driven web applications. We can send data from Arduino, ESP32 to MySQL either by sending a HTTP POST request or by using a connector.
Send Data from Arduino to MySQL Database by HTTP POST
Fundamentally this way involves using a PHP script for MySQL CRUD functions. It is kind of dirty way and of course will not be practical many of the use-cases. However, it is easy even with a little known microcontroller! You need a PHP file for keeping the configuration details, such as :
1 2 3 4 5 | define('DB_SERVER', 'localhost'); define('DB_USER', 'USERNAME'); define('DB_PASSWORD', 'PASSWORD'); define('DB_NAME', 'database_name'); define('TB_ENV', 'table_name'); |
The above file will remain on server. You need another PHP file to handle the incoming data from ESP32 Arduino :
---
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | include('config.php'); $conn = mysqli_connect(DB_SERVER, DB_USER, DB_PASSWORD,DB_NAME) or die("Unable to connect to MySQL"); if (mysqli_real_escape_string($conn,$_POST['temperature']) ==NULL ||mysqli_real_escape_string($conn,$_POST['temperature']) ==NAN){ $temperature="NULL"; }else{ $temperature=mysqli_real_escape_string($conn,$_POST['temperature']); } if (mysqli_real_escape_string($conn,$_POST['humidity']) ==NULL){ $humidity="NULL"; }else{ $humidity=mysqli_real_escape_string($conn,$_POST['humidity']); } if (mysqli_real_escape_string($conn,$_POST['pressure']) ==NULL){ $pressure="NULL"; }else{ $pressure=mysqli_real_escape_string($conn,$_POST['pressure']); } if (mysqli_real_escape_string($conn,$_POST['light']) ==NULL){ $light="NULL"; }else{ $light=mysqli_real_escape_string($conn,$_POST['light']); } $logdate= date("Y-m-d H:i:s"); $insertSQL="INSERT into ".TB_ENV." (logdate,temperature,humidity,pressure,light) values ('".$logdate."',".$temperature.",".$humidity.",".$pressure.",".$light.")"; mysqli_query($conn,$insertSQL) or die("INSERT Query has Failed - ".$insertSQL ); ?> |
Third thing you’ll need is another PHP or HTML file to request data from that post.php by HTTP GET to create graph. Part of the code for ESP32 Arduino will be :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | … HTTPClient http; http.begin("YOUR_SERVER/post.php"); http.addHeader("Content-Type", "application/x-www-form-urlencoded"); int httpResponseCode = http.POST("temperature=" + String(t) +"&humidity=" + String(h)); if (httpResponseCode >0){ //check for a return code - This is more for debugging. String response = http.getString(); Serial.println(httpResponseCode); Serial.println(response); } else{ Serial.print("Error on sending post"); Serial.println(httpResponseCode); } http.end(); … |
Send Data from Arduino to MySQL Database by Using MySQL Connector
For directly writing to MySQL data table from ESP32 Arduino, you’ll need some MySQL connector library, like here is one :
1 | https://github.com/ChuckBell/MySQL_Connector_Arduino |
This is example code how to issue a SELECT query and how to read columns and rows :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | #include <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h> byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress server_addr(10,0,1,35); // IP of the MySQL *server* here char user[] = "root"; // MySQL user login username char password[] = "secret"; // MySQL user login password // Sample query char query[] = "SELECT * FROM world.city LIMIT 12"; EthernetClient client; MySQL_Connection conn((Client *)&client); void setup() { Serial.begin(115200); while (!Serial); // wait for serial port to connect Ethernet.begin(mac_addr); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); } else Serial.println("Connection failed."); } void loop() { delay(2000); Serial.println("\nRunning SELECT and printing results\n"); // Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Execute the query cur_mem->execute(query); // Fetch the columns and print them column_names *cols = cur_mem->get_columns(); for (int f = 0; f < cols->num_fields; f++) { Serial.print(cols->fields[f]->name); if (f < cols->num_fields-1) { Serial.print(", "); } } Serial.println(); // Read the rows and print them row_values *row = NULL; do { row = cur_mem->get_next_row(); if (row != NULL) { for (int f = 0; f < cols->num_fields; f++) { Serial.print(row->values[f]); if (f < cols->num_fields-1) { Serial.print(", "); } } Serial.println(); } } while (row != NULL); // Deleting the cursor also frees up memory used delete cur_mem; } |
Although this way appears easy and advanced, not in all cases we can actually use it.
Tagged With arduino mysql , aurdino mysql , Arduino send data to mysql , how to send arduino data to sql , arduino mysql web server , send data from arduino to mysql , esp32 mysql post send arduino , upload arduino data to db , arduin senddata , arduino sql query