Solved

Using CFDIRECTORY, CFLOOP, CFQUERY causes the NAME variable to become static inside a CFQUERY.

Posted on 2003-10-23
7
1,926 Views
Last Modified: 2013-12-24
00  <cfdirectory action="list" directory="\\teletran\stockphotography" name="StockDirectories">
01  <cfloop query="StockDirectories">
02   <cfif StockDirectories.Type is "DIR">
03   <cfoutput>#Name#</cfoutput><br>
04    <cfdirectory action="list" filter="*.jpg" directory="\\teletran\stockphotography\#StockDirectories.Name#\HighResolutionVersions" name="CurrentSelectedDirectory">
05  
06    <cfoutput query="CurrentSelectedDirectory">
07  
08    <cfquery name="LookForFile" datasource="#dn#" username="#un#" password="#pw#">
09    select *
10    from Stock
11    where ImageFileName='#Name#' and SourceFolder='#Name#'
12    </cfquery>
13              
14    <cfif LookForFile.RecordCount is 0>
15                    
16     <cfquery name="InsertTheFile" datasource="#dn#" username="#un#" password="#pw#">
17     insert into Stock (ImageFileName,SourceFolder,FileSize)
18     values ('#Name#','#StockDirectories.Name#','#Size#')
19     </cfquery>
20                          
21     <cfset FilesInserted=FilesInserted+1>
22     <cfelse>
23      <cfset FilesIgnored=FilesIgnored+1>
24     </cfif>
25              
26     </cfoutput>
27    </cfif>
28   </cfloop>
0
Comment
Question by:tomkinite
7 Comments
 
LVL 1

Author Comment

by:tomkinite
ID: 9608149
Line numbering would be an awesome feature for EE! But, that's not my problem right now.

Here's the application.

On line 00 I read the contents of the directory (stockphotography). In this directory contains hundreds of sub directories, each contain hundreds of JPEGS.  Through each loop of the StockDirectories query (CFDIRECTORY) I then do another loop to list JPEGS (CFDIRECTORY).  

The loop checks to see if the file exists in the database (line 08 to 12) and if it doesn't, it inserts it into the database (line 16 to 19).  

Here's the problem.

On line 03 you can see that I output the #Name# variable from StockDirectories perfectly fine.  The output is exactly what is expected.  On line 18 I use this very same variable but it now mysteriously static to the first folder's name in the CFDIRECTORY query.  Why is it looping as normal anywhere else (I have put the output of #NAME# inside of the loop as well) but when I use CFQUERY to insert the value of #NAME# it thinks it is the first folder?
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9608242
What happens when you scope the Name variable with the query name?  IE:

06    <cfoutput query="CurrentSelectedDirectory">
07  
08    <cfquery name="LookForFile" datasource="#dn#" username="#un#" password="#pw#">
09    select *
10    from Stock
11    where ImageFileName='#CurrentSelectedDirectory.Name#' and SourceFolder='#StockDirectories.Name#'
12    </cfquery>
13            
14    <cfif LookForFile.RecordCount is 0>
15                
16     <cfquery name="InsertTheFile" datasource="#dn#" username="#un#" password="#pw#">
17     insert into Stock (ImageFileName,SourceFolder,FileSize)
18     values ('#CurrentSelectedDirectory.Name#','#StockDirectories.Name#','#Size#')
19     </cfquery>
20                      
21     <cfset FilesInserted=FilesInserted+1>
22     <cfelse>
23      <cfset FilesIgnored=FilesIgnored+1>
24     </cfif>
25            
26     </cfoutput>
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9608256
Oops, forgot to post which lines I modified. 11 and 18
0
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.

 
LVL 25

Expert Comment

by:James Rodgers
ID: 9608285
save the following as a cfm called

dirInsertRecursive.cfm

<cfif isDefined('attributes.dirPath')>
      <cftry>
            <cfset path=#expandPath(#attributes.dirPath#)#>
      <cfcatch>
            <cfset path=#attributes.dirPath#>
      </cfcatch>
      </cftry>
      <cfif findNoCase(rootPath,#getDirectoryFromPath(path)#,1)>
      <cfif right(path,1) eq '\'>
            <cfset path=left('#attributes.dirPath#',len('#attributes.dirPath#')-1)>
      <cfelse>
            <cfset path=#attributes.dirPath#>
      </cfif>
<cfelse>
      <cfset path=rootPath>
</cfif>
</cfif>
<cfdirectory action="list" directory="#path#" name="fileList" sort="type,name">
<cfloop query="fileList">
<cfif #type# is "dir" and #name# neq "." and #name# neq "..">
<CF_dirInsertRecursive dirPath= "#attributes.dirPath#\#name#">
<cfdirectory action="delete"  directory="#attributes.dirPath#\#name#">
<cfelseif #type# eq "file">
#name#<br>
<!---
      <cfquery name="LookForFile" datasource="#dn#" username="#un#" password="#pw#">
    select *
    from Stock
    where ImageFileName='#Name#' and SourceFolder='#Name#'
   </cfquery>
           
    <cfif LookForFile.RecordCount is 0>
                 
    <cfquery name="InsertTheFile" datasource="#dn#" username="#un#" password="#pw#">
     insert into Stock (ImageFileName,SourceFolder,FileSize)
     values ('#Name#','#StockDirectories.Name#','#Size#')
     </cfquery>
                     
     <cfset FilesInserted=FilesInserted+1>
     <cfelse>
      <cfset FilesIgnored=FilesIgnored+1>
     </cfif>
       --->    
     </cfoutput>

</cfif>

</cfloop>

and call it from another cfm as

<cf_dirInsertRecursive.cfm dirPath="\\teletran\stockphotography">

test it in a small directory structure first, this will just list the contents, to do the insert remove the comments and delete or comment out the  #name#<br> line

0
 
LVL 2

Accepted Solution

by:
jonnygo55 earned 500 total points
ID: 9608355
I have come across this before...
I cfoutput of a query inside a cfloop causes the cfloop not to advance as expected it seems...

one thing that worked for me was after the cfloop and before the cfoutput to set the qurey variable into another temp variable:

<cfloop query="StockDirectories">
<cfset thisDir = StockDirectories.name>

and then to refrerence #thisDir# on line 18 within the cfoutput of the query...
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9608862
i agree with jonnygo - i had done a similar workaround & it did the trick !

u cld try changing the code in ur line 6 from CFOUTPUT to CFLOOP & see if it helps
else what jonnygo suggested shld help u out for sure.

K'Rgds
Anand
0
 
LVL 10

Expert Comment

by:Mause
ID: 9609196
Also had that problem and solved it like jonnygo55 did

Anand cfloop inside a cfloop will still give the same problem....
well in CF5, not test it in MX yet.

Mause
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

825 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