How to pass NULL to SQL query

[potap]

I have an integer value. It can be NULL (actually NULL is the default value).
When I set it to some value in the edit form, save it, and then want to clean this field it doesn't allow me to do so with the following error:

===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'reg_payment' at row 1

===SQL===========

UPDATE registration SET `reg_payment` = '' WHERE `reg_id` = '10';

How may I change the value of reg_payment field that I send to DB from empty string to NULL if it's empty?

[steven]

Hi potap,

Try

UPDATE registration SET `reg_payment` = NULL WHERE `reg_id` = '10';


Steven

[kev1n]

Presently, only date types are nulled out when a field is cleared and saved. Actually, this should also apply to numeric fields.
Here's an updated nudata.php :

nudata.zip

Please test it and let me know if it works as expected.

[potap]

Yes, it works! Thank you so much.

[potap]

BTW, an idea for future is to put the array of NULLable types to the settings. For example I needed this functionality for INT type only, but not tinyint (I use it as boolean without nulls). I corrected nullableTypes array in nudata.php, it was pretty easy, thanks to Kevin. But as an idea for future it may be part of settings.

[kev1n]

BTW, an idea for future is to put the array of NULLable types to the settings. For example I needed this functionality for INT type only, but not tinyint (I use it as boolean without nulls). I corrected nullableTypes array in nudata.php, it was pretty easy, thanks to Kevin. But as an idea for future it may be part of settings.

This makes perfect sense to me. The following types are now set to NULL when the nuBuilder's object value is blank.

Define an array $nuConfigDBTypesSetNullWhenEmpty in nuconfig.php to overwrite the default.

Code: Select all

$nuConfigDBTypesSetNullWhenEmpty = [
			"integer",
			"int",
			"mediumint",
			"longint",
			"decimal",
			"float",
			"real",
			"double",
			"serial",
			"date",
			"datetime",
			"timestamp",
			"year",
		];