3 Replies Latest reply on Jun 12, 2006 4:36 AM by Giles_B

    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
        • 1. Please help with processing a query
          SafariTECH Level 1
          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

          • 2. Re: Please help with processing a query
            Dan Bracuk Level 5
            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.
            • 3. Re: Please help with processing a query
              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