PHP MySQL Insert Query is not Working as Expected

Question

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.

I really doubt you want floating values. You may want DECIMAL column types, but floating values come with a raft of calcuation issues as their values are by their true nature, er, floating. — Mm-Art-In
– Mm-Art-In, Commented Feb 18, 2018 at 23:09
Thank you for your input. You are absolutely correct. I have changed my table so that it stores data as decimal rather than float. However, this has not resolved the issue, unfortunately. — claybot
– claybot, Commented Feb 19, 2018 at 3:00

Mm-Art-In · Accepted Answer · 2018-02-18 23:11:14Z

1

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.

edited Feb 18, 2018 at 23:11

Mm-Art-In

23k13 gold badges81 silver badges144 bronze badges

answered Feb 18, 2018 at 23:02

ezw

4303 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

claybot Over a year ago

After removing the escaped quotes, the query still failed to work. I have also tried using mysqli_error(), and it is not providing any output...Very Strange!

ezw Over a year ago

What happens if you add error_reporting(E_ALL); at the beginning of the file?

ezw Over a year ago

@claybot make sure your values are aligned with your columns. Looks like you are putting a WindDirection (varchar) into humidity (float). The varchar (xxx) is trying to be converted to float

Collectives™ on Stack Overflow

PHP MySQL Insert Query is not Working as Expected

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related