Best practice for using boolean with MySQL and JSON

Question

Are there any suggestions on how to best handle the fact that neither MySQL nor JSON have a boolean data type? Right now I'm thinking of always just using 0 and 1 instead of 0 and 1, because that would be at least consistent.

As I see all other possibilities have severe downsides. When I use php to read a MySQL-Record and send it to JavaScript via AJAX in JSON format - I just receive a 0 or 1 there anyway. If I send JSON to php I would receive a string "true" or "false" there - which I would have to query via string in php (also not a nice solution).

My solution of only using 0 and 1 has a downside too, but I consider that a smaller one: When I have a JavaScript function with a bool input that has to be transferred to php I would already have to use 0 and 1 there (which is not nicely readable) - or I would have to translate the values in the function before the JSON encoding. Still I prefer both variants to querying "if (mybool == 'true') in php.

Any suggestions are greately appreciated.

Answer 1

neither MySQL nor JSON have a boolean data type

JSON does support boolean as a type natively.

MySQL provides BOOLEAN or BOOL as the synonym of TINYINT(1). It also provides convenient TRUE/FALSE constants. So even though MySQL internally uses a TINYINT(1) to store boolean values, from your point of view that shouldn't matter. You just need to read the MySQL value as a boolean, instead of an integer.

When I have a JavaScript function with a bool input that has to be transfered to php I would already have to use 0 and 1 there

What do you mean by "transfered to php"? If you mean that PHP code will be used on the server to handle an AJAX request, there is no problem. PHP also supports boolean natively, so if you send a JSON request containing boolean values, they will be deserialized into a PHP data structure containing booleans as well.