mysql - PHP MySQL 插入查询未按预期工作

Question

我正在编写的 PHP 程序遇到问题，但我无法确定问题所在。

这是程序:

   <?php
require 'dbSensorData.php';

$sql = "INSERT INTO `data` (`timeStamp`, `MessageType`, `UniqueID`, `SensorID`, `SoilMoistureOne`, `SoilMoistureTwo`, `SoilMoistureThree`, `SoilMoistureFour`, `SoilMoistureFive`, `SoilMoistureSix`, `SoilTempOne`, `SoilTempTwo`, `SoilTempThree`, `SoilTempFour`, `SoilTempFive`, `SoilTempSix`, `SoilMoistureWM1`, `SoilMoistureWM2`, `SoilMoistureWM3`, `Rainfall`, `WindSpeed`, `WindDirection`, `temperature`, `humidity`) VALUES (CURRENT_TIMESTAMP, \'0010\',\'00000\',\'000\',\'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'xxx\')";

    $add = $mysqli->query($sql);

    if ( $add )
    {
        echo "Adding data to DB was Successful";
    }

    else 
    {
        echo "Adding data to DB failed";
    }

?>

我特别遇到了这部分代码的问题:

    $sql = "INSERT INTO `data` (`timeStamp`, `MessageType`, `UniqueID`, `SensorID`, `SoilMoistureOne`, `SoilMoistureTwo`, `SoilMoistureThree`, `SoilMoistureFour`, `SoilMoistureFive`, `SoilMoistureSix`, `SoilTempOne`, `SoilTempTwo`, `SoilTempThree`, `SoilTempFour`, `SoilTempFive`, `SoilTempSix`, `SoilMoistureWM1`, `SoilMoistureWM2`, `SoilMoistureWM3`, `Rainfall`, `WindSpeed`, `WindDirection`, `temperature`, `humidity`) VALUES (CURRENT_TIMESTAMP, \'0010\',\'00000\',\'000\',\'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'xxx\')";

    $add = $mysqli->query($sql);

代码执行，但是表中没有添加新条目，并且代码给出以下输出:

Adding data to DB failed

这必定意味着 $mysqli->query($sql) 返回错误值。

但是，当我使用 PHPMyAdmin 使用以下 SQL 插入表时，该条目会毫无问题地添加到表中:

INSERT INTO `data` (`timeStamp`, `MessageType`, `UniqueID`, `SensorID`, `SoilMoistureOne`, `SoilMoistureTwo`, `SoilMoistureThree`, `SoilMoistureFour`, `SoilMoistureFive`, `SoilMoistureSix`, `SoilTempOne`, `SoilTempTwo`, `SoilTempThree`, `SoilTempFour`, `SoilTempFive`, `SoilTempSix`, `SoilMoistureWM1`, `SoilMoistureWM2`, `SoilMoistureWM3`, `Rainfall`, `WindSpeed`, `WindDirection`, `temperature`, `humidity`) VALUES (CURRENT_TIMESTAMP, \'0010\',\'00000\',\'000\',\'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'00.00\', \'xxx\')

另外，这是我的dbSensorData.php信息，省略了实际密码:

    <?php
$host = 'localhost';
$user = 'root';
$pass = 'password';
$db = 'SensorData';
$mysqli = new mysqli($host,$user,$pass,$db) or die($mysqli->error);
?>

此外，这是我用来创建 MySQL 表的命令:

CREATE TABLE `sensorData`.`data` 
(
    `timeStamp` TIMESTAMP NOT NULL PRIMARY KEY,
    `MessageType` INT NOT NULL,
    `UniqueID` VARCHAR(64) NOT NULL,
    `SensorID` VARCHAR(64),
    `SoilMoistureOne` FLOAT,
    `SoilMoistureTwo` FLOAT,
    `SoilMoistureThree` FLOAT,
    `SoilMoistureFour` FLOAT,
    `SoilMoistureFive` FLOAT,
    `SoilMoistureSix` FLOAT,
    `SoilTempOne` FLOAT,
    `SoilTempTwo` FLOAT,
    `SoilTempThree` FLOAT,
    `SoilTempFour` FLOAT,
    `SoilTempFive` FLOAT,
    `SoilTempSix` FLOAT,
    `SoilMoistureWM1` FLOAT,
    `SoilMoistureWM2` FLOAT,
    `SoilMoistureWM3` FLOAT,
    `Rainfall` FLOAT,
    `WindSpeed` FLOAT,
    `WindDirection` VARCHAR(32),
    `temperature` FLOAT,
    `humidity` FLOAT
);

非常感谢对此的任何帮助，因为我对此感到困惑。

Answer 1

这些转义引号有原因吗？它对我的 phpMyAdmin 中的那些反斜杠不起作用。我必须仔细检查代码并取消转义这些引号。

INSERT INTO `data` (`timeStamp`, `MessageType`, `UniqueID`, `SensorID`, `SoilMoistureOne`, `SoilMoistureTwo`, `SoilMoistureThree`, `SoilMoistureFour`, `SoilMoistureFive`, `SoilMoistureSix`, `SoilTempOne`, `SoilTempTwo`, `SoilTempThree`, `SoilTempFour`, `SoilTempFive`, `SoilTempSix`, `SoilMoistureWM1`, `SoilMoistureWM2`, `SoilMoistureWM3`, `Rainfall`, `WindSpeed`, `WindDirection`, `temperature`, `humidity`) VALUES (CURRENT_TIMESTAMP, '0010','00000','000','00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', '00.00', 'xxx')

这个查询对我有用。

否则你可以使用 mysqli_error() ( $mysqli->error ) 来查看 what kind of error MySQLi向你扔。可能会提示您失败的原因。