Solved

cfloop quirk

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

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.

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…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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