Empty string in database instead of NULL

Need Help
1

If I enter a value from a field that is empty via a form, an entry with an empty string is stored in the database. Could it be NULL instead?

Is there anything to set, or is it a PHP CAKE feature?

Thank You

2

Well first of all the DB column needs be allowed to have NULL values.
You can check that in the structure/schema of your table.

If so none other than a validator like

$validator->scalar( 'my_columnname' )
  ->allowEmptyString( 'my_columnname' );

allows you to save null values as well.

3

Yes, I have the column set to NULL, I have validation set in the model ->allowEmptyString('my_columnname'); after save in the DB it is not NULL but empty.
How to save a record so that it is NULL in the DB?

4

Which DBMS are you using and which type does your column have?

5

Maria DB 10.4.13, type column is TEXT, or VARCHAR (utf8mb4_unicode_ci)

6

You will need the Nullable behavior of the Shim Plugin.

Follow the Install instructions here

And then how to configure the behavior here

7

Thank you, it is not possible without the plugin, or do I have to have another type of db?

8

No, this is not related to your DB Type.
CakePHP in general tries to keep the data as consistent as possible so you don’t run into problems based upon this structure (like having to do where field != '' AND field IS NOT NULL queries)

There has already been a lengthy discussion about this problem here:

1 Like
9

Therefore, it is recommended that you do not enter a NULL value in the column.

10

actually if you do the same with a int column CakePHP will save NULL instead of"" because there is no "" value for int columns.

You do know, that in the HTML form there is no difference between "" and null right? You can’t represent those 2 states just with an <input type="text"> and therefore the submitted form will always return "" for that input regardless of if it needs to be null or "" in the database.

CakePHP just used this approach to keep the data in the DB consistent.

1 Like
11

I understand, thank you very much for the explanation

12

you can debug (display) your entity after patch or before save and you can try change field manually to NULL and save it. If this works, (check in DB via other tool) just add in beforeSave

if (emtpy($entity->your_string) ) $entity->your_string = null;
13

I see it differently, because why does a previously defined field or fields have to change the field type if no data has been transferred to this/these field(s)?
The simplest solution would be to define the following without any shims/plugins in the corresponding model(s):

public function beforeMarshal(EventInterface $event, ArrayObject $data, ArrayObject $options): void
    {
        foreach ($data as $key => $value) {
            if ($value === '') {
                $data[$key] = null;
            }
        }
    }
14

This would work if you have only fields that prefer null values to empty strings. There are use cases where null and empty strings say very different things (e.g. null means “not initialized”, while empty string means “initialized to an empty value”).