php - 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.

Many thanks in advance for your help and time

Answer 1

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