|
Returning values from mySQL in a select query as Yes/No rather than 1/0 |
||||||||
Whilst writing a ColdFusion based query to create a JSON response I thought I'd look up returning data values as a "Yes/No" string rather than "1/0". Then I wouldn't have to transform it in any way to use in the JSON build script.
The mySQL version allows you to do this by evaluating the value, and matching a string if the condition is met, like this:
1SQL version:
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, "Yes", "No")
3FROM table.users
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, "Yes", "No")
3FROM table.users
This does not work in ColdFusion at all. An error is thrown:
After a little tweaking it seems that if you alias the field it does work. In the example code below I've simply aliased the field with its own name.
I'm not exactly clear why, as the error message above isn't all that helpful.
1CF version:
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, 'Yes', 'No') as intactive
3FROM table.users
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, 'Yes', 'No') as intactive
3FROM table.users
TweetBacks
#yourQuery.intactive#
... and the "intactive" column didn't exist. This would make sense in the first demo since you're not aliasing a column, but rather, creating a completely new, calculated column based on the intactive column. If you dump out the query result, it probably gave it some crazy column name.
For example, if I run the query:
select IF( id = 3, 'a', 'b' ) from blog_entry LIMIT 5
... the column name comes back as "IF( id = 3, 'a', 'b' )"... funky!
So anyway, that's why the aliasing is crucial.