Insert Data from ESP32 into Database MySQL Using PHP and Arduino IDE

4 min readMay 7, 2023

— Introduction
This article will demonstrate how to transmit information from a sensor to a MySQL database using PHP. The prior project (a weather station) only presented the most recent sensor readings on the web server, whereas this project allows for the presentation of all sensor data. To accomplish this, we will utilize an HTTP POST request to the PHP script to send the ESP32’s sensor readings to the MySQL Database.

— Tools
Hardware :

Breadboard + WiFi
BMP280
Jump wire
Micro USB wire
Laptop

Software :

XAMPP
Arduino IDE
Visual Studio Code atau IDE lainnya dan pastikan sudah terinstall PHP

— Build Website

Install XAMPP
Open XAMPP
Press Start on Apache and MySQL
Open http://localhost/phpmyadmin/
Setelah itu silahkan buat database dan tabel baru
Setelah berhasil, silahkan kunjungi folder C:\Xampp\htdocs (disesuaikan dengan directory tempat kalian melakukan instalasi XAMPP)
Buatlah sebuah folder dengan nama disesuaikan, dalam kasus ini saya membuat folder bernama ‘databaseemsis’ dan buatlah 3 buah file php dengan nama config.php, connect.php, index.php (perlu diperhatikan bahwasanya file connect.php bisa kalian ubah sesuai preferensi kalian)
Untuk file config.php, silahkan salin code berikut

<?php 

/*
$dbname = 'YOUR_DB_NAME';
$dbuser = 'YOUR_DB_USER';  
$dbpass = 'YOUR_DB_PASSWORD'; 
$dbhost = 'localhost'; 
*/

// Pada kasus ini, konfigurasi yang saya gunakan yaitu :

$dbname = 'databaseemsis';
$dbuser = 'root';  
$dbpass = ''; 
$dbhost = 'localhost'; 


?>

9. Untuk file connect.php, silahkan salin code berikut

<?php 

/*********
  Hafidz Shidqi
  Follow github https://github.com/hafidzyami
*********/

#connect ke sql
require 'config.php';
$db = @mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);

#mengembalikan data berdasarkan query
function showData($query){
    global $db;
    $result = mysqli_query($db, $query);
    $rows = [];
    while($row = mysqli_fetch_assoc($result)){
        $rows[] = $row;
    }
    return $rows;
}

$Data = showdata("SELECT * FROM bmp280hafidz");
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">
</head>
<body>
    <div class="container">

        <h2 class="mt-4 mb-5 text-center">Daftar Data Sensor BMP280</h2>
        <table class="table " >
            <thead>
                <tr class="table-primary table-bordered border-dark">
                    <th>No.</th>
                    <th>temperature (c)</th>
                    <th>pressure (hPa)</th>
                    <th>approx altitude</th>
                    <th>timestamp</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($Data as $datum): ?>
                    <tr class="<?= $datum['id']%2 ==0 ? 'table-info' : '' ?>">
                        <th scope="row"><?= $datum["id"]?></th>
                        <td><?= $datum["temperature"]?></td>
                        <td><?= $datum["pressure"]?></td>
                        <td><?= $datum["altitude"]?></td>
                        <td><?= $datum["timestamp"]?></td>
                    </tr>
                <?php endforeach;?>
            </tbody>
    
        </table>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.11.6/dist/umd/popper.min.js" integrity="sha384-oBqDVmMz9ATKxIep9tiCxS/Z9fNfEXiDAYTujMAeBAsjFuCZSmKbSSUnQlmh/jp3" crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.min.js" integrity="sha384-cuYeSxntonz0PPNlHhBs68uyIAVpIIOZZ5JqeqvYYIcEL727kskC66kF92t6Xl2V" crossorigin="anonymous"></script>
</body>
</html>

— Read and Insert Data

Buatlah rangkaian seperti di gambar di bawah ini:

Buka Arduino IDE kalian, salin dan upload code berikut

/*
  Rui Santos
  Complete project details at Complete project details at https://RandomNerdTutorials.com/esp32-http-get-post-arduino/

  Permission is hereby granted, free of charge, to any person obtaining a copy
  of this software and associated documentation files.

  The above copyright notice and this permission notice shall be included in all
  copies or substantial portions of the Software.
*/

#include <WiFi.h>
#include <HTTPClient.h>
#include <Wire.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BMP280.h>

#define SEALEVELPRESSURE_HPA (1013.25)

Adafruit_BMP280 BMP; // I2C

const char* ssid = "blabla";
const char* password = "blabla";

//Your Domain name with URL path or IP address with path
String serverName = "http://192.168.0.142/esp32database/connect.php";

void setup() {
  Serial.begin(115200); 

  Serial.println(F("BMP280 test"));

  bool status;

  // default settings
  // (you can also pass in a Wire library object like &Wire2)
  status = BMP.begin(0x76);  
  if (!status) {
    Serial.println("Could not find a valid BMP280 sensor, check wiring!");
    while (1);
  }

  Serial.println("-- Default Test --");

  Serial.println();

  WiFi.begin(ssid, password);
  Serial.println("Connecting");
  while(WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.print("Connected to WiFi network with IP Address: ");
  Serial.println(WiFi.localIP());
 
  Serial.println("Timer set to 5 seconds (timerDelay variable), it will take 5 seconds before publishing the first reading.");
}

void loop() {
    //Check WiFi connection status
    if(WiFi.status()== WL_CONNECTED){
      HTTPClient http;

      String serverPath = serverName + "?temperature=" + String(BMP.readTemperature()) + "&pressure=" + String(BMP.readPressure() / 100.0F)
                          + "&altitude=" + String(BMP.readAltitude(SEALEVELPRESSURE_HPA));
      
      // Your Domain name with URL path or IP address with path
      http.begin(serverPath.c_str());
      
      // If you need Node-RED/server authentication, insert user and password below
      //http.setAuthorization("REPLACE_WITH_SERVER_USERNAME", "REPLACE_WITH_SERVER_PASSWORD");
      
      // Send HTTP GET request
      int httpResponseCode = http.GET();
      
      if (httpResponseCode>0) {
        Serial.print("HTTP Response code: ");
        Serial.println(httpResponseCode);
        String payload = http.getString();
        Serial.println(payload);
      }
      else {
        Serial.print("Error code: ");
        Serial.println(httpResponseCode);
      }
      // Free resources
      http.end();
    }
    else {
      Serial.println("WiFi Disconnected");
    }
    delay(3000);
}

Please note that you need to change the code segment const char* ssid = “blabla” and const char* password = “blabla”. Replace the value “blabla” with your SSID name and your SSID password. Also, for the String serverName = “IPAddress/FOLDER/FILE.php” line, replace it with your IP Address followed by the folder name you created in step 6 and the file name you created in step 8 when creating the database and web server.

Next, open the Serial Monitor with 115200 Baud, and pay attention to the message that appears. If the message displayed is like the picture below, then it means that you have successfully sent data to the database.
Please visit http://localhost/phpmyadmin/ and http://localhost/YOUR_FOLDER_NAME/. The folder name can be found in step 6 when creating the database and web server. In my case, I opened the following link http://localhost/esp32database/.

4. Please refresh both pages and the sensor reading data will certainly be updated every 3 seconds on both (you can change the updated data every few seconds in the delay (3000) code snippet in step six above, 3000 indicates 3000 milliseconds = 3 seconds).

That’s all for Project 9. Thank you!

Insert Data from ESP32 into Database MySQL Using PHP and Arduino IDE

— Read and Insert Data

Written by Gevyndo Gunawan