Article Image
read

While inserting null for values while using cfqueryparam has been written about before, I'd like address the specific case of lists. When I say lists, I'm referring to (in MySql) queries that use something along the lines of

SELECT this, that FROM table WHERE id IN (12, 13, 29, 56)

So the paramerterized version would look like this:

<cfset id_list = "12,13,29,56">
<cfquery name="aQuery" datasource="dsn">
Select this, that
From table
Where id in
(
<cfqueryparam value="#id_list#" >
)
</cfquery>

That looks fine and dandy, unless your list (id_list in this case) is null or empty. Coldfusion 6 doesn't insert a null for you automatically, which causes MySql to throw an error. To remedy this, I employed a solution similar to the null-ifying of the parameter. The addition being that the list parameter also needs to be set inversely to the null parameter. So if the list parameter is hard coded to true, null will not be inserted.

<cfset id_list = "">
<cfquery name="aQuery" datasource="dsn">
Select this, that
From table
Where id in
(
<cfqueryparam value="#id_list#"
list="#LEN(id_list) GT 0#"
null="#LEN(id_list) EQ 0#"
>
)
</cfquery>

Note: It looks like CF8 works fine without this method.

Links:
cfqueryparam and conditional handling of NULL’s

Blog Logo

Jon Dowdle

Jon Dowdle currently works on the internet at a lovely place called InVision.


Published

Color

Jon Dowdle's Blog

The personal blog of Jon Dowdle.

Back to Overview