ESP32+PHP+MYSQL 搭建自己的物联网平台Demo

源码链接：

https://gitee.com/hejinlv/ESP32_PHP_MYSQL

ESP32 POST 数据到PHP，并通过PHP修改mysql内容与显示

软件架构

ESP32 MicroPython

安装教程

Win安装phpstudy 或者树莓派安装 php + mysql-server + nginx 或者放至云服务器
将esp-data.php post-esp-data.php 放入服务器主目录
EPS32下载ESP32.py
数据库导入SensorData_Table.sql文件或者新建一个表

使用说明

输入图片说明

ESP32代码：

from mpython import *
import network
import urequests
import json
import time

my_wifi = wifi()

my_wifi.connectWiFi("CMCC-GoodMaker", "steam666")


num = 0
if my_wifi.sta.isconnected():
    oled.fill(0)
    oled.DispChar("连接成功", 0, 0, 1)
    oled.DispChar((str(my_wifi.sta.ifconfig()[0])), 0, 16, 1)
    oled.show()
else:
    oled.fill(0)
    oled.DispChar("连接失败", 0, 0, 1)
    oled.show()
while True:
    _response = urequests.post("http://192.168.1.6/post-esp-data.php/", headers={"Content-Type":"application/json"}, data=json.dumps({"api_key":"tPmAT5Ab3j7F9", "sensor":"Light", "location":"Office", "value1":str(light.read()), "value2":str(sound.read()), "value3":"3", "id":str(num + 1)}))
    print(_response.text)
    time.sleep(3)

PHP接收post，解析json，更新数据库代码：

<?php

$servername = "localhost";  //数据库地址
// 数据库名
$dbname = "bak";
// 数据库用户名
$username = "root";
// 数据库密码
$password = "123456";

//保持此API密钥值与项目页面中提供的ESP32代码兼容。
//如果您更改此值，则ESP32草图需要匹配
$api_key_value = "tPmAT5Ab3j7F9";

$api_key= $sensor = $location = $value1 = $value2 = $value3 = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    
    $read_post = file_get_contents('php://input');
    $datas=json_decode($read_post, true);

    $api_key = $datas['api_key'];

    if($api_key == $api_key_value) {
        $sensor = $datas['sensor'];
        $location =$datas['location']; 
        $value1 = $datas['value1'];
        $value2 = $datas['value2'];
        $value3 = $datas['value3'];
        $id     = $datas['id'];
        // 创建数据库连接
        $conn = new mysqli($servername, $username, $password, $dbname);
        // 检查数据库连接状态
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        } 
        //插入一个新的数据行
        //$sql = "INSERT INTO SensorData ( id, sensor, location, value1, value2, value3)
        //VALUES ('" . $id . "', '" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "')";

        //更新表中内容
        $sql = mysqli_query($conn,"UPDATE SensorData SET value1 = '".$value1."',value2 = '".$value2."'
            WHERE sensor='Light'");
        //如果数据修改成功
        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        }  
        else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
    
        $conn->close();
    }
    else {
        echo "Wrong API Key provided.<br/>";
    }

}
else {
    echo "No data posted with HTTP POST.<br/>";
}


?>

PHP数据库查询，显示代码：

<!DOCTYPE html>
<html><body>
<?php


$servername = "localhost";

// 数据库名
$dbname = "bak";
// 数据库用户名
$username = "root";
// 数据库密码
$password = "123456";

// 创建数据库连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查数据库连接状态
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

//查询数据库bak  SensorData表中的内容
$sql = "SELECT id, sensor, location, value1, value2, value3, reading_time FROM SensorData ORDER BY id DESC";

echo '<table cellspacing="5" cellpadding="5">
      <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 ($result = $conn->query($sql)) {
    while ($row = $result->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"];
      
        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->free();
}

$conn->close();

//<!--JS 页面自动刷新 -->
echo ("<script type="text/javascript">");
echo ("function fresh_page()"); 
echo ("{");
echo ("window.location.reload();");
echo ("}"); 
echo ("setTimeout('fresh_page()',3000);"); //3秒刷新一次
echo ("</script>");

?> 
</table>
</body>
</html>

参考：

https://randomnerdtutorials.com/esp32-esp8266-mysql-database-php/