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.

Answer 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

Answer 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.