CFDIRECTORY - Count files in each directory

[weezerboy]

I want to count the number of files in each and every directory in my website. I don't want to list the files but just get a count of all files in each directory.

Not a sub directory count but a file count in each directory

For example using my C:\Windows\ directory as the directory where the files in each and every directory are to be counted

Like this

Directory Name	File Count	Date Last File Modified
C:\Windows\ADFS\ar	1	12-12-2016
C:\Windows\ADFS\bg	2	12-12-2016
...................
C:\Windows\WinSxS	81	12-14-2016
C:\Windows\WinSxS\x86_microsoft-windows-deviceaccess.resources_31bf3856ad364e35_6.3.9600.16384_en-us_21effc1389c6f3e1	1	12-14-2016

I need the count of the files in each directory. Every row in the table would be the next directory as you drill down.

[WolfShade]

One way you could do it is with a query-of-a-query, I suppose.  When you use CFDIRECTORY to list files and folders, the object that is returned is a query-like object (or a query object, I can never remember.)  But you can use CFDIRECTORY to recursively get all files and folders under a specific directory, which would return everything, then use a QoQ to selectively get that information.

So, for example, if you were to recursively look in C:\wwwroot.  You would get an object that can be accessed like a query object.

<cfdirectory action="list" directory="C:\wwwroot" name="folders" />
<cfquery dbtype="query" name="QueryOfDirectories">
SELECT DISTINCT f.name, (SELECT COUNT(z.name) FROM folders z WHERE z.name = f.name and type='file') as filecount
FROM folders f
WHERE f.type='dir'
GROUP BY f.name, filecount
ORDER BY f.name
</cfquery>

This isn't tested, I don't have that much free time, but this should get you in the right direction.

HTH,

^_^

[WolfShade]

I forgot to add the recurse="yes" attribute to the CFDIRECTORY tag, so it gets all child folders.

^_^

[weezerboy]

Thank you for your help.

I am getting this error?

Error Executing Database Query.<br><b>Query Of Queries syntax error.</b><br> Encountered ""SELECT. Incorrect Select List, The specific sequence of files included or processed is: E:\inetpub\wwwroot\cf\directory.cfm, line: 12

Is (SELECT COUNT(z.name) FROM folders z WHERE z.name = f.name and type='file') as filecount 
proper SQL syntax?

[WolfShade]

Not sure why you're getting that error.  I'm looking into a super simple way to do what you want, but it's not co-operating with me. 

BRB,

^_^

[WolfShade · Accepted solution]

Okay.. finally got it..

<cfdirectory action="list" recurse="yes" directory="C:\wwwroot" name="folders" sort="directory ASC" />

<cfoutput query="folders" group="directory">
   #folders.directory#<cfset thisCount = 0 />
   <cfoutput><cfif lcase(trim(folders.type)) eq "file"><cfset thisCount++ />  - #folders.name#<br /></cfoutput>
   #thisCount# files...<br /><br />
</cfoutput>

This will list each directory, which will be followed by a list of each file in that directory, followed by the number of files in that directory.

HTH,

^_^

[weezerboy]

THANK YOU

[WolfShade]

Thank you for marking my answer as correct.  I do appreciate it, and I'm sure others who have the same question will, too.

V/r,

^_^