We help IT Professionals succeed at work.

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

MaxwellTurner
on
Medium Priority
340 Views
Last Modified: 2013-12-24
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
Comment
Watch Question

Commented:
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>

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
expanding on the above answer...

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

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

Author

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

Commented:
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.
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.

Author

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!

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.