PHP Script is no longer fixing Datetime field

Question

I have the following PHP script that takes a poorly formatted date column and then writes it into a datetime column.

The data looks like this:

Tail_Number,Departure_Date,Departure_Date2,Departure_Time,Arrival_Date,Arrival_Date2,Arrival_Time,Original_Airport,Destination_Airport

N681GH,14-Jun-15,,1701,14-Jun-15,,1808,KFHB,KPDK

The end result is that the Date2 fields end up with the default 0000-00-00 00:00:00 instead of actually combining the date/time as they had previously.

The script was working fine but now suddendly is not updating my datetime field but I can't tell what is wrong as the month seems the same?

$sql = "SELECT *, Date(Departure_Date2) As Dep2 FROM faaData0707new WHERE  Departure_Date2 = '0000-00-00 00:00:00'";
echo $sql;

$q = mysql_query($sql, $conn);
if (!$q) {
        die("query error: ".mysql_error($conn)."<br>in query: <pre>$sql</pre>");
}

$x = 0;
while ($obj = mysql_fetch_object($q)) {



        $sDepTime = $obj->Departure_Time;
        $sDepDate = split("/",$obj->Departure_Date);
        $sDepMonth = "";
        if (strtoupper($sDepDate[1]) == "JAN")
            $sDepMonth = "01";
        elseif (strtoupper($sDepDate[1]) == "FEB")
            $sDepMonth = "02";
        elseif (strtoupper($sDepDate[1]) == "MAR")
            $sDepMonth = "03";
        elseif (strtoupper($sDepDate[1]) == "APR")
            $sDepMonth = "04";
        elseif (strtoupper($sDepDate[1]) == "MAY")
            $sDepMonth = "05";
        elseif (strtoupper($sDepDate[1]) == "JUN")
            $sDepMonth = "06";
        elseif (strtoupper($sDepDate[1]) == "JUL")
            $sDepMonth = "07";
        elseif (strtoupper($sDepDate[1]) == "AUG")
            $sDepMonth = "08";
        elseif (strtoupper($sDepDate[1]) == "SEP")
            $sDepMonth = "09";
        elseif (strtoupper($sDepDate[1]) == "OCT")
            $sDepMonth = "10";
        elseif (strtoupper($sDepDate[1]) == "NOV")
            $sDepMonth = "11";
        elseif (strtoupper($sDepDate[1]) == "DEC")
            $sDepMonth = "12";


        $sArrivalTime = $obj->Arrival_Time;
        $sArrivalDate = split("/",$obj->Arrival_Date);
        $sArrivalMonth = "";

        if (strtoupper($sArrivalDate[1]) == "JAN")
            $sArrivalMonth = "01";
        elseif (strtoupper($sArrivalDate[1]) == "FEB")
            $sArrivalMonth = "02";
        elseif (strtoupper($sArrivalDate[1]) == "MAR")
            $sArrivalMonth = "03";
        elseif (strtoupper($sArrivalDate[1]) == "APR")
            $sArrivalMonth = "04";
        elseif (strtoupper($sArrivalDate[1]) == "MAY")
            $sArrivalMonth = "05";
        elseif (strtoupper($sArrivalDate[1]) == "JUN")
            $sArrivalMonth = "06";
        elseif (strtoupper($sArrivalDate[1]) == "JUL")
            $sArrivalMonth = "07";
        elseif (strtoupper($sArrivalDate[1]) == "AUG")
            $sArrivalMonth = "08";
        elseif (strtoupper($sArrivalDate[1]) == "SEP")
            $sArrivalMonth = "09";
        elseif (strtoupper($sArrivalDate[1]) == "OCT")
            $sArrivalMonth = "10";
        elseif (strtoupper($sArrivalDate[1]) == "NOV")
            $sArrivalMonth = "11";
        elseif (strtoupper($sArrivalDate[1]) == "DEC")
            $sArrivalMonth = "12";




        $sNewDepartDate = $sDepDate[2]."-".$sDepMonth."-".$sDepDate[0]." ".substr($sDepTime,0,2).":".substr($sDepTime,-2);
        $sNewArrivalDate = $sArrivalDate[2]."-".$sArrivalMonth."-".$sArrivalDate[0]." ".substr($sArrivalTime,0,2).":".substr($sArrivalTime,-2);

        $sql = "UPDATE faaData0707new Set Departure_Date2 = '".$sNewDepartDate."', Arrival_Date2 = '".$sNewArrivalDate."' WHERE PK = ".$obj->PK;
        #echo $sql;
        mysql_query($sql,$conn) or die("query error: ".mysql_error($conn)."<br>in query: <pre>$sql</pre>");;




        $x++;   




}

echo "fixed ".$x." rows";
exit(0);

Answer 1

You can reduce a lot of your code to a few lines:

$sql = "SELECT *, Date(Departure_Date2) As Dep2 FROM faaData0707new WHERE  Departure_Date2 = '0000-00-00 00:00:00'";
echo $sql;

$q = mysql_query($sql, $conn);

$x = 0;
while ($obj = mysql_fetch_object($q)) {

        $Departure_Date = DateTime::createFromFormat( "d-M-y Hi", $obj->Departure_Date . " " . $obj->Departure_Time );
        $Arrival_Date = DateTime::createFromFormat( "d-M-y Hi", $obj->Arrival_Date . " " . $obj->Arrival_Time );

        $sNewDepartDate = $Departure_Date->format( "Y-m-d H:i:00" );
        $sNewArrivalDate = $Arrival_Date->format( "Y-m-d H:i:00" );

        $sql = "UPDATE faaData0707new Set Departure_Date2 = '".$sNewDepartDate."', Arrival_Date2 = '".$sNewArrivalDate."' WHERE PK = ".$obj->PK;
        #echo $sql;
        mysql_query($sql,$conn) or die("query error: ".mysql_error($conn)."<br>in query: <pre>$sql</pre>");;

        $x++;   
}

echo "fixed ".$x." rows";
exit(0);

Alternatively, you can do it a lot easier directly in MySQL:

UPDATE faaData0707new 
SET Departure_Date2=STR_TO_DATE(CONCAT(Departure_Date,' ',Departure_Time), "%d-%b-%y %H%I") 
WHERE Departure_Date2 = '0000-00-00 00:00:00'

This will create a single string containing both the date and the time (CONCAT()), extract the date and time from it (STR_TO_DATE()) and write them directly into the row again. Not a single line in PHP nessecary.

Answer 2

you split the Departure_Date by '/' (see line

$sDepDate = split("/",$obj->Departure_Date);

but in your db you have this date with '-', see:

14-Jun-15

and same with Arrival_Date. So you work with empty array items, not with items from your date

Btw, isn't this cleaner solution?

$oldDate = strtotime($obj->Departure_Date);
$sNewDepartDate = date("d-m-Y", $oldDate);
$sNewDepartDate .=  " " . substr($sArrivalTime,0,2).":".substr($sArrivalTime,-2);