Solved

cfloop quirk

Posted on 2006-06-22
8
294 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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…

777 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