Today I faced a quite interesting problem, that originated from pure laziness.
I’m developing a backend system for a quite complex database structure. Within this backend, an almost limitless amount of table views have to be created for the end user. Because I’m extremely lazy, and didn’t want to develop the html view code for each table view, I created a PHP html-table-generator-class, which takes a
mysql_result_set as parameter, and outputs the html table in string format.
This method works great, unless for some cases, where a value in the query has to be substituted by a user readable value. A boolean is a good example of such a value.
Booleans in MySQL
Since MySQL doesn’t natively support boolean values, there are various methods of saving booleans to a database. Common practices include fields of the format
ENUM('Y','N'). Recent versions of MySQL also include the
BIT field format, which in fact boils down to using
TINYINT(1), when it comes to boolean values.
I always use the
TINYINT(1) field format for my boolean columns. This works perfectly, since you can
INSERT true/false values, which are automatically transformed to 1/0. When you
SELECT a boolean column in PHP, this also works, since you can compare true/false to 1/0 within PHP. (Whether this loose type handling is preferable is a discussion for another time).
However.. when you
SELECT a boolean column, and run it through a generic html table generator class such as mine, you’ll end up with a nice table showing 1’s and 0’s, which aren’t very user friendly (Having in mind that most backend users aren’t quite as computer savvy as you are!). Replacing all 1’s and 0’s within the PHP code was not an option, since it would be a generic measure, and would also transform ID’s and Prices and such. So I had to come up with a solution to substitute the boolean values within the MySQL result by human readable values.
Substituting boolean values within a MySQL SELECT query
The solution is in fact, as they usually are, quite simple. MySQL supports the use of the
REPLACE() SQL command. Usually this command is used to
UPDATE tables with replacement values, but it also works within a
SELECT query to alter results. Having this in mind I started out replacing one value in the result:
This works! There’s a slight catch though, since ‘false’ values also have to be substituted. After some fiddling with
AND constructions, the solutions was of course a lot easier:
And there you have it.. value substitution 101 in MySQL. This method also works for your other substitution needs!