Please help with processing a query

[rere]

I have a text area were I input 6 numbers (ex. 123456, 25698,12564,8899664, etc) the first problem is I would like for the numbers
to be displayed in a verical list (12345
12345
12345 etc)
The second problem is I am comparing the numbers in the list with numbers that are in a Database. I do know that 1 set of the
numbers exists, when I run my query with all the numbers being submitted I get no records found, however if I run the query with
only the set of numbers that is in the database i get 1 record found for 12345,

here is my code
<cfquery name="TRI">
Select IDnumbers from Process where IDnumbers like '%#DepID#%'
cfloop = "TRI" list="#DepID#"
cfoutput #Tri.Recordcount found #DepID#

DepID is the name given to the textarea were the numbers are posted.
I know the syntax in not correct I'm just trying to get an idea of what the problem is. and what direction should i be heading in

[SafariTECH]

Assuming that the list of numbers always has comma delimited entries your problem is with the LIKE processing

Lets say your list is 123456, 25698,12564,8899664

Your query is saying to look for numbers that look like "123456,25698,12564,8899664" ( the entire string) rather than looking for a number that is in that series of numbers.

You should be having it ask to find numbers that are identical to the ones in the list

try WHERE IDnumbers IN (#DepID#)


This may help:
http://www.sql-tutorial.net/SQL-IN.asp

[Dan_Bracuk]

quote:

---

Originally posted by: rere
I have a text area were I input 6 numbers (ex. 123456, 25698,12564,8899664, etc) the first problem is I would like for the numbers
to be displayed in a verical list (12345
12345
12345 etc)
The second problem is I am comparing the numbers in the list with numbers that are in a Database. I do know that 1 set of the
numbers exists, when I run my query with all the numbers being submitted I get no records found, however if I run the query with
only the set of numbers that is in the database i get 1 record found for 12345,

here is my code
<cfquery name="TRI">
Select IDnumbers from Process where IDnumbers like '%#DepID#%'
cfloop = "TRI" list="#DepID#"
cfoutput #Tri.Recordcount found #DepID#

DepID is the name given to the textarea were the numbers are posted.
I know the syntax in not correct I'm just trying to get an idea of what the problem is. and what direction should i be heading in

---

No offense, but you are doing something horribly wrong. Not sure if it is storing lists of numbers in a single field or storing numbers in char fields.

Fix your db and life will be much easier.

[Giles_B]

If I understand the question correctly, you are passing several numbers of variable length - not one number with up to 6 digits?

If that is right, your query will not find the record you are looking for because, to use your example, it would be trying to do the following:

SELECT IDnumbers
FROM Process
WHERE IDnumbers like '%123456, 25698,12564,8899664%'

I'm pretty sure you do not have any number in your database that looks anything like that! (commas in this case do not act as a seperator rather as part of the string that the query tries to match)

You would probably have much more luck if you used IN instead:

SELECT IDnumbers
FROM Process
WHERE IDnumbers IN (123456, 25698,12564,8899664)

So try something like this:

<cfquery name="TRI" datasource="MyDataSource">
SELECT IDnumbers
FROM Process
WHERE IDnumbers IN (#DeptID#)
</cfquery>
<!--- show number of matching records found --->
<cfoutput>
#TRI.RecordCount#
</cfoutput>
<!--- show matching numbers --->
<cfoutput query="TRI">
#TRI.IDnumbers#<br />
</cfoutput>

Hope that helps