Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Filter a CFQUERY using a list of data separated by a semi-colon

I have a pretty standard form where the user can enter data into one or more of several text boxes and search a database for info.  I want to take this further by allowing the user to enter more than one search parameter within one text box separated by semi-colons.  The best way to explain this is to show a simple example:

EG.  Currently the user can enter all or part of an item number of a product.  In the cfquery I use:

WHERE itemno like '%#item_number#%' . . . .
. . . . So, if they entered "12345" into the text box, the query would search for and return any item number containing "12345"

WHAT I NEED:  is for the user to be able to enter more than 1 item number within the same text box separated by semi-colons:

"12345;56789;10293"

How do I change my "WHERE" statement in the query so it will search for each item number found between the semi-colons?  I know a little about lists, but cannot get this to work.

Max
0
MaxwellTurner
Asked:
MaxwellTurner
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
tim_csCommented:
What if you just looped through the list?  Something like this??


<cfloop    index = "item_number"   list = "item_list"   delimiters = ";">
OR itemno like '%#item_number#%'


 </cfloop>

0
 
danrosenthalCommented:
expanding on the above answer...

SELECT ...
FROM ...
WHERE
0=1
<cfloop    index = "item_number"   list = "item_list"   delimiters = ";">
OR itemno like '%#item_number#%'

0
 
JeffHowdenCommented:
This isn't ideal as it leaves you potentially open for SQL injection attacks, but if it's in a closed system where that concern is minimal or non-existant, you could try something like this:

WHERE itemno IN ('#Replace(item_number, ";", "', '", "ALL")#')

If, instead, you need it to be as secure as possible, try something like this:

WHERE itemno IN (
  <cfqueryparam value="#item_number#" cfsqltype="CF_SQL_NUMERIC" separator=";" list="yes">
)
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
JeffHowdenCommented:
Actually, my suggestion isn't going to do LIKE comparisons.  So, if that's important to do, you'll need to use a loop with an OR condition.
0
 
danrosenthalCommented:
Here is my suggestion which is more complete than my last post and addresses the security concern.

<CFQUERY datasource="#dsn#" name="queryname">
      SELECT *
      FROM tablename
      WHERE 0=1
      <cfloop    index = "item_number"   list = "#item_list#"   delimiters = ";">
            OR item_no like <CFQUERYPARAM value="%#item_number#%" cfsqltype="CF_SQL_VARCHAR">
      </cfloop>
</CFQUERY>
0
 
MaxwellTurnerAuthor Commented:
Thanks for the replies.  I was side-tracked for abit, but I am going to try tackle this now.

I want it to remain possible to just do a simple LIKE comparision as well as provide the option for retrieving multiple paramters with a semi-colon delimiter.  The WHERE portion of my query is massive (about 1500 lines) so this is going to be a little bit of work.

Dan - What is with the "WHERE 0=1" - what is the 0=1 for?

Max
0
 
tim_csCommented:
Without the 0=1 to start off the where clause's the first thing you would end up with when the loop first ran would be

Where OR item_no like...etc which wouldn't work.  


so you start off with Where 0=1 so you can get

Where 0=1 OR item_no like... which will work.


You want to use "where 0=1" whenever you use OR's and "where 1=1" whenever you use AND's.
0
 
JeffHowdenCommented:
This 1 = 0 and 1 = 1 advice all hinges on whether or not you have existing conditional statements you need to add these bits too.  If you do, ignore the advice of 1 = 0 and 1 = 1 as it's completely unnecessary.
0
 
MaxwellTurnerAuthor Commented:
I get it now - "0=1" - I should have figured that one out!  I didn't look close enough at the code inside the loop before I asked that.

Sorry I have not replied here for the last few days - I got hammered by a virus and had to take care of that first.  

I have never used an index loop before, but it worked perfectly!

Thanks a million guys - I would have never figured that out on my own.  I'll split the points!

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now