How to deal with 'Boolean' values in PHP & MySQL?

We are using MySQL version 8.0.12. Let us first check the MySQL version:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

To deal with Boolean in MySQL, you can use BOOL or BOOLEAN or TINYINT(1). If you use BOOL or BOOLEAN, then MySQL internally converts it into TINYINT(1).

In BOOL or BOOLEAN data type, if you use true literal then MySQL represents it as 1 and false literal as 0 like in PHP/ C/ C++ language.

To proof that MySQL convert the BOOL or BOOLEAN to TINYINT(1), let us create a table with BOOLEAN or BOOL column.

Here, we are creating a table with column BOOLEAN. The query to create a table is as follows:

mysql> create table BoolOrBooleanOrTinyintDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> isvalidAddress BOOLEAN,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.74 sec)

Now check the DDL of the above table using SHOW CREATE command. The query is as follows:

mysql> show create table BoolOrBooleanOrTinyintDemo\G

The following is the output:

*************************** 1. row ***************************
Table: BoolOrBooleanOrTinyintDemo
Create Table: CREATE TABLE `boolorbooleanortinyintdemo` (
   `Id` int(11) NOT NULL AUTO_INCREMENT,
   `isvalidAddress` tinyint(1) DEFAULT NULL,
   PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Look at the column isvalidAddress, the datatype BOOLEAN is converted into tinyint(1) internally. Now you can check the true literal will be represented by 1 and false literal by 0. Insert some records in the table with true and false literal values. The query to insert record is as follows:

mysql> insert into BoolOrBooleanOrTinyintDemo(isvalidAddress) values(true);
Query OK, 1 row affected (0.43 sec)
mysql> insert into BoolOrBooleanOrTinyintDemo(isvalidAddress) values(false);
Query OK, 1 row affected (0.17 sec)
mysql> insert into BoolOrBooleanOrTinyintDemo(isvalidAddress) values(true);
Query OK, 1 row affected (0.29 sec)
mysql> insert into BoolOrBooleanOrTinyintDemo(isvalidAddress) values(false);
Query OK, 1 row affected (0.12 sec)
mysql> insert into BoolOrBooleanOrTinyintDemo(isvalidAddress) values(true);
Query OK, 1 row affected (0.33 sec)

Display all records from the table using select statement. The query to display all records is as follows:

mysql> select *from BoolOrBooleanOrTinyintDemo;

The following is the output:

+----+----------------+
| Id | isvalidAddress |
+----+----------------+
|  1 |              1 |
|  2 |              0 |
|  3 |              1 |
|  4 |              0 |
|  5 |              1 |
+----+----------------+
5 rows in set (0.00 sec)

Look at the above sample output, true represents as 1 and false represents as 0.

In PHP, if you use true then it will be represented as 1 and false will be represented as 0.

Look at the following PHP code. Here, I have set the variable ‘isValidAddress’. The value is 1, that means it evaluates the if condition true and execute the body of if statement only. Check the following code:

$isValidAddress = 1;
if($isValidAddress)
{
   echo 'true is represented as ';
   echo ($isValidAddress);
}
else
{
   echo 'false is represented as ';
   echo ($isValidAddress);
}

Here is the snapshot of code:

The following is the output:

If you change the variable ‘isValidAddress’ to value 0. That means it evaluates the if condition false and execute the body of else statement only. The following is the code:

$isValidAddress=0;
if($isValidAddress)
{
   echo 'true is represented as ';
   echo ($isValidAddress);
}
else
{
   echo 'false is represented as ';
   echo ($isValidAddress);
}

Here is the snapshot of code:

The following is the output: