[Leo]

Hello! I can't update my product with a NULL value in one of the table cells in MariaDb

So I have a column which I want to be either 1, 0, or NULL
It is a tinyint(1), default null, column

I'm trying to update the data like this:

PHP Code:

//in my form, in view
form_dropdown('valid', [null => '', 1 => 'Yes', 0 => 'No'])

//in my controller
$product->fill($_POST);
$model->save($product);


right now I made a quick fix to make it work like this:
$product->fill($_POST);
$product->project_variant = ($_POST['valid'] === '1' || $_POST['valid'] === '0') ? $_POST['valid'] : null
$model->save($product);

But this is so long and ugly, theres gotta be a "cleaner" way? 


I'm using a model and entities. Help?

[jreklund]

Does $_POST contain null? And fill() are the one not accepting it?

Code:

var_dump($_POST);

[Leo]

Yeah, I saw it now...
var_dump($_POST); does not contain null for this value, because in my code when i write form_dropdown('valid', [null => '', 1 => 'Yes', 0 => 'No']) the select for the null value looks like this: <option value selected="selected"></option>

I guess, there is no easy way of inserting a null directly from a $_POST...I have to specify it in PHP

[jreklund]

Will to empty strings help?
'' => ''

Does it count as a value then?

[Gary]

if (POST['valid'] === '') POST['valid'] = NULL;
$product->project_variant = $_POST['valid'];

Or you could consider the implications of changing your database field/column to not work with NULL, but simply be empty?... (remember NULL also takes up space) and then simply do this:

$product->project_variant = $_POST['valid'];

[Leo]

@jreklund The empty space counts as a value - it inserts empty space in to the database if the column is text based. If it is number or boolean it inserts 0. Only way to insert NULL is to write it out specifically in PHP (like $_POST['valid'] = null).

@Gary actually the reason I want NULL in this record is to make it "like new" in case the user did not chose "Yes" or "No" - or doesn't know what to chose.
I did not know that NULL takes up space. I force NULL in a lot of my database because I always thought performance-wise NULL is preferable to empty space. But I actually don't know. So is NULL and empty space the same? What about 0? I use MariaDB.
----
update: after about 10 mins of reading the net a lot of people say use null for innodb but not for myisam. I use innodb because supposedly it makes use of multi-core CPU processing...and it's 2020 right now and everything except old stuff is multi-core.
but in general null is better as a default because it's easier interpreted as !isset($yourNullDBcell); vs empty or 0 which can actually be interpreted as a set value.

[Gary]

If you're using the database in STRICT mode, if you have '' in a TINYINT field it'll likely complain. Of course, NULL won't have this problem if it's set as 'allowed' in the database config... but be aware that (if it's not explicitly allowed, but you're running in the default NON-STRICT mode), according to the documentation, it will be implicitly converted: to an empty string for string types, and the zero value for numeric, date and time types.

I believe NULL is probably stored as a single CHAR (byte), and under certain types of encoding as two bytes. Though, saying that, I can't see how it could be stored as a single byte in your TINYINT field without having to be two bytes.

0 will be one byte in your TINYINT field, and '' will be stored either as one byte (in the prefix byte) if it's stored in a VARCHAR field, or as the maximum field-length if a CHAR field (eg: in a CHAR field of length 10, '' will store it as ten empty CHARs (the CHAR type is a fixed-length field). So NULL can be better for some, but not all field types.

The other way you could try to do it, if you're running in the default NON-STRICT mode, is to simply let '' be 0 ('like new') and YES=1, NO=2. You could probably fiddle your code without too much effort to have this also work whilst in STRICT mode too (?).

Not sure that gives you an answer... though, there are a few things to think about.

[Leo]

So you are basically saying in a tinyint field an actual int (0 - 255) would be better than NULL?