How to pass null value to database mysql with date type from php

Question

I spend too many hours trying to pass null value to null coloumn in database and all the solution that I google it didn't work for my code it give me this error :

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect date value: '' for column 'DateOfImplementation' at row 2

here is the query that update the table :

<?php

function updateDates($ApplicationID , $DateOfImplementation , $DueDate ,$FinanceDeliveryDate ,$FinanceReceiptDate){
    try {
        $dbc = new db();
        $dbhc = $dbc->connect();

        $sql = "UPDATE `applicationstudy` SET 
                `DateOfImplementation` = '$DateOfImplementation',
                `DueDate` = '$DueDate',
                `FinanceDeliveryDate` = '$FinanceDeliveryDate',
                `FinanceReceiptDate` = '$FinanceReceiptDate'
                WHERE `ApplicationID` = '$ApplicationID' ;";
        if( $dbhc->exec( $sql ) ) {
            return true;
        }
        else {
            return false;
        }
        $dbhc = null;
    } catch(PDOException $e) {
        echo $e->getMessage();
    }
}

?>

here is the post for insert the dates :

<?php

if ( isset( $_POST["applicationid"] ) ) {
    $applicationid = $helperObj->prepar_data( $_POST["applicationid"] );
}   

if( ! is_null( $_POST["FinanceDeliveryDate"] ) ) {
    $FinanceDeliveryDate = $helperObj->prepar_data( $_POST["FinanceDeliveryDate"] );
} else {
    $FinanceDeliveryDate = 'NULL';
}

if( ! is_null( $_POST["FinanceReceiptDate"] ) ) {
    $FinanceReceiptDate = $helperObj->prepar_data( $_POST["FinanceReceiptDate"] );

} else {
    $FinanceReceiptDateFil= 'NULL';
}

if( ! is_null( $_POST["DueDate"] ) ) {
    $DateOfFirstInstallment = $helperObj->prepar_data( $_POST["DueDate"] );
} else {
    $DateOfFirstInstallment ='NULL';
}
if( ! is_null( $_POST["DateOfImplementation"] ) ) {
    $DateOfImplementation = $helperObj->prepar_data( $_POST["DateOfImplementation"] );
} else {
    $DateOfImplementation = 'NULL';
}
$status = $appstudyobj->updateDates( $applicationid , $DateOfImplementation , $DateOfFirstInstallment ,$FinanceDeliveryDate ,$FinanceReceiptDate );
?>

How can I insert null value it work if there is no single quotation in the function but doesn't take the value correct if there is a date

If your columns default to null just skip them in the insert statement — Lelio Faieta, Oct 25 '17 at 10:39
if you have defined the field as date field in mysql you can only supply date (string) from PHP. It works with single quotes ```'NULLL'``` because this is a ```(string) Null``` — Raheel, Oct 25 '17 at 10:39
I can't skip them in insert because he my update the dates in too many submits so I have to leave them in the same statement @LelioFaieta — Reem Aziz, Oct 25 '17 at 10:42
What @LelioFaieta said is the correct way ! your approach is wrong when you are considering ```NULL``` as same as ```"NULL"```. If i would you i and i really dont want to keep it as ```NULL```, I would pass date as 00:00:00 to differentiate. — Raheel, Oct 25 '17 at 11:56

score 0 · Answer 1 · answered Oct 25 '17 at 10:43

0

php doesn't convert null into NULL

To fix this, check for Null Values before you do the query.

use "NULL" instead of null.

answered Oct 25 '17 at 10:43

Jayesh Vyas

1,145
3
15
35

Simpy · Answer 2 · 2017-10-25T10:53:53.000

0

You should use prepared statements to insert null values Eg.

//Testing
$DateOfImplementation = null;
$FinanceDeliveryDate = null;

$stmt = $dbhc-> prepare("UPDATE `applicationstudy` SET 
        `DateOfImplementation` = ?,
        `DueDate` = ?,
        `FinanceDeliveryDate` = ?,
        `FinanceReceiptDate` = ?
        WHERE `ApplicationID` = ?")
$stmt->bind_param('sssss', $DateOfImplementation, $DueDate, $FinanceDeliveryDate, $FinanceReceiptDate,  $ApplicationID)

$stmt->execute()

Referred from PHP/MySQL Insert null values & PHP UPDATE prepared statement

edited Oct 25 '17 at 10:53

answered Oct 25 '17 at 10:48

Simpy

1
1

Refer: https://stackoverflow.com/questions/17784390/mysql-how-to-insert-null-dates I think using prepared statements 'NULL' insertion would work. – Simpy Oct 26 '17 at 03:18

score 0 · Answer 3 · edited Oct 25 '17 at 11:04

$sql = "UPDATE `applicationstudy` SET 
        `DateOfImplementation` = '$DateOfImplementation',
        `DueDate` = '$DueDate',
        `FinanceDeliveryDate` = '$FinanceDeliveryDate',
        `FinanceReceiptDate` = '$FinanceReceiptDate'
        WHERE `ApplicationID` = '$ApplicationID' ;";

You made one type here ';' shouldn't be there after '$ApplicationID'.

And , you shouldn't pass NULL under quotations. Try without quotations i.e NULL instead of 'NULL'. Hope this helps!!

How to pass null value to database mysql with date type from php

3 Answers3