ERROR: Incorrect decimal value: '' for column

Question

I have a server which is running Apache 2.4, PHP 7.4 and MySQL 5.7.32

When I run a script that inserts data into a table I am getting an error of:

ERROR: Incorrect decimal value: '' for column 'ScheduledTimeStamp' at row 1

The data is from an .XML file which is provided from an outside source and there will be occasions where some of the fields will be empty.

The code for populating the variables prior to inserting into the data table is:

if(empty($record["SCHEDULEDDATE"])) {
    $SCHEDULEDDATE = "";
    $ScheduledTimeStamp = "";
} else {
    $SCHEDULEDDATE = $record["SCHEDULEDDATE"];
    $SCHEDULEDDATETIME = $SCHEDULEDDATE . " " . $SCHEDULEDTIME;
    $ScheduledTimeStamp = strtotime($SCHEDULEDDATETIME);
}

The SQL Query:

$sql = "INSERT INTO FIDS_LHR2 (AODBUniqueID, FlightHistoryID, IO, DayNumber, StatusCode, AirlineName, AirlineCode, FlightNo, AirlineLogoUrlPng, CodeShare, DestinationAirportName, DestinationAirportCode, DestinationCity, DestinationFamiarName, DestinationStateCode, DestinationCountryCode, Flight, Remarks, RemarksWithTime, RemarksCode, AirportCode, AirportName, Terminal,  ScheduleTime, ScheduleDate, ScheduledTimeStamp, EstimatedTime, EstimatedDate, EstimatedTimeStamp, CurrentTime, CurrentDate, ScheduleGateTime, ScheduleGateDate,  EstimatedGateTime, EstimatedGateDate, CurrentGateTime, CurrentGateDate, CurrentGateTimeStamp, FlightActive, FlightDisplay) 

VALUES ('".$AODBUniqueID."','".$FlightHistoryID."','".$_POST['IO']."', '".$DayNumber."', '".$record["STATUSCODE"]."', '".$record["AIRLINENAME"]."', '".$AirlineCode."', '".$record["FLIGHTNUMBER"]."', '".$LogoPath."', '".$record["ISCODESHARE"]."', '".$record["DESTINATIONAIRPORTNAME"]."', '".$record["DESTINATIONAIRPORTCODE"]."', '".$record["DESTINATIONCITY"]."', '".$record["DESTINATIONFAMILIARNAME"]."', '".$DESTINATIONSTATECODE."', '".$record["DESTINATIONCOUNTRYCODE"]."', '".$record["FLIGHT"]."', '".$record["REMARKS"]."', '".$record["REMARKSWITHTIME"]."', '".$record["REMARKSCODE"]."', '".$record["AIRPORTCODE"]."', '".$IATALookup."', '".$_POST['Terminal']."', '".$SCHEDULEDTIME."', '".$_POST['ScheduleDate']."', '".$ScheduledTimeStamp."', '".$ESTIMATEDTIME."', '".$_POST['EstimatedDate']."','".$EstimatedTimeStamp."', '".$CURRENTTIME."',  '".$_POST['CurrentDate']."',  '".$SCHEDULEDGATETIME."', '".$_POST['ScheduleGateDate'] ."', '".$ESTIMATEDGATETIME."', '".$_POST['EstimatedGateDate']."', '".$CURRENTGATETIME."', '".$_POST['CurrentGateDate'] ."', '".$CurrentGateTimeStamp."', '".$FlightActive."', '".$FlightDisplay."')";

If $ScheduledTimeStamp has content it would be a UNIX TimeStamp, example: 1610522700

The data table structure for the "ScheduledTimeStamp" is column type: decimal, length:30, decimal point:0

Is there any reason why, if "$ScheduledTimeStamp" is empty I get the ERROR.

Please share more details. The given code does not contain any SQL queries — Nico Haase, Jan 12 '21 at 10:09
Is the column nullable? I'd expect that, since you say that this data doesn't always exist. Also, please note that the way you build your query is unsafe due to the possibility of an SQL injection. See [here](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) how to prevent it. — El_Vanja, Jan 12 '21 at 10:20
Please share more details. What have you tried to debug why the given query is not working? If your table is defined such that it **should** contain any value in that column, you have to provide a value in **all** cases — Nico Haase, Jan 12 '21 at 10:23

score 0 · Answer 1 · answered Jan 12 '21 at 10:13

You haven't share your SQL Queries code but I believe I got the reason for the error.

You're trying to insert/update the following variable $ScheduledTimeStamp to a SQL COLUMN which type is timestamp/decimal. Therefore, its value must be decimal.

In case it's empty you set it to "" which is a STRING type. Therefore, you get an error.

if(empty($record["SCHEDULEDDATE"])) {
    $SCHEDULEDDATE = "";
    $ScheduledTimeStamp = 0; // <--- not "" which is type string
} else {
    $SCHEDULEDDATE = $record["SCHEDULEDDATE"];
    $SCHEDULEDDATETIME = $SCHEDULEDDATE . " " . $SCHEDULEDTIME;
    $ScheduledTimeStamp = strtotime($SCHEDULEDDATETIME);
}

ERROR: Incorrect decimal value: '' for column

1 Answers1