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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
)
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"
)
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
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!
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!
SELECT ...
FROM ...
WHERE
0=1
<cfloop index = "item_number" list = "item_list" delimiters = ";">
OR itemno like '%#item_number#%'