To deal with 'Boolean' values in PHP & MySQL

Question

Currently I'm using Tinyint(1) to indicate Boolean values in my MySQL databases, which I really don't like that. So, how could I store and retrieve Boolean values in my MySQL databases via PHP?

How to use it in WHERE clause and how to assign the value in INSERT, UPDATE queries properly?

When I have it back on PHP, it's TRUE, true, or simply 1, if I'm gonna check that with ===?

Also did you ever had any problem when you migrating from Tinyint(1) to BOOLEAN?

Thanks in advance. :)

Update:

I know that Tinyint(1) is the same as Boolean, however I want to work on Boolean data type instead of Tinyint(1). That's why I'm asking the question.

Solution 1

MySQL doesn't have a boolean data type. Tinyint(1) is pretty close enough. Working with this in PHP is simple.

If (1) echo 'true'; // is the same as if (true)
// Just as
if (0) echo 'false'; // is the same as if (false)

And if you really really want a boolean value, you can do

// $mysql_data is tinyint value from db
$boolean = $mysql_data ? true : false;
// Now you have your boolean as $boolean

Solution 2

With booleans, don't use === FALSE - the value is already a boolean (unless the function requires you to use ===, like strpos()). The value is booleanish - it's technically an integer, but PHP is a dynamic language, so it its not a problem.

Consider preg_match() function - it returns the number of matches (integer).

Would you prefer to write that?

if (preg_match('/\bregexp?\b/', $variable) === 1)

Or that?

if (preg_match('/\bregexp?\b/', $variable))

Obviously, the way without explicit === 1 is better. You ask if it matches, not if it has 0 matches. Also, if you think that === 1 is safer, why not do === 1 === TRUE?

Of course, it's possible to convert values to booleans using (bool) or !!.

Also, in certain languages such as C or Perl, there is no difference between booleans and numbers. It just works.

Comments

[juergen d]

BOOL, BOOLEAN are only synonyms for TINYINT(1) in MySQL.

[Mahdi]

thanks, yes I know that, but I want to work on Boolean in my PHP side, instead of Tinyint, that's why I'm asking the question ...

[Super Cat]

Though it depends on your methodology, you may need to cast the true false keywords to int when inserting into any int type. I do believe that some forms of prepared statements make this arrangement for you, but if your query is inline, then this is necessary.

[mikewasmike]

In MySQL, the values TRUE and FALSE are merely aliases for 1 and 0 as explained in mysql docs