Shaun Mccran

My digital playground

05
M
A
R
2010

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:

view plain print about
1SQL version:
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.

view plain print about
1CF version:
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, 'Yes', 'No') as intactive
3FROM     table.users

TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Ben Nadel's Gravatar I've seen that error many times before :) In my experience, any time I have an error that says "not indexable by", it means that I tried to access a column name that didn't exist (typically because I misspelled it). You probably tried to do:

#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.
# Posted By Ben Nadel | 12/03/10 22:25
dr david caminer plastic surgeon's Gravatar 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.
# Posted By dr david caminer plastic surgeon | 03/10/15 07:04
sydney seo company's Gravatar I've seen that error many times before :) In my experience, any time I have an error that says "not indexable by", it means that I tried to access a column name that didn't exist (typically because I misspelled it). You probably tried to do:
# Posted By sydney seo company | 12/10/15 02:34
Build My List 20's Gravatar I have an error that says "not indexable by", it means that I tried to access a column name that didn't exist (typically because I misspelled it). You probably tried to do:
# Posted By Build My List 20 | 05/11/15 23:14
taxi for delhi jaipur's Gravatar , it means that I tried to access a column name that didn't exist (typically because I misspelled it). You probably tried to do:
# Posted By taxi for delhi jaipur | 30/11/15 04:03
buy instagram likes's Gravatar this wonderful post that you have provided for us. I assure this would be beneficial for most of the people.
# Posted By buy instagram likes | 18/12/15 21:41
cccam server's Gravatar I want you to thank for your time of this wonderful read!!! I definitely enjoy every little bit of it and I have you bookmarked to check out new stuff of your blog a must read blog....
# Posted By cccam server | 18/12/15 23:47
gibbson's Gravatar Trying different ways is always noisy but gives innovative thoughts. Returning values as YES and NO rather than binary is sounding cool, and well explained just like i have referred from http://pay4essayonline.blogspot.com/2015/12/avoid-... . The information is good and please give some more examples. Please give as an information on this purpose so that it would be more helpful.
# Posted By gibbson | 21/12/15 01:26
cccam server's Gravatar It's my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles...
# Posted By cccam server | 22/12/15 02:38
cccam server's Gravatar I want you to thank for your time of this wonderful read!!! I definitely enjoy every little bit of it and I have you bookmarked to check out new stuff of your blog a must read blog....
# Posted By cccam server | 22/12/15 04:02
Back to top