這是目前bookstore的資料表,該範例將會用ESP32透過GET與POST的方式傳給PHP,然後PHP的頁面將會把POST或GET收到的資料傳給SQL
請先確認是否建立了一樣的資料表,或是自行修改程式,注意ESP發送的名稱是否與PHP程式端的接收名稱相同不然無法正常運作
下面程式碼的連結會提供三種傳值給資料庫的方式可以依據需求來使用,文中只會說明ESP32直接對SQL傳值的方式
首先先建立連線,檔名叫connecttest2.php
<?php
//PDO version
$servername = "192.168.158.35"; //根據你伺服器的IP
$username = "XXXX";
$password = "XXXX";
$dbName = "testdb"; //資料庫的名稱
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbName", $username, $password);
// set the PDO error mode to exception //PDO::ATTR_ERRMODE 錯誤報告,PDO::ERRMODE_EXCEPTION 表示 PDO 生成的錯誤
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
echo "<br>";
} catch(PDOException $e) {//表示 PDO 生成的錯誤
echo "Connection failed: " . $e->getMessage();//getMessage —獲取異常信息
}
?>
先是GET的使用方式,將下方的程式碼複製貼上檔名叫 httpgetpage.php
PHP程式碼:
<?php
require_once 'connecttest2.php';
header('Content-type: text/html; charset=utf-8');
// 若 $_POST['username'] 不是空值, 即顯示歡迎訊息
if ( $espname = $_GET["name"] ){
$espname = $_GET["name"];
$espbookname = $_GET["bookname"];
$espcash = $_GET["cash"];
date_default_timezone_set('Asia/Taipei'); //設定時區
$now = date("Y-m-d H:i:s");
$sql="INSERT bookstore ( NAME,BOOKNAME,CASH,DATETIME)
VALUES ('$espname','$espbookname','$espcash','$now')";
// 使用 exec() ，没有结果返回
$conn->exec($sql);
$rowadd = mysqli_affected_rows($conn);
//如果新增的筆數大於 0, 則顯示成功, 若否, 便顯示失敗
if ($rowadd >=0){
echo "新增成功";
}
else {
echo "新增失敗";
}
header('Location: http://192.168.158.80');
exit;
}
else{ echo "沒有輸入！";}
?>
ESP32程式碼:
#include <WiFi.h>
const char* ssid = "XXXX";
const char* password = "XXXX";
const char* host = "192.168.158.35"; // 要連線的IP
String espname = "doggi";
String espbookname = "IOTlink";
uint16_t x;
String espcash;
void setup() {
Serial.begin(115200);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
}
void loop() {
Serial.print("connecting to ");
Serial.println(host);
WiFiClient client;
const int httpPort = 80;
if (!client.connect(host, httpPort)) {
Serial.println("connection failed");
return;
}
x = random(1000);
String espcash = String(x);
String getData = "name=" + espname +"&bookname="+ espbookname +"&cash="+ espcash;
String url = "/httpgetpage.php?" + getData;
client.print(String("GET ") + url + " HTTP/1.1\r\n" +
"Host: " + host + "\r\n" +
"Connection: close\r\n\r\n");
unsigned long timeout = millis();
while (client.available() == 0) {
if (millis() - timeout > 20000) {
Serial.println(">>> Client Timeout !");
client.stop();
return;
}
}
while (client.available()) {
String line = client.readStringUntil('\r');
Serial.print(line);
}
Serial.println();
Serial.println("closing connection");
}
下面是POST的使用方式,將下方的程式碼複製貼上檔名叫 httppostpage.php
PHP程式碼:
<?php
require_once 'connecttest2.php';
header('Content-type: text/html; charset=utf-8');
// 若 $_POST['username'] 不是空值, 即顯示歡迎訊息
if ( $espname = $_POST["name"] ){
$espname = $_POST["name"];
$espbookname = $_POST["bookname"];
$espcash = $_POST["cash"];
date_default_timezone_set('Asia/Taipei'); //設定時區
$now = date("Y-m-d H:i:s");
$sql="INSERT bookstore ( NAME,BOOKNAME,CASH,DATETIME)
VALUES ('$espname','$espbookname','$espcash','$now')";
// 使用 exec() ，没有结果返回
$conn->exec($sql);
$rowadd = mysqli_affected_rows($conn);
//如果新增的筆數大於 0, 則顯示成功, 若否, 便顯示失敗
if ($rowadd >=0){
echo "新增成功";
}
else {
echo "新增失敗";
}
//echo "<p><a href='192.168.64.80'>回系統首頁</a></p>";
header('Location: http://192.168.158.80');
exit;
}
else{ echo "沒有輸入名稱！";}
?>
ESP32程式碼:
#include <WiFi.h>
const char* ssid = "XXXX";
const char* password = "XXXX";
const char* host = "192.168.158.35";
String espname = "vivi";
String espbookname = "zigbeelink";
uint16_t x;
String espcash;
void setup() {
Serial.begin(115200);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
}
void loop() {
Serial.print("connecting to ");
Serial.println(host);
WiFiClient client;
const int httpPort = 80;
if (!client.connect(host, httpPort)) {
Serial.println("connection failed");
return;
}
x = random(1000);
String espcash = String(x);
String postData = "name=" + espname +"&bookname="+ espbookname +"&cash="+ espcash;
String url = "/httppostpage.php";
client.print(String("POST ") + url + " HTTP/1.1\r\n" +
"Host: " + host + "\r\n" +
"Connection: close\r\n" +
"Content-Type: application/x-www-form-urlencoded\r\n" +
"Content-Length: " + postData.length() + "\r\n\r\n" +
postData);
unsigned long timeout = millis();
while (client.available() == 0) {
if (millis() - timeout > 20000) {
Serial.println(">>> Client Timeout !");
client.stop();
return;
}
}
while (client.available()) {
String line = client.readStringUntil('\r');
Serial.print(line);
}
Serial.println();
Serial.println("closing connection");
}
下方連結是範例的壓縮檔,同時裡面也附上透過AJAX的方式每十秒就發送一次資料給SQL,檔名叫WebAutoPostValue
該範例須將裡面的資料夾DATA透過SPIFFS的方式燒錄進ESP32,該範例將主程式及SPIFFS都燒錄完畢後會顯示一個ESP的SERVER IP,打開瀏覽器將IP輸入連線到該網頁,會看到當下的數值,
要注意的是如果你將網頁關掉那就會停止發送給SQL了
https://drive.google.com/file/d/15--wX3aTAYFIn1xBIzj74NI27yr2dE1g/view?usp=share_link
留言列表
