源码链接：
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/