Got it, let's break down a practical, scalable HTTPS-based IoT solution for your ESP32 + LAMP setup that checks all your boxes. Here's a step-by-step approach tailored to your experience level:

整体架构 Overview #

We'll stick to your preference for HTTPS (instead of MQTT over WebSocket) since it integrates seamlessly with LAMP and scales well as you add more devices or features. The core flow is:

• ESP32 collects sensor data → sends it to your server via HTTPS POST
• Server-side PHP script stores the data in MySQL
• Authenticated users log into your web dashboard to view historical data
• Users send control commands (like LED on/off) via the dashboard → PHP stores commands in MySQL
• ESP32 periodically polls the server via HTTPS GET to fetch and execute pending commands

ESP32端 Implementation #

ESP32 has built-in TLS support for HTTPS, so you won't need extra hardware. Here's a quick breakdown using the Arduino framework:

1. WiFi & HTTPS Setup: Use the WiFiClientSecure library to establish a secure connection. You'll need your server's SSL certificate (grab it from Let's Encrypt later) to avoid certificate errors.
2. Data Upload Example:

   #include <WiFi.h>
   #include <WiFiClientSecure.h>
   #include <ArduinoJson.h>
   
   const char* ssid = "YOUR_WIFI_SSID";
   const char* password = "YOUR_WIFI_PASS";
   const char* server = "your-domain.com";
   const char* cert = "-----BEGIN CERTIFICATE-----\nYOUR_SERVER_CERT\n-----END CERTIFICATE-----";
   
   WiFiClientSecure client;
   
   void setup() {
     Serial.begin(115200);
     WiFi.begin(ssid, password);
     while (WiFi.status() != WL_CONNECTED) delay(500);
   
     client.setCACert(cert);
   }
   
   void loop() {
     // Simulate sensor data (replace with your actual sensor code)
     float temp = 25.5;
     float humidity = 60.2;
   
     // Pack data into JSON
     StaticJsonDocument<200> doc;
     doc["device_id"] = "esp32_001";
     doc["temperature"] = temp;
     doc["humidity"] = humidity;
     String jsonPayload;
     serializeJson(doc, jsonPayload);
   
     // Send HTTPS POST request
     if (client.connect(server, 443)) {
       client.println("POST /submit_sensor_data.php HTTP/1.1");
       client.println("Host: your-domain.com");
       client.println("Content-Type: application/json");
       client.print("Content-Length: ");
       client.println(jsonPayload.length());
       client.println();
       client.println(jsonPayload);
   
       // Read server response (optional)
       while (client.available()) {
         String line = client.readStringUntil('\n');
         Serial.println(line);
       }
     }
     client.stop();
   
     // Fetch pending commands (add this part)
     // ... similar HTTPS GET request to /get_command.php?device_id=esp32_001
     delay(30000); // Upload every 30 seconds
   }
   

3. Command Fetching: Add an HTTPS GET request to pull pending commands from the server. After executing a command (like toggling an LED), send a status update back to the server to mark the command as completed.

Ubuntu 16.04 LAMP Server Setup #

First, get your LAMP stack up and running with HTTPS:

1. Install LAMP:

   sudo apt-get update && sudo apt-get install apache2 mysql-server php7.0 libapache2-mod-php7.0 php7.0-mysql -y
   

2. Enable HTTPS with Let's Encrypt:

   sudo apt-get install certbot python-certbot-apache -y
   sudo certbot --apache
   

   Follow the prompts to get a free SSL certificate and force all traffic to HTTPS.
3. Database Configuration:
   • Log into MySQL: sudo mysql -u root -p
   • Create a database and tables:

     CREATE DATABASE iot_system;
     USE iot_system;
     
     -- Table for sensor data
     CREATE TABLE sensor_data (
       id INT AUTO_INCREMENT PRIMARY KEY,
       device_id VARCHAR(50) NOT NULL,
       temperature FLOAT,
       humidity FLOAT,
       timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
     );
     
     -- Table for control commands
     CREATE TABLE control_commands (
       id INT AUTO_INCREMENT PRIMARY KEY,
       device_id VARCHAR(50) NOT NULL,
       command VARCHAR(20) NOT NULL, -- e.g., "LED_ON", "LED_OFF"
       status TINYINT DEFAULT 0, -- 0 = pending, 1 = executed
       created_at DATETIME DEFAULT CURRENT_TIMESTAMP
     );
     
     -- Table for user logins
     CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(50) UNIQUE NOT NULL,
       password VARCHAR(255) NOT NULL
     );
     

   • Create a dedicated MySQL user for PHP (never use root!):

     CREATE USER 'iot_user'@'localhost' IDENTIFIED BY 'your-strong-password';
     GRANT ALL PRIVILEGES ON iot_system.* TO 'iot_user'@'localhost';
     FLUSH PRIVILEGES;
     

PHP Scripts for Data & Commands #

Keep these scripts in your Apache web root (/var/www/html/):

1. submit_sensor_data.php (handles ESP32 data uploads):

   <?php
   header("Content-Type: application/json");
   $db = new mysqli("localhost", "iot_user", "your-strong-password", "iot_system");
   
   if ($db->connect_error) {
     echo json_encode(["status" => "error", "message" => "DB connection failed"]);
     exit;
   }
   
   // Get JSON payload from ESP32
   $payload = json_decode(file_get_contents("php://input"), true);
   if (!isset($payload["device_id"], $payload["temperature"], $payload["humidity"])) {
     echo json_encode(["status" => "error", "message" => "Missing parameters"]);
     exit;
   }
   
   // Use prepared statement to prevent SQL injection
   $stmt = $db->prepare("INSERT INTO sensor_data (device_id, temperature, humidity) VALUES (?, ?, ?)");
   $stmt->bind_param("sdd", $payload["device_id"], $payload["temperature"], $payload["humidity"]);
   
   if ($stmt->execute()) {
     echo json_encode(["status" => "success"]);
   } else {
     echo json_encode(["status" => "error", "message" => $stmt->error]);
   }
   
   $stmt->close();
   $db->close();
   ?>
   

2. get_command.php (sends pending commands to ESP32):

   <?php
   header("Content-Type: application/json");
   $db = new mysqli("localhost", "iot_user", "your-strong-password", "iot_system");
   
   if ($db->connect_error) {
     echo json_encode(["status" => "error", "message" => "DB connection failed"]);
     exit;
   }
   
   $device_id = $_GET["device_id"] ?? "";
   if (empty($device_id)) {
     echo json_encode(["status" => "error", "message" => "Missing device_id"]);
     exit;
   }
   
   // Fetch first pending command and mark as executed
   $stmt = $db->prepare("SELECT id, command FROM control_commands WHERE device_id = ? AND status = 0 LIMIT 1");
   $stmt->bind_param("s", $device_id);
   $stmt->execute();
   $result = $stmt->get_result();
   
   $command = null;
   if ($row = $result->fetch_assoc()) {
     $command = $row["command"];
     // Mark as executed
     $updateStmt = $db->prepare("UPDATE control_commands SET status = 1 WHERE id = ?");
     $updateStmt->bind_param("i", $row["id"]);
     $updateStmt->execute();
     $updateStmt->close();
   }
   
   echo json_encode(["status" => "success", "command" => $command]);
   $stmt->close();
   $db->close();
   ?>
   

Web Dashboard with Login #

Build a simple, secure dashboard for users:

1. Login System: Create login.php to handle user authentication. Use password_hash() to store passwords (never store plain text!):

   <?php
   session_start();
   if ($_SERVER["REQUEST_METHOD"] == "POST") {
     $db = new mysqli("localhost", "iot_user", "your-strong-password", "iot_system");
     $username = $_POST["username"];
     $password = $_POST["password"];
   
     $stmt = $db->prepare("SELECT password FROM users WHERE username = ?");
     $stmt->bind_param("s", $username);
     $stmt->execute();
     $result = $stmt->get_result();
   
     if ($row = $result->fetch_assoc()) {
       if (password_verify($password, $row["password"])) {
         $_SESSION["username"] = $username;
         header("Location: dashboard.php");
         exit;
       } else {
         $error = "Invalid username or password";
       }
     } else {
       $error = "Invalid username or password";
     }
   }
   ?>
   <!-- HTML login form here -->
   

2. Dashboard Page (dashboard.php):
   • Check if the user is logged in (via session_start() and isset($_SESSION["username"]))
   • Query the sensor_data table to display historical data (use Chart.js for nice graphs)
   • Add a form to send control commands (e.g., a dropdown for "LED_ON"/"LED_OFF" and device selection) that inserts into control_commands when submitted

Scalability Tips #

• Device Management: Assign unique device_ids to each ESP32 to support multiple devices later.
• Real-Time Improvements: For faster command delivery, add Server-Sent Events (SSE) to push commands to ESP32 instead of polling. PHP can handle SSE with a simple loop.
• Data Storage: If you collect tons of sensor data, consider adding a time-series database like InfluxDB alongside MySQL for better performance with historical data queries.
• Security: Regularly update your server software, use strong passwords, and add rate limiting to your PHP APIs to prevent abuse.

内容的提问来源于stack exchange，提问作者Fillipos Christou