问题描述
我有一个项目,我在其中将数据从ESP32发送到MySQL数据库.我正在使用一个PHP脚本来获取POST并更新数据库中的数据.脚本如下:
I am having a project in which I am sending data from ESP32 to a MySQL Database. I am using a PHP script that fetches the POST and updates the data in the database. The script is the following:
<?php
$servername = "localhost";
// REPLACE with your Database name
$dbname = "licenta";
// REPLACE with Database user
$username = "admin";
// REPLACE with Database user password
$password = "mihnea";
// Keep this API Key value to be compatible with the ESP32 code provided in the project page$
// If you change this value, the ESP32 sketch needs to match
$api_key_value = "tPmAT5Ab3j7F9";
$api_key= $spot = $distance = $vacancy = "";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$api_key = test_input($_POST["api_key"]);
if($api_key == $api_key_value) {
$spot = test_input($_POST["spot"]);
$distance = test_input($_POST["distance"]);
$vacancy = test_input($_POST["vacancy"]);
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE SensorData SET distance = '" . $distance . "', vacancy = '" . $vacanc$
if ($conn->query($sql) === TRUE) {
echo "Updated successfully";
}
else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}
else {
echo "Wrong API Key provided.";
}
}
else {
echo "No data posted with HTTP POST.";
}
function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
我的问题是,脚本没有更新指定的位置并仅更新该特定行,而是更新了所有行.我的数据库是这样构建的:
My problem is that instead of searching for the specified spot and updating only that specific row, my script updates all the rows. My database is built like this:
MariaDB [licenta]> describe SensorData;
+--------------+-----------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+---------------------+-------------------------------+
| id | int(6) unsigned | NO | PRI | NULL | auto_increment |
| spot | varchar(30) | NO | | NULL | |
| mcu | varchar(30) | YES | | NULL | |
| distance | int(6) | YES | | NULL | |
| vacancy | varchar(10) | YES | | NULL | |
| reading_time | timestamp | NO | | current_timestamp() | on update current_timestamp() |
+--------------+-----------------+------+-----+---------------------+-------------------------------+
如何编写查询,以便例如,如果我发送spot ="B1",它将仅更新B1所在的行.谢谢!
How can I write the query so that if, for example, I send spot = "B1", it will update only the row in which B1 is located. Thank you!
推荐答案
您在$sql
查询中缺少WHERE
条件.
您的代码中也存在语法错误-我想是
You have also syntax error in your code - I suppose that
$sql = "UPDATE SensorData SET distance = '" . $distance . "', vacancy = '" . $vacanc$
无效.
尝试将$sql
变量值更改为
$sql = "UPDATE SensorData SET distance = '".$distance."', vacancy = '".$vacancy."' WHERE spot = '".$spot."'";
这篇关于HTTP POST更新所有行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!