How to check for a null value from the return value in ColdFusion query loop

Question

<cfloop query="GET_ALL_STUDENTS>
 <cfif #student_id# is  NOT NULL>
 <!--- do something--->
 </cfif>
</cfloop>

Above is how I am looping my cf query which returns null value and I want to check if the student_id is null or not. This is what I have tried and it failed. Can anyone tell me a better way?

score 34 · Accepted Answer · edited Feb 09 '12 at 05:58

34

You can use your database's ifNull() or the like. However, in ColdFusion, queries are returned as strings. Given your situation, easiest way is to check for a non-empty string:

<cfif len(student_id)>

By the way, you don't need the pound signs inside of an evaluation: only when using a variable as a literal (such as when outputting)

edited Feb 09 '12 at 05:58

Leigh

28,765
10
55
103

answered Feb 09 '12 at 05:15

Billy Cravens

1,643
10
15

8

Small clarification, `null` values are converted to an empty string "" in queries. – Leigh Feb 09 '12 at 05:56
Example using Len() during inline evaluation of a query column for cfchartdata: `` – Trob Frank Sep 06 '22 at 14:06

score 15 · Answer 2 · edited May 11 '15 at 13:39

15

In Adobe ColdFusion 9, you can do:

<cfif IsNull(student_id)>
</cfif>

Or since you're doing the opposite:

<cfif NOT IsNull(student_id)>
</cfif>

edited May 11 '15 at 13:39

kguest

3,804
3
29
31

answered Feb 09 '12 at 05:10

NullyB

211
1
2

1

If the variable is a result from a database query, a null result will become an empty string in CF, then you have to use `len()`. – Roland May 22 '15 at 13:21

score 3 · Answer 3 · answered Feb 09 '12 at 21:39

It looks like the query is retrieving all of the students and then cfloops over the records to find the student_id fields that are NULL.

It would be more efficient to write a query that specifically queried the records that have student_id IS NULL.

The method of grabbing all the student table records will work great when you have 100 or so students. What happens when it is put into production and there are 25,000 students?

Pak Chin Wong · Answer 4 · 2020-01-21T07:02:44.540

0

While the java class of CFQuery object (coldfusion.sql.QueryTable) will return empty string for any null value, it's parent class coldfusion.sql.Table is providing a method getField(row, column) to access the query table values directly, which return "undefined" if the value is null. We can make use of the IsNull to identify the "undefined" hence able to detect NULL.

<CFLOOP query="GET_ALL_STUDENTS">
    Row = #CurrentRow#
    <CFIF IsNull(GET_ALL_STUDENTS.getField(GET_ALL_STUDENTS.CurrentRow, GET_ALL_STUDENTS.findColumn('student_id')))>
        [NULL]
    <CFELSE>
        #GET_ALL_STUDENTS.student_id#
    </CFIF>
    <br>
</CFLOOP>

Reference: http://laxmanthota.blogspot.com/2010/11/cfquery-and-underlying-java-objects.html

edited Jan 21 '20 at 07:02

answered Jan 20 '20 at 14:48

Pak Chin Wong

1
1

While this code snippet might solve the issue and provide some limited, immediate help. A [proper explanation](https://meta.stackexchange.com/q/114762/349538) would greatly improve its long-term value by showing why this is a good solution to the problem and would make it more useful to future readers with other, similar questions. Please consider an [edit] your answer to add some explanation, including the assumptions you’ve made. – ButchMonkey Jan 20 '20 at 15:02
Please add some explanation to your answer. – Run_Script Jan 20 '20 at 15:24

How to check for a null value from the return value in ColdFusion query loop

4 Answers4