Inserting date into oracle database from PHP

Question

I have a question regarding inserting date from PHP into Oracle DB

so i have this in my PHP,

$delivDate = strval($_POST['deliveryDate']);

echo $delivDate; //Just for checking

$delivInsertionSql = "INSERT INTO DELIVERY (DELIVERY_DATE) 
                      VALUES (to_date('".$delivDate ."','MM/DD/YYYY hh24:mi:ss'))";
$delivInsertionParse = oci_parse($conn, $delivInsertionSql);
oci_execute($delivInsertionParse);

The problem is, i dont see any new record inserted in the database. the data type of DELIVERY_DATE is DATE

Solution 1

Few days back I face the same issue and it was date format issue. So I change the date format and it works for me. You can try below code and test it and if it works than modify code as per your need.

$delivDate = date('d-m-Y h:i:s', strtotime($_POST['deliveryDate']));    
INSERT INTO DELIVERY (DELIVERY_DATE) VALUES (to_date('".$delivDate."','dd-mm-yy hh24:mi:ss'))";

Solution 2

I'm guessing its a date format issue but you need to have an oci_error statement to expose any sql related errors.

Please refer to the oci_execute section here http://php.net/manual/en/function.oci-execute.php. There are a few examples where oci_execute is used in if statements implying that its a boolean return. If its false, fork into the oci_error code and expose your sql issues.

Excerpted from link above to save you time:

<?php 
$q = oci_parse($c, ""); 
if($q != false){ 
    // parsing empty query != false 
    if(oci_execute($q){ 
        // executing empty query != false 
        if(oci_fetch_all($q, $data, 0, -1, OCI_FETCHSTATEMENT_BY_ROW) == false){ 
            // but fetching executed empty query results in error (ORA-24338: statement handle not executed) 
            $e = oci_error($q); 
            echo $e['message']; 
        } 
    } 
    else{ 
        $e = oci_error($q); 
        echo $e['message']; 
    } 
} 
else{ 
    $e = oci_error($link); 
    echo $e['message']; 
} 
?>