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?

Solution 1

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)

Solution 2

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>

Solution 3

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?

Comments

[Leigh]

Small clarification, null values are converted to an empty string "" in queries.

[Roland]

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().

[ButchMonkey]

While this code snippet might solve the issue and provide some limited, immediate help. A proper explanation 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.

[Run_Script]

Please add some explanation to your answer.