enter null values in the database

Yii 2.0 General Discussions
1

Hi

I have changed the common\user\model.php in the yii2 advanced template to better model my enterprise where not all employees have an email account.

I would like to know how can I insert a NULL in the database if the email field is left blank in the signupform view?

In SignupForm.php I have the following validation rule for email:




['email', 'unique', 'targetClass' => '\common\models\User',

'filter' => ['not', ['email' => 'NULL']], 'message' => 'This email address has already been taken.'],

(I really would expect the filter to be redundant, and maybe it is),

and changed the function signup:




public function signup()

    {

        if (!$this->validate()) {

            return null;

        }


        $user = new User();

        $user->username = $this->username;

	// the original

	// $user->email = $this->email;

	// was changed to

        $user->email = $this->email ? $this->email : 'NULL';

        $user->setPassword($this->password);

        $user->generateAuthKey();


        return $user->save() ? $user : null;

    }

}

This however does not work. In fact the form does not even post. By removing the quotes from NULL I get the form to validate but it inserts an empty string rather then NULL in the database.

On the next signup with no email I get a PDOException: duplicate entry on the email UNIQUE constraint.

Where can I tweak to get to save NULL to the basebase?

2

Setting the model property to string ‘NULL’ will literally try to insert the string ‘NULL’ in to the database. Meanwhile, if the property is actually null, it should insert a null value. So, by that logic, you should only set it if passed through. Something like:


if (isset($this->email) && is_string($this->email) && strlen($this->email) > 0) {

    $user->email = $this->email;

}
3 



['email', 'unique', 'targetClass' => '\common\models\User'],

['email', 'default', 'value' => null],

yii\validators\DefaultValueValidator

This:


'value' => null

can be safely omitted but makes the rule easier to read/understand.

4

Maybe User model has its own validation rules and one of them (probably a 'trim" rule) converts NULL to empty string?

5

What is default value for you email column in your db? Make sure that you allow null in database.

6

Thanks all for the input.

I thought I would be clever and assign PDO::PARAM_NULL to email if empty:




...

$user->email = !empty($this->email) ? $this->email : \PDO::PARAM_NULL;

...

Similarly to the case where I use a quoted ‘NULL’, the form does not process.

I am conceding on this issue and resigning to remove the unique constraint as I believe that I would need to delve into the query builder code to change the behaviour to what I want.

7

Hi again,

Not wanting to admit defeat, I resorted to a perhaps not very elegant solution but it does the trick.

I changed the function signup() as follows:




public function signup()

{

   if (!$this->validate()) {

       return null;

   }


   $user = new User();

   $user->username = $this->username;

   $user->email = $this->email;

   $user->setPassword($this->password);

   $user->generateAuthKey();


   /* original code */

   // return $user->save() ? $user : null;


   /* changed to */

   if ($user->save()) {

      // update the empty string to NULL if required

      if (isempty($user->email) {

         try {

            Yii::$app->db->pdo->exec('UPDATE user SET email = NULL WHERE email = "" ');

         } catch(PDOException $e) {

            echo $e->getMessage();

         }

      return $user;

   }

   return null;

}

It would certainly be neater and less wasteful to save the NULL from the start. If any one knows how, please share.

8

If you do not save the email field at all then it will be null. ;)

9

I need to save the email field as some users have email.

After some more research on this, I found this post.

So to have the applications behave as I want in this particular case, I added the element


'attributes' => [PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING]

to the ‘db’ array definition.

I am still perplexed, as far as I could establish, Yii2 should store a php null as a mysql NULL without further ado. I don’t know why it didn’t in my application as all changes I made from the default install could not at all have affected it otherwise.

This solution the best so far, I don’t need to change the signup() method at all, but with the proviso that this behaviour may not be desirable in all cases.

10

That is probably a bug in the Oracle db driver. Perhaps you can inquire at the Yii2 Github issue tracker?