Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

cfloop quirk

Posted on 2006-06-22
8
Medium Priority
?
301 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
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 1600 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

972 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