CODEWITHSUNDEEP
mysql
claybot
claybot

Reputation: 11

PHP MySQL Insert Query is not Working as Expected

I am running into an issue with a PHP program which I am writing, and I cannot pinpoint what the issue is.

Here is the program:

   <?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";
    }

?>

I am running into an issue with this part of the code in particular:

    $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);

The code executes, however, the table does not have a new entry added to it, and the code gives the following output:

Adding data to DB failed

This must mean that $mysqli->query($sql) is returning a false value.

However, when I use the following SQL to insert into the table using PHPMyAdmin, the entry is added into the table without any issues:

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\')

Additionally, here is my dbSensorData.php information, with the actual password omitted:

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

Additionally, here is the command I used to create the MySQL Table:

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
);

Any help with this is greatly appreciated, as I have been stumped by this.

Upvotes: 0

Views: 260

Answers (1)

ezw
ezw

Reputation: 432

Is there a reason for those escaped quotes? It doesn't work for my with those backslashes in my phpMyAdmin. I had to go through the code and un-unescape those quotes.

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')

And that query worked for me.

Otherwise you could use mysqli_error() ( $mysqli->error ) to see what kind of error MySQLi throws at you. Might give you a hint as to why it's failing.

Upvotes: 1

Related Questions