Solved

cfloop quirk

Posted on 2006-06-22
8
296 Views
Last Modified: 2013-12-24
I am trying to get this query to loop through the database to see if each farmid is being used by any other user.  The farmid column is a comma delimited list of names.  It correctly identifies things added to the end of the list, but if I add a name in the middle of the list it doesn't give me an error.  Please look over my code and tell me where the problem might be.

<cfloop index="checkfarmloop" list="#form.farmid#" delimiters=" ,">
<cfquery name ="checkfarm" datasource="dairy">
SELECT farmid
FROM Dairy.dbo.users
WHERE userid > '#session.userid#'
AND farmid like '%#checkfarmloop#%'
OR userid < '#session.userid#'
AND farmid like '%#checkfarmloop#%'
</cfquery></cfloop>
<cfif checkfarm.recordcount GTE 1>
<cfoutput>
  <b>The farmid #checkfarmloop# is already being used by another user, please choose another FarmID.
cfoutput>
<cfelse>
code to update database.
0
Comment
Question by:muellertj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 7

Expert Comment

by:aseusainc
ID: 16962752
Change up your query a bit. Not sure how you're wanting to compare userid and the session.userid, so I guessed...

instead of using LIKE, use IN.

<cfloop index="checkfarmloop" list="#form.farmid#" delimiters=" ,">
  <cfquery name ="checkfarm" datasource="dairy">
    SELECT farmid
    FROM Dairy.dbo.users
    WHERE userid = '#session.userid#'
    AND farmid IN ('#checkfarmloop#')
  </cfquery>
</cfloop>
<cfif checkfarm.recordcount GTE 1>
  <cfoutput>
    <b>The farmid #checkfarmloop# is already being used by another user, please choose another FarmID.
  </cfoutput>
<cfelse>
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16962763
Oops...goofed....dont loop..

<cfquery name ="checkfarm" datasource="dairy">
    SELECT farmid
    FROM Dairy.dbo.users
    WHERE userid = '#session.userid#'
    AND farmid IN ('#form.farmid#')
  </cfquery>
<cfif checkfarm.recordcount GTE 1>
  <cfoutput>
    <b>The farmid #checkfarmloop# is already being used by another user, please choose another FarmID.
  </cfoutput>
<cfelse>
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16962780
Fixed because I goofed again..


  <cfoutput>
    <b>The farmid #checkfarm.farmid# is already being used by another user, please choose another FarmID.
  </cfoutput>
<cfelse>
0
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 

Author Comment

by:muellertj
ID: 16962964
I don't think I was clear enough in my original post.  I am actually comparing individual items from one comma delimited list to all the other comma delimited lists in the database to see if any of the individual items match.  

So my list may be:  one, two, three, four
and I need to see if any of those values match any other single value in someoine elses list.  Another list in the database may be: four, five, six, seven.  

When I submit my list I want it to tell me that four is already being used.  
0
 
LVL 7

Accepted Solution

by:
aseusainc earned 400 total points
ID: 16963352
<!--  the WHERE clause lets the user reuse one of their OWN farmid's.  If you dont want them to, just remove the WHERE clause  -->
<cfquery name ="checkfarm" datasource="dairy">
    SELECT farmid
    FROM Dairy.dbo.users
    WHERE userid <> #session.userid#
</cfquery>
<cfset match = 0>
<cfoutput query="checkfarm">
  <cfloop list=#checkfarm.farmid# index=x>
    <cfif listfind(#form.farmid#,#x#)>
        <cfset match = 1>
        <cfset thematch = "#x#">
      </cfif>
  </cfloop>
</cfoutput>
<cfif match EQ 1>
  <cfoutput>
    The farmid "#thematch#" is already being used by another user, please choose another FarmID.
  </cfoutput>
<cfelse>
whatever
</cfif>
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16963362
Also, if your lists really have spaces in them like you showed above, if they aren't 100% consistant, you may want to toss a few trim() in the logic...
0
 

Author Comment

by:muellertj
ID: 16963616
Do you have a reccomendation on where I put the trim statments?
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 16963702
Thinking about my own statement, it's kind of hard to just toss it in there in the middle of the game.  As long as you adhere to a standard, you'll be fine as is.  Otherwise, you may want to A) parse over your db and remove unwanted spaces, and B) avoid adding spaces when creating #form.farmid#.

The reason for this is because I'm pretty sure that "one,two,three" <> "one, two, three".  I could be wrong though, I've never actually tested it.  Let us know if you ever run into anything that either proves or disproves my theory.
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question