Inserting NULL into date Field

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
garybrett17
New php-forum User
New php-forum User
Posts: 3
Joined: Wed Jan 17, 2018 7:28 am

Wed May 23, 2018 2:44 am

Hi there, I have been trying to resolve this issue for weeks now and finally admitted defeat!

When I insert or update a php form several fields can either be null or contain a date but my code is always reporting it as invalid date when inserting or updating.

The fields below 'signedupdate' & 'DOB' cpuld be NULL or a datevalue, the mysql table is set as Date | Default: NULL | Allow Null

$dateValue1 = is_null($rowData[' signedupdate ']) ? 'NULL' : sprintf("'%s'", $rowData[' signedupdate']);
$dateValue2 = is_null($rowData[' DOB ']) ? 'NULL' : sprintf("'%s'", $rowData[' DOB']);

$sql = sprintf("UPDATE tbl_lead SET DOB = '%s' , signedupdate = '%s' WHERE client_id = %d;", $rowData['$dateValue2'], $rowData['$dateValue1']);
$this->GetConnection()->ExecSQL($sql);

Has anyone any ideas what I can use to allow the form to send null data?

chorn
php-forum Fan User
php-forum Fan User
Posts: 559
Joined: Fri Apr 01, 2016 2:18 am

Wed May 23, 2018 5:47 am

Why do you try to insert $rowData when you cast to $dateValue first? The variable wouldn't be interpreted within single quotes. Also a NULL value must be inserted without quotes, or it's taken as a string. You should read the section about quotes again:

http://php.net/manual/en/language.types.string.php

Use Prepared Statements, it's easier.

garybrett17
New php-forum User
New php-forum User
Posts: 3
Joined: Wed Jan 17, 2018 7:28 am

Wed May 23, 2018 7:00 am

Thanks for you reply, This is code I have inherited from a generator for internal use only. The original code was below but I was playing around with it. I have tried many different ways including removing the ' ' but still trows the invalid date error, presumably as you say its sending string value rather than NULL. If I add a date its all good but more often than not its left blank causing this issue
$lastInsertId = $this->GetConnection()->GetLastInsertId();
$sql = sprintf("INSERT INTO tbl_lead (client_id, signedupdate) VALUES(%d, '%s');", $lastInsertId, $rowData['signedupdate']);
$this->GetConnection()->ExecSQL($sql);
Thanks again

chorn
php-forum Fan User
php-forum Fan User
Posts: 559
Joined: Fri Apr 01, 2016 2:18 am

Wed May 23, 2018 9:14 am

I would recommend to first fix the SQL statement before testing around with any value. But still: if you want to insert a NULL value, the quotes must be removed. Better use Prepared Statements, e.g. with PDO, so the quotes are added automatically only when necessary.

Post Reply