Skip to main content
2Charlie
2Charlie
Inspiring
November 2, 2015
Question

How to use variable in cfQuery where?

  • November 2, 2015
  • 2 replies
  • 4107 views

I have this variable:

strURL = siteURL not like '/training%' and siteURL not like '/webadmin%'

Now in my cfquery:

<cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

  select *   from qryContent where #strURL#

</cfquery>

And it failed to execute. I put a log after the cfquery and it failed to generate anything too.

This topic has been closed for replies.

2 replies

T
tribule
Legend
November 2, 2015

Try:

<cfset strURL = "siteURL not like '/training%' and siteURL not like '/webadmin%'">

<cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

  select * from qryContent where #strURL#

</cfquery>

You need to make the strUrl a complete string.

V
vermasumit
Participating Frequently
November 2, 2015

Is there any reason why you are using the variable? Ideally you should be doing this:

<cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

  select *   from qryContent

  where siteURL not like <cfqueryparam value="/training%" cfsqltype="cf_sql_varchar" />

  and siteURL not like <cfqueryparam value="/webadmin%" cfsqltype="cf_sql_varchar" />

</cfquery>

2Charlie
2CharlieAuthor
Inspiring
November 2, 2015

Yes. Here's larger view of the whole process. I was thinking about putting the <cfloop> inside of the cfquery in the where clause but also causes issue too.

<cfset todayDate = DateFormat(Now(), "mm/dd/yyyy")>

<cfset strURL = "">

<cfscript>

    // get data from the "My Element" custom element

    data = application.ADF.ceData.getCEData(customElementName="Subsite Exclusion");

  </cfscript>

  <cfloop from="1" to="#arrayLen(data)#" index="itm">

  <!---// renders data from the uniqueID and Title fields from the element --->

  <cfif #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# gte #todayDate# || #DateFormat(data[itm].values.ExpirationDate, "mm/dd/yyyy")# is "">

  <cfif #itm# eq 1>

  <cfscript>

    strURL = "subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

    </cfscript>

  <cfelse>

  <cfscript>

    strURL &= " and subSiteUrl not like '" & #data[itm].values.SubsiteURL# & "'";

    </cfscript>

  </cfif>

  </cfif>

  </cfloop>

  <cfquery dbtype="query" name="qryContentFiltered" result="tmpResult">

  select *

  from qryContent

  where #strURL#

</cfquery>

T
tribule
Legend
November 2, 2015

Building dynamic queries can be tricky - you just need to ensure that you start out with a base query and then add the AND conditions on to the end at each stage, thus ensuring the SQL is syntactically correct. Why not just output the SQL without running it in a CFQUERY to see if all looks good, first? Can't help you do that; that's just something you'll have to figure out in the coding

Btw, don't use CFSCRIPT if you don't need to. Going into CFSCRIPT mode just to set a variable instead of using CFSET is overkill. Also you don't need <cfif #itm# eq 1>, you just need <cfif itm eq 1> etc - the #'s are superfluous inside CF tags, as the values are already being evaluated.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices