Just a quick note to point out that CakePHP fakes a BOOLEAN field in MySQL by using tinyint(1). MySQL doesn’t have a native support for BOOLEAN.
Therefore if you attempt to save some value other than 0 or 1, CakePHP will not allow you to do that (instead it will just save 1). The easiest way to get around this is to make your field tinyint(2), as one option.
FYI, supporting ticket: https://trac.cakephp.org/ticket/3903
nuts and bolts of cakephp 
You’re so damn quick!
Thanks for the help on irc – was about to post the same thing ;)
@JadB
Ha ha… thanks for the blog topic ;)
@Teknoid: What do you mean by not having support for BOOLEAN type?
You can set BOOLEAN type for a column.
Maybe the problem is that isn’t CakePHP that fakes a BOOLEAN in tinyints. Maybe MySQL does.
I’m I wrong?
@rafaelbandeira3
There is simply no “real” boolean data type in mysql.
http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html
So cake has to do something to automagiacally support one, and tinyint(1) is the best option it seems.
I’d always been told (and seen people get told) in irc://freenode/#mysql that the enum type http://dev.mysql.com/doc/refman/5.0/en/enum.html used with possible values of “Y/N”, “T/F”, “True/False” or some other such logical options is the best substitute for boolean. Is there some reason why enum wasn’t used for this purpose, I wonder?
@Silver Knight
ENUM is not supported by cake. There are a few discussions as to why (which I mostly tend to agree with), but a quick search at the google group should give you plenty to digest.
Thank you for the quick response. After a bit of research, it appears the primary reason the enum type is not supported in CakePHP is that enum is specific to a particular database vendor (MySQL). However, the same research turned up several nifty snippets of code which did allow CakePHP to support the enum type. This causes me to wonder: why hack around the lack of a boolean type by misusing the tinyint type when it would be just as simple to hack in a small “fix” using enum only when it’s needed to replace the boolean? I’m probably not understanding something key to the issue, I’m guessing?
@Silver Knight
No, you’ve got it just right. The ENUM type has been ruled out completely, that’s the bottom line…
To me the deal with tinyint(1) seems worthwhile, it probably allows for easier adoption across various DB’s. And the benefit of using tinyint(1) for boolean automagic, far outweighs the inconvenience of having to make the field tinyint(2) if other values are needed to be stored.
Well, you are probably right on that one. After all, declaring tinyint(1) or tinyint(2) doesn’t actually change the max values a tinyint can store anyhow. Either way, it’s 0-255 unsigned or -128 to 127 signed any way you slice it. The (1), (2), or (3) only affects the output, not the storage capacity (according to http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html afaict), so I guess it’s not really a big deal. ;-)
The deal is, if I want to store values from 0 up to 9, and I defined a TINYINT(1) UNSIGNED NOT NULL in MySQL, Cake will just assume I want to have a boolean field.
@Kemal
Yes, you are absolutely right… hence the point of the post and suggestion that you should use tinyint(2) instead, if you need values other than 1 or 0 for that field.
I came upon this situation today and was scratching my head. I had a field I had originally been using as a boolean, but I decided to make it store a numerical value 0 to 9 instead, so I left it as a tinyint(1) but CakePHP just wouldn’t save anything but 0 or 1!
Now I understand why, of course. I decided for my purposes to switch it to a char(1) though instead of a tinyint(2). BUT… it still didn’t work. It occurred to me that CakePHP must cache model information, and it does. So, just a tip to anyone who might be in my shoes. If you change your database structure, be sure to head over to app/tmp/cache/models and delete the file for the table you’ve altered. Once I did that, it worked like a charm!
@room34
That’s an excellent advice. It is very important to remember to clear cache after any DB modification to save a few head/desk connections.
Thank you for pointing that out.