Laravel 5.8 BIT(1) datatype issue

Question

I have a column enabled with datatype bit(1). I am trying to save 0 or 1 value in Database by Laravel eloquent.

$model->enabled = $inputs['enabled'];
$model->save();

I have saved values in my config file.

'enabled' => [
    '0' => 'No',
    '1' => 'Yes',
],

But when I tried to save these value in database. I was getting error like,

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'enabled' at row 1

when i ran query in mysql phpmyadmin, it saved data correctly database.

But running this query by eloquent, it produces error.

It seems you are saving data ' 'No', 'Yes' that is string not 0 or 1 — Sehdev, Feb 25 '20 at 16:32
by doing var_dump($inputs['enabled']); it shows me 0 or 1. it produces error on save(); — Faisal Khan, Feb 25 '20 at 16:34
Does this answer your question? [Laravel 4.2 BIT datatype issue](https://stackoverflow.com/questions/27704052/laravel-4-2-bit-datatype-issue) — miken32, Feb 26 '20 at 17:46

Ruben Danielyan · Answer 1 · 2020-02-25T16:43:55.193

3

you need tinyint type, in your migration file do

$table->boolean('enabled')->default(false);

When you migrate with boolean('column_name') it creates a column with type tinyint(1) and you can set to model true/1 or false/0 and save. For example

$model->boolean_field = true;
$model->save();

Or

$model->boolean_field = 1;
$model->save();

edited Feb 25 '20 at 16:43

answered Feb 25 '20 at 16:36

Ruben Danielyan

728
4
19

Please add some explanation to your answer such that others can learn from it - why is tinyint needed? – Nico Haase Feb 25 '20 at 16:38
When you migrate with boolean('column_name') it creates a column with type tinyint(1) and you can set to model true/1 or false/0 and save. For example `$model->boolean_field = true; $model->save();` It will be the same as `$model->boolean_field = 1; $model->save();` – Ruben Danielyan Feb 25 '20 at 16:41
1

Please add all such information to your answer by editing it. Don't put explanations in the comment section – Nico Haase Feb 25 '20 at 16:42

score 1 · Answer 2 · answered Feb 25 '20 at 16:44

As per MySQL manual you can use should use bool and boolean which are aliases of tinyint to store 0 or 1 value

TINYINT: A value of zero is considered false. Non-zero values are considered true.

So use:

$table->tinyInteger('enabled');

https://dev.mysql.com/doc/refman/8.0/en/other-vendor-data-types.html

score 1 · Answer 3 · answered Feb 25 '20 at 16:46

1

A boolean in mysql is generaly a tinyint.

Eloquent models can cast boolean automaticaly : https://laravel.com/docs/6.x/eloquent-mutators#attribute-casting

protected $casts = [
    'enabled' => 'boolean',
];

Before saving your model, make sure your variable is a boolean

$model->enabled = $inputs['enabled']==='Yes';
$model->save();

answered Feb 25 '20 at 16:46

Sébastien R

66
1
4

what if i try to save 'No' value ? – Faisal Khan Feb 25 '20 at 16:47
$inputs['enabled']==='Yes' returns `false` so it works – Sébastien R Feb 25 '20 at 16:51
Basically i am using dropdown from where i select yes or no to save 1 or 0 value into database respectively. i have to handle both things at the same time. can you please guide me ? – Faisal Khan Feb 25 '20 at 16:55
so your `$inputs['enabled']` should be equal to 0 or 1 ? In this case, you should only do `$model->enabled = $inputs['enabled']` and it will works as expected. – Sébastien R Feb 25 '20 at 17:20
Thank you for you response. Yes by doing so it is returning 0 or 1, but it is not saving by doing $model->save(); and producing error "String data, right truncated: 1406 Data too long for column 'enabled' " – Faisal Khan Feb 25 '20 at 17:28
It is not possible if you have casting it to a boolean ! – Sébastien R Feb 25 '20 at 17:37
No i haven't casting it to boolean. – Faisal Khan Feb 25 '20 at 18:55

score 0 · Answer 4 · answered Feb 25 '20 at 16:57

also you can use mutator to force values.
example : in your model, append :

 public function setEnabled($value) // note the E in uppercase.
    {
        if ($value=='YES') { $this->attributes['enabled'] = 1;
        if ($value=='NO')  { $this->attributes['enabled'] = 0;
        if ($value=='1')  { $this->attributes['enabled'] = 1;
        if ($value=='0')  { $this->attributes['enabled'] = 0;
        // etc...
        // the logic can be improved... yes/YES/No/no/0/1/empty/null/ etc..
    }

and in the client code

   $model->enabled = 'YES'; // || 'NO' || etc..,  : note the e in lowercase.

then method setEnabled is called during assignation and 'enabled' will become 1

doc : https://laravel.com/docs/5.8/eloquent-mutators

score 0 · Answer 5 · answered Feb 26 '20 at 11:09

So i had solved my question by just adding DB::raw();

like,

$model->enabled = DB::raw($inputs['enabled']);
$model->save();

Eloquent doesn't work by calling Boolean value directly, first you have to gone through your values in DB::raw();

It works like a charm.

Laravel 5.8 BIT(1) datatype issue

5 Answers5