3 Replies - 2342 Views - Last Post: 02 February 2015 - 10:38 AM Rate Topic: -----

#1 rausten  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 31-January 15

Need help with using cfloop to build a keyword search query

Posted 31 January 2015 - 08:29 AM

I am trying to build a kind of keyword search based on one form field. I am looking for a way to generate the sql query using the space as a delimiter. I am trying to use use CFLOOP to loop over the list and that almost works but it generates a line for entire index at once. In this case I get:

SELECT [id] ,[spannedtext] ,[PMID_DOI] ,title ,class FROM [dbo].[Knowtator] WHERE 1 = 1 
OR( [spannedtext] like AT2G37630% ) 
OR( [spannedtext] like ATSUC2% ) 


The Problem is in the logic of the query I need the first WHERE clause in this case to be an AND and any subsequent keywords to be OR.

Like so:
SELECT [id] ,[spannedtext] ,[PMID_DOI] ,title ,class FROM [dbo].[Knowtator] WHERE 1 = 1 
AND ( [spannedtext] like AT2G37630% ) 
OR( [spannedtext] like ATSUC2% ) 


My Code:
SELECT [id]
      ,[spannedtext]
	  ,[PMID_DOI]
	  ,title
	  ,class
  FROM [dbo].[Knowtator]
    WHERE 1 = 1
<cfloop list="AT2G37630 ATSUC2" delimiters=" " index="word" >
		OR( [spannedtext] like #word#% )	
</cfloop>



Any help on how to do this would be highly appreciated.

Ross

Is This A Good Question/Topic? 0
  • +

Replies To: Need help with using cfloop to build a keyword search query

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1983
  • View blog
  • Posts: 3,522
  • Joined: 13-January 08

Re: Need help with using cfloop to build a keyword search query

Posted 31 January 2015 - 11:10 AM

Welcome to DIC rausten!

So, you're about 95% of the way there. You've already figured that you can insert CF code into the SQL statement in order to build dynamic SQL. You're just lacking the last part: changing the SQL operator from an AND to an OR after the first search.

Your code:
SELECT [id]
      ,[spannedtext]
	  ,[PMID_DOI]
	  ,title
	  ,class
  FROM [dbo].[Knowtator]
    WHERE 1 = 1
<cfloop list="AT2G37630 ATSUC2" delimiters=" " index="word" >
		OR( [spannedtext] like #word#% )	
</cfloop>



So, in your code you're on the money with the CFLOOP. All you need is some conditional code inside the loop to determine what iteration you're on within the loop. First iteration is an AND statement, everything is an OR, right?

Try this:
SELECT [id]
      ,[spannedtext]
	  ,[PMID_DOI]
	  ,title
	  ,class
  FROM [dbo].[Knowtator]
    WHERE 1 = 1
<cfset counter = 0>
<cfloop list="AT2G37630 ATSUC2" delimiters=" " index="word" >
     <cfset counter = counter + 1>
     <cfif counter EQ 1>
          AND( [spannedtext] like #word#% )
     <cfelse>
          OR( [spannedtext] like #word#% )
     </cfif>
</cfloop>



That's a very mechanical way of doing a conditional where statement per the loop iteration but this is easiest to see and understand if you're not accustomed. Try giving that a spin and see what you get.

Good luck!
Was This Post Helpful? 1
  • +
  • -

#3 rausten  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 31-January 15

Re: Need help with using cfloop to build a keyword search query

Posted 02 February 2015 - 03:40 AM

Thanks that worked perfectly.
Was This Post Helpful? 0
  • +
  • -

#4 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1983
  • View blog
  • Posts: 3,522
  • Joined: 13-January 08

Re: Need help with using cfloop to build a keyword search query

Posted 02 February 2015 - 10:38 AM

N/P.

As an aside, you may want to look at the grouping that that code produces for your where statement.

That is, your where statement could end up looking like this:

WHERE 1 = 1 AND( [spannedtext] like #word#% ) OR( [spannedtext] like #word#% ) OR( [spannedtext] like #word#% ) OR( [spannedtext] like #word#% )

Make sure that's the where statement you want (an AND statement followed by an ungrouped OR series like that).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1