How to Send Data from Arduino to MySQL Database

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 :

	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 :

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 :

…
      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 :

https://github.com/ChuckBell/MySQL_Connector_Arduino

This is example code how to issue a SELECT query and how to read columns and rows :

#include 
#include 
#include 

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.

This Article Has Been Shared 3127 Times!

Facebook Twitter Google+ Pinterest