Solved

cfloop quirk

Posted on 2006-06-22
8
293 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Suggested Solutions

Title # Comments Views Activity
Redirect website ! 4 52
Help Fix A 403 error 13 103
how to setup ssl access to internal local machine 4 109
System Analysis 5 51
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
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.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now