Skip to content Skip to sidebar Skip to footer
Home / I Want to Upload Mysql File to Server and Access on Php

I Want to Upload Mysql File to Server and Access on Php

Post a Comment

In this project you'll build an ESP32 or ESP8266 customer that makes an HTTP POST request to a PHP script to insert information (sensor readings) into a MySQL database.

ESP32 ESP8266 Insert Data into MySQL Database using PHP and Arduino IDE

You'll too have a web page that displays the sensor readings, timestamp and other information from the database. You can visualize your data from anywhere in the world by accessing your own server.

As an example, we'll exist using a BME280 sensor connected to an ESP board. You tin modify the code provided to transport readings from a different sensor or apply multiple boards.

In guild to create build this project, you'll apply these technologies:

  • ESP32 or ESP8266 programmed with Arduino IDE
  • Hosting server and domain proper noun
  • PHP script to insert data into MySQL and display it on a spider web folio
  • MySQL database to store readings

1. Hosting Your PHP Application and MySQL Database

The goal of this project is to have your own domain name and hosting account that allows you to store sensor readings from the ESP32 or ESP8266. You can visualize the readings from anywhere in the world by accessing your own server domain. Here'south a high level overview:

Hosting PHP Application and MySQL Database to post ESP32 or ESP8266 Sensor Readings

I recommend using one of the following hosting services that can handle all the project requirements:

  • Bluehost (user-friendly with cPanel): complimentary domain name when you sign up for the iii-year plan. I recommend choosing the unlimited websites option;
  • Digital Ocean: Linux server that you manage through a command line. I simply recommended this option for advanced users.

Those ii services are the ones that I utilise and personally recommend, but you can apply any other hosting service. Any hosting service that offers PHP and MySQL will work with this tutorial. If you don't take a hosting account, I recommend signing up for Bluehost.

Get Hosting and Domain Name with Bluehost »

When ownership a hosting business relationship, yous'll also have to purchase a domain name. This is what makes this projection interesting: yous'll be able to go your domain proper name (http://instance-domain.com) and see your ESP readings.

If you like our projects, you might consider signing up to ane of the recommended hosting services, because yous'll be supporting our work.

Note: you tin also run a LAMP (Linux, Apache, MySQL, PHP) server on a Raspberry Pi to access data in your local network. However, the purpose of this tutorial is to publish readings in your own domain name that you can access from anywhere in the globe. This allows you to easily access your ESP readings without relying on a tertiary-party IoT platform.

two. Preparing Your MySQL Database

After signing upwards for a hosting account and setting up a domain proper noun, you tin login to your cPanel or like dashboard. After that, follow the next steps to create your database, username, password and SQL table.

Creating a database and user

1. Type "database" in the search bar and select "MySQL Database Wizard".

CPanel select MySQL database wizard to create db

ii. Enter your desired Database name. In my case, the database proper noun is esp_data. Then, press the "Next Step" push button:

ESP32 ESP8266 CPanel Create MySQL Database

Notation: afterwards you'll have to employ the database name with the prefix that your host gives you (my database prefix in the screenshot higher up is blurred). I'll refer to it as example_esp_data from now on.

3. Type your Database username and set a password. You must save all those details, because you'll demand them later to constitute a database connection with your PHP code.

ESP32 ESP8266 CPanel Create MySQL Database User and Password

That's it! Your new database and user were created successfully. Now, salve all your details because you'll need them later:

  • Database name: example_esp_data
  • Username: example_esp_board
  • Password: your countersign

Creating a SQL tabular array

Subsequently creating your database and user, go back to cPanel dashboard and search for "phpMyAdmin".

ESP32 ESP8266 CPanel Open PHPMyAdmin

In the left sidebar, select your database proper noun example_esp_data and open the "SQL" tab.

ESP32 ESP8266 PHPMyAdmin Open Database

Of import: brand sure you've opened the example_esp_data database. Then, click the SQL tab. If you don't follow these verbal steps and run the SQL query, yous might create a table in the wrong database.

Re-create the SQL query in the post-obit snippet:

          CREATE TABLE SensorData (     id INT(6) UNSIGNED AUTO_INCREMENT Chief KEY,     sensor VARCHAR(30) Non Zilch,     location VARCHAR(30) Not Null,     value1 VARCHAR(10),     value2 VARCHAR(10),     value3 VARCHAR(10),     reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )

View raw code

Paste it in the SQL query field (highlighted with a ruby-red rectangle) and press the "Go" button to create your table:

ESP32 ESP8266 PHPMyAdmin Create SQL Table

After that, you should see your newly created table called SensorData in the example_esp_data database as shown in the figure below:

ESP32 ESP8266 PHPMyAdmin View SQL Database

3. PHP Script HTTP POST – Insert Information in MySQL Database

In this section, we're going to create a PHP script that is responsible for receiving incoming requests from the ESP32 or ESP8266 and inserting the data into a MySQL database.

If you're using a hosting provider with cPanel, you lot can search for "File Manager":

ESP32 ESP8266 CPanel Open Edit PHP Files

Then, select the public_html pick and press the "+ File" push to create a new .php file.

ESP32 ESP8266 CPanel Create New PHP File

Notation: if yous're following this tutorial and you're not familiar with PHP or MySQL, I recommend creating these exact files. Otherwise, you'll need to modify the ESP sketch provided with unlike URL paths.

Create a new file in /public_html with this verbal name and extension: mail service-esp-data.php

PHP Create New file post esp data

Edit the newly created file (postal service-esp-information.php) and copy the following snippet:

          <?php  /*   Rui Santos   Complete projection details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/      Permission is hereby granted, gratis of charge, to any person obtaining a copy   of this software and associated documentation files.      The above copyright discover and this permission notice shall be included in all   copies or substantial portions of the Software. */  $servername = "localhost";  // Supplant with your Database name $dbname = "REPLACE_WITH_YOUR_DATABASE_NAME"; // Replace with Database user $username = "REPLACE_WITH_YOUR_USERNAME"; // REPLACE with Database user password $password = "REPLACE_WITH_YOUR_PASSWORD";  // Keep this API Key value to be compatible with the ESP32 code provided in the projection page.  // If y'all alter this value, the ESP32 sketch needs to friction match $api_key_value = "tPmAT5Ab3j7F9";  $api_key= $sensor = $location = $value1 = $value2 = $value3 = "";  if ($_SERVER["REQUEST_METHOD"] == "Postal service") {     $api_key = test_input($_POST["api_key"]);     if($api_key == $api_key_value) {         $sensor = test_input($_POST["sensor"]);         $location = test_input($_POST["location"]);         $value1 = test_input($_POST["value1"]);         $value2 = test_input($_POST["value2"]);         $value3 = test_input($_POST["value3"]);                  // Create connection         $conn = new mysqli($servername, $username, $password, $dbname);         // Cheque connexion         if ($conn->connect_error) {             die("Connexion failed: " . $conn->connect_error);         }                   $sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3)         VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";                  if ($conn->query($sql) === True) {             echo "New tape created successfully";         }          else {             echo "Error: " . $sql . "<br>" . $conn->error;         }              $conn->close();     }     else {         echo "Wrong API Key provided.";     }  } else {     echo "No data posted with HTTP Post."; }  function test_input($information) {     $information = trim($information);     $data = stripslashes($data);     $data = htmlspecialchars($data);     return $data; }

View raw code

Before saving the file, you need to modify the $dbname, $username and $countersign variables with your unique details:

          // Your Database proper name $dbname = "example_esp_data"; // Your Database user $username = "example_esp_board"; // Your Database user password $password = "YOUR_USER_PASSWORD";

After adding the database name, username and countersign, save the file and proceed with this tutorial. If you try to access your domain proper noun in the next URL path, you'll encounter the following:

          http://example-domain.com/postal service-esp-data.php
ESP32 ESP8266 Test POST ESP Data PHP URL

iv. PHP Script – Display Database Content

Create some other PHP file in the /public_html directory that volition brandish all the database content in a web page. Name your new file: esp-data.php

PHP Create New file esp data

Edit the newly created file (esp-data.php) and copy the following lawmaking:

          <!DOCTYPE html> <html><body> <?php /*   Rui Santos   Complete projection details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/      Permission is hereby granted, free of accuse, to any person obtaining a copy   of this software and associated documentation files.      The above copyright detect and this permission notice shall exist included in all   copies or substantial portions of the Software. */  $servername = "localhost";  // REPLACE with your Database proper name $dbname = "REPLACE_WITH_YOUR_DATABASE_NAME"; // REPLACE with Database user $username = "REPLACE_WITH_YOUR_USERNAME"; // Supervene upon with Database user countersign $password = "REPLACE_WITH_YOUR_PASSWORD";  // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connexion if ($conn->connect_error) {     dice("Connectedness failed: " . $conn->connect_error); }   $sql = "SELECT id, sensor, location, value1, value2, value3, reading_time FROM SensorData Social club Past id DESC";  echo '<tabular array cellspacing="5" cellpadding="v">       <tr>          <td>ID</td>          <td>Sensor</td>          <td>Location</td>          <td>Value 1</td>          <td>Value 2</td>         <td>Value 3</td>          <td>Timestamp</td>        </tr>';   if ($effect = $conn->query($sql)) {     while ($row = $outcome->fetch_assoc()) {         $row_id = $row["id"];         $row_sensor = $row["sensor"];         $row_location = $row["location"];         $row_value1 = $row["value1"];         $row_value2 = $row["value2"];          $row_value3 = $row["value3"];          $row_reading_time = $row["reading_time"];         // Uncomment to prepare timezone to - 1 hour (you tin can change 1 to whatsoever number)         //$row_reading_time = engagement("Y-thou-d H:i:south", strtotime("$row_reading_time - 1 hours"));                // Uncomment to set timezone to + iv hours (y'all can alter four to any number)         //$row_reading_time = engagement("Y-thou-d H:i:southward", strtotime("$row_reading_time + 4 hours"));                echo '<tr>                  <td>' . $row_id . '</td>                  <td>' . $row_sensor . '</td>                  <td>' . $row_location . '</td>                  <td>' . $row_value1 . '</td>                  <td>' . $row_value2 . '</td>                 <td>' . $row_value3 . '</td>                  <td>' . $row_reading_time . '</td>                </tr>';     }     $result->gratis(); }  $conn->close(); ?>  </table> </body> </html>

View raw code

Later on adding the $dbname, $username and $password salvage the file and go on with this project. 

          // Your Database proper noun $dbname = "example_esp_data"; // Your Database user $username = "example_esp_board"; // Your Database user countersign $countersign = "YOUR_USER_PASSWORD";

If yous try to admission your domain name in the following URL path, you'll run across the following:

          http://example-domain.com/esp-information.php
ESP32 ESP8266 Test ESP Data PHP URL

That's it! If yous see that empty table printed in your browser, it means that everything is set up. In the next section, you'll learn how to insert information from your ESP32 or ESP8266 into the database.

5. Preparing Your ESP32 or ESP8266

This project is compatible with both the ESP32 and ESP8266 boards. You just need to assemble a simple circuit and upload the sketch provided to insert temperature, humidity, force per unit area and more into your database every 30 seconds.

Parts Required

For this case nosotros'll go sensor readings from the BME280 sensor. Here's a list of parts you need to build the circuit for this projection:

  • ESP32 board (read All-time ESP32 dev boards)
  • Culling – ESP8266 board (read Best ESP8266 dev boards)
  • BME280 sensor
  • Jumper wires
  • Breadboard

You tin can use the preceding links or go directly to MakerAdvisor.com/tools to find all the parts for your projects at the all-time cost!

Schematics

The BME280 sensor module we're using communicates via I2C advice protocol, so you lot need to connect information technology to the ESP32 or ESP8266 I2C pins.

BME280 wiring to ESP32

The ESP32 I2C pins are:

  • GPIO 22: SCL (SCK)
  • GPIO 21: SDA (SDI)

So, gather your circuit every bit shown in the next schematic diagram (read complete Guide for ESP32 with BME280).

BME280 wiring to ESP32

Recommended reading: ESP32 Pinout Reference Guide

BME280 wiring to ESP8266

The ESP8266 I2C pins are:

  • GPIO 5(D1): SCL (SCK)
  • GPIO 4(D2): SDA (SDI)

Assemble your circuit every bit in the next schematic diagram if you're using an ESP8266 board (read complete Guide for ESP8266 with BME280).

BME280 wiring to ESP8266

Recommended reading: ESP8266 Pinout Reference Guide

ESP32/ESP8266 Code

Nosotros'll program the ESP32/ESP8266 using Arduino IDE, so you must have the ESP32/ESP8266 addition installed in your Arduino IDE. Follow 1 of the next tutorials depending on the lath you're using:

  • Install the ESP32 Lath in Arduino IDE – you also demand to install the BME280 Library and Adafruit_Sensor library
  • Install the ESP8266 Lath in Arduino IDE – you also need to install the BME280 Library and Adafruit_Sensor library

After installing the necessary board add-ons, copy the following code to your Arduino IDE, but don't upload it withal. You need to make some changes to make it work for you lot.

          /*   Rui Santos   Complete project details at https://RandomNerdTutorials.com/esp32-esp8266-mysql-database-php/      Permission is hereby granted, costless of charge, to any person obtaining a copy   of this software and associated documentation files.      The above copyright notice and this permission detect shall be included in all   copies or substantial portions of the Software.  */  #ifdef ESP32   #include <WiFi.h>   #include <HTTPClient.h> #else   #include <ESP8266WiFi.h>   #include <ESP8266HTTPClient.h>   #include <WiFiClient.h>

You may like these posts

Post a Comment for "I Want to Upload Mysql File to Server and Access on Php"