MySQL Boolean Type

Question

I am really torn on what to do with this. Over the past 10 years, I just agreed with this, but I think it's time to decide the right way to solve this problem.

According to the internet, MySQL does not support its own data type, which makes sense why, when I select "BOOLEAN", I see that the column ends as tinyint (1).

I have no problem storing data like tinyint with a 1 or 0 value, however I have a problem with the fact that when I select a lot of rows from the database with this column, I get the string "1" or "0" instead of PHP native bool true / false. Of course, PHP can treat this variable as a boolean, because it seems to automatically know what I want to do when I do == 0 or == 1 or even == '0' or == '1' if I "I really feel it."

So here I go, take this data and run it in the PHP json_encode function and output it when the Backbone / Handlebars implementation makes an ajax call and gets a beautiful beautiful JSON object (usually with many string data in it) with this: {"myVariable":"1"} or this is {"myVariable":"0"} .

I am sure you can see the problem with the desire to do something like this in Handlebars.js:

 {{#if myVariable}} Do this {{else}} Do that {{/if}} 

Now I really DO NOT want to do something like this:

 <?php for($i = 0; $i < count($recordSet); $i++) { $recordSet[$i]['myVariable'] = (bool)$recordSet[$i]['myVariable']; } ?> 

Being that the application I am creating contains thousands of columns in a database that I cannot (or just have to) just run the above code for each column that I want to maintain as bool in my javascript. And I really don't want to do this (although it probably would have been easier)

 Handlebars.registerHelper('ifTrue', function(a, b) { // Cast the compared value to a bool and then run a regular handlebars #if statement }); 

Is there a better way? I am using a code igniter ... can the database class be modified in some way to detect a tinyint (1) column and automatically drop it in the bool, and then just do it until MySQL supports the true Boolean column? I know that they are planning this, but ...

Answer 1

Keep in mind that by default, the column values ​​returned by the PHP / MySQL drivers are just strings (i.e. untyped arrays of bytes). This short snippet demonstrates this:

 // mysqli driver $c = mysqli_connect ($host, $user, $pass, $dbname); $r = mysqli_query($c, 'SELECT * FROM foo'); $f = mysqli_fetch_array($r); foreach ($f as $k => $v) { echo "$k => $v [" . gettype($v) . ']' . PHP_EOL; } // PDO $c = new PDO("mysql:dbname=$dbname;host=$host", $user, $pass); $r = $c->query('SELECT * FROM foo'); $f = $r->fetch(); foreach ($f as $k => $v) { echo "$k => $v [" . gettype($v) . ']' . PHP_EOL; } 

By default, because you can provide the driver with a mapping so that it can use these strings in native PHP types. For this purpose, mysqli has mysqli_result::fetch_object() , PDO has PDOStatement::fetchObject() .

Although you can typically return the actual type of a column from its metadata or even from a table definition, the client application knows this in advance.

Bottom line: you want to do this (or something similar):

 $recordSet[$i]['myBoolColumn'] = (bool)$recordSet[$i]['myBoolColumn']; 

... because you really have to do this too:

 $recordSet[$i]['myIntColumn'] = (int)$recordSet[$i]['myIntColumn']; 

But PHP is very laxist regarding typing, I would not worry too much about it, after all.