Multiple search items

[thysonj]

I am developing a CF application that needs to allow a user to search for certain products in a db. I want to be able to let them enter multiple product numbers in a text field and then search for them. I am having a lot of trouble getting anything to work although I believe I have a good idea how it should work. If someone could post n idea and some example code that would be great.

Thanks

 

[Silvano]

those multiple product numbers will be entered in some kind of a list, right?
if so, you can use IN and ListQualify() function to extract only records that id's is found in the form field; I have removed all the blank spaces and used just "" as list qualifier; it works just fine...

SELECT *
FROM Products
WHERE productID IN (#ListQualify(form.productID, "&quot#); Sylvano
dsylvano@hotmail.com

 

[thysonj]

Okay...
Thats seems easy enough but where I am having real trouble is with each number.
The stock numbers can be entered in two fasions really.
First of all the numbers are really two numbers concatinated.
example: 1234-123456789
Now I would like them to be able to search for the first 4 numbers or for the second 9 nine numbers or all the numbers. So if they were to enter a list like 2324,7869987656765,989767547,9890,987687678,etc...
it would find every number beginning with 2324, the concatinated number 7869987656765 etc...
How can I parse this and where should I store the numbers? I think I can figure out how to perform the search but I need to get the search string into the right form first.

 

[CFDude]

I'm a little lost on your examples, but, to make it simple, if they were to enter only 1 number like 7869987656765, you would want to search on:
7869
987656765
and
7869987656765 ?

So would 7869 be a (possible) product?

To use webmigits example, you can loop through the list of values, and create a new list:

<cfset newStockList = &quot;&quot;>
<cfloop list=&quot;#Form.StockNumberList#&quot; index = &quot;idx&quot;>
<!--- Get the first 4 digits --->
<cfset newStockList = ListAppend(newStockList, Left(idx,4)>
<!--- Get the last 9 digits --->
<cfset newStockList = ListAppend(newStockList, Right(idx,9)>

</cfloop>

Then query using the new list:
SELECT *
FROM Products
WHERE productID IN (#newStockList#)

(You really don't need the ListQualify, unless the productID is a string field, in that case, you could use it to put single quotes around each value:
WHERE productID IN (#ListQualify(newStockList,&quot;'&quot#)

 

[thysonj]

I think that will help me out a lot...thanks

 

[thysonj]

Any idea why I get a...

Just in time compilation error

Invalid parser construct found on line 4 at position 59. ColdFusion was looking at the following text:

>
Invalid expression format. The usual cause is an error in the expression structure.

with the above code?

 

[ingernet]

you've probably already figured it out, but you're missing a nested parenthesis on both line 4 and line 6. it should read:

<cfset newStockList = &quot;&quot;>
<cfloop list=&quot;#Form.StockNumberList#&quot; index = &quot;idx&quot;>
<!--- Get the first 4 digits --->
<cfset newStockList = ListAppend(newStockList, Left(idx,4))>
<!--- Get the last 9 digits --->
<cfset newStockList = ListAppend(newStockList, Right(idx,9))>

hope this helps!
inger