The difficulty with the Internet of Things deployment for hobbyists and makers is out of the absence of a simple open-source GPL-ed platform (like we have WordPress, Magento, Joomla, and OctoPress for content management). MQTT is often not easy for this segment, examples with HTTP are too less.
The Present Scenario of IoT Platforms
Blynk.io decided to shut down the Blynk app and local server. IBM Watson IoT was a professional solution but we already informed the readers about IBM Kills Watson IoT Platform. So we have PHP, MySQL, Eclipse Mosquitto, Paho, NodeRED etc in hand. Except for PHP & MySQL, the rest are related to technologies developed by IBM (and they are free software).
Of course, there are a few larger open-source software suites for IoT, such as ThingsBoard. But few tutorials have ever been written on ThingsBoard with ESP32. Self-hosting ThingsBoard is not as easy as self-hosting WordPress. There are excellent proprietary platforms, for example, Sinric Pro (we discussed it).
---
It is unsafe in terms of time investment to rely on someone’s IoT setup. We think that for ordinary personal projects (with GNU GPL compatible license) such as controlling a light bulb or recording the temperature and humidity, it is better to use PHP, MySQL and HTTP protocol. For commercial projects, it is better to hire developers.
Back to PHP MySQL
In simple PHP-MySQL applications for using with ESP32 Arduino like that we have shown in our guide on ESP32 Arduino IoT Relay Control, there is no security checkpoint. If you deploy such a project for controlling the light of your home, then anyone can turn it on or off! You need some “door” and “lock” to such kind of simple applications so that they become private, secure and professional.
Phrases such as API, RESTful API, API key etc sound complicated to newbies as well as to hobbyists. But they are not complicated in PHP development when there is only one user.
An API is a set of definitions for writing applications (that is what we are trying to do). When we are changing the state of the LED connected to an ESP32 Arduino, keeping a few PHP scripts on the server, it is a contract between a provider and an information user. We can modify the simple scripts in a manner so that we could specify that the user needs supply some constant values to authorize. An API helps you to define and standardize the communication.
How We Will Do So?
Below is a part of the PHP script to write data received from ESP32 to the MySQL database.
Suppose we are monitoring the temperature with a DHT22 sensor and ESP32. The api_key_value in this case is a value of your choice. If that value matches the request from ESP32, one criterion will be fulfilled.
Next, we have mentioned api_key must have hardware (suppose it is the sensor’s name), location (so that you can organize the data from multiple locations such as Kolkata, Krishnagar, Sodepur, NYC, Paris etc places where you have your houses, apartments etc), and the value itself. Our MySQL database has a table named DeviceMAC.
Then this will be the PHP file:
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 | $api_key_value = "abcdefgh12345678"; $api_key= $hardware = $location = $value = ""; if ($_SERVER["REQUEST_METHOD"] == "POST") { $api_key = arduino_input($_POST["api_key"]); if($api_key == $api_key_value) { $hardware = arduino_input($_POST["hardware"]); $location = arduino_input($_POST["location"]); $value = arduino_input($_POST["value"]); $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO DeviceMAC (hardware, location, value) VALUES ('" . $hardware . "', '" . $location . "', '" . $value . "')"; if ($conn->query($sql) === TRUE) { echo "New information successfully added"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); } else { echo "Possible Error with API Key"; } } else { echo "Error with HTTP POST"; } function arduino_input($data) { $data = trim($data); $data = stripslashes($data); $data = htmlspecialchars($data); return $data; } |
The above file will be complete if you add PHP opening tag at the beginning and database details. Below SQL query will create the required MySQL table.
1 2 3 4 5 6 7 | CREATE TABLE DeviceMAC ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, hardware VARCHAR(30) NOT NULL, location VARCHAR(30) NOT NULL, value VARCHAR(10), reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) |
In this case, part of your .ino file for ESP32 will be like this one:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ... const char* ssid = "YOUR_SSID"; const char* password = "YOUR_PASSWORD"; const char* serverName = "https://example.com/post-data.php"; String apiKeyValue = "abcdefgh12345678"; String hardwareName = "DHT22"; String sensorLocation = "Kolkata"; ... void loop() { ... if(WiFi.status()== WL_CONNECTED){ WiFiClient client; HTTPClient http; http.begin(client, serverName); http.addHeader("Content-Type", "application/x-www-form-urlencoded"); String httpRequestData = "api_key=" + apiKeyValue + "&hardware=" + sensorName + "&location=" + sensorLocation + "&value=" + String(dht22.readTemperature()) + ""; Serial.print("httpRequestData: "); Serial.println(httpRequestData); ... } |
The above set of logic will authenticate.
