We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Proper way to work with byte(1) column.

Hi. I have byte(1) column in my table for store true/false values. I would like to use in my models and forms for Yes/No selectors e.g.

 $this->add(new Select('approved', [
            1 => 'Yes',
            0 => 'No'
        ]));

When I'm trying to save model I get:

: String data, right truncated: 1406 Data too long for column 'approved'

Dumping 'approved's value prior to saving gives me:

string '1' (length=1)

However when I'm getting record from database I have

int 1

for the same field.

The only sollution I can think about is to do intval on field beforeSave.

My question is: is it the best way to work with byte(1)? Or it's better to change database column to Y/N for instance?



79.0k

For special fileds like that, ORM is troublesome. For instance, I'm using BIT field. So in regular PDO statement :

$sql = 'SELECT *, BIN(Active + 0) AS Active FROM VIDEOS WHERE Active = ? ORDER BY VideoID DESC LIMIT ?';

Never found a way how to do it via ORM.



15.9k
edited Jan '16

Hi, stamster. I was able to work with byte column using Phalcon model methods:

$approvedUsers = User::find([
                    'approved = :approved:',
                    'bind' => ['approved'=>1]
                    ]);

After some reading on BYTE format I think the proper way to work with byte is: don't work with byte. I desided to move to TINYINT(1), because BYTE behaviour is inconsistent even in MySQL. This can be done in one SQL statement without any data or code modification.



79.0k

Thanks for the solution. But I'm not sure how to implement BIT field. Just like you said - don't work with such special fields. But when it isn't you who made a model of a database... you don't have much choice but to dump the ORM and use PDO.