[midasxl]

Hello and thanks for your time!

I have a cfquery that returns over 3000 records from a database. The specific column I am working with is called FILEPATH and it does indeed include file paths to a variety of files (.pdf, .txt, .doc, etc.)

Here are a couple of example results

share/open/global/robots.txt
share/open/global/recipes.txt
share/open/global/movies.txt
share/open/public/cars.txt
share/open/public/bikes.txt
share/open/public/desserts.txt
share/internal/file.txt

I am trying to parse out everything to the right of the last "/" in the string. So I'm looking to gather...

robots.txt
recipes.txt
movies.txt
cars.txt
etc.

I am able to use the following to create an array

<cfset theString="#checkDb.FILEPATH#">
<cfset theArray=ListToArray(theString, "/")>
<cfdump var=#theArray[4]#>

Running this using the string "share/open/global/robots.txt" will indeed dump robots.txt

I then tried to loop through my database query in this manner:

<cfloop query="checkDb">
<cfset theString="#checkDb.FILEPATH#">
<cfset theArray=ListToArray(theString, "/")>
<cfdump var=#theArray[4]#>
</cfloop>

This does not work. Should this be an index loop? I have tried a handful of possibilities, with no success yet.

I just want to dump out a full list of the files without the filepaths.

Any insight will be greatly appreciated. Thanks!

[Craig328]

Have you tried the ListLast() function?

Try passing in the filepath value and then use the forward slash as the delimiter:

<cfoutput query="checkDb">
<cfset parsedFileName = ListLast(checkDb.FILEPATH,"/")>
parsedFileName: [#parsedFileName#]<br>
</cfoutput>

Of course, rather than outputting the value you could add it to an array or whatever it is you feel like doing with it but that should isolate your filename nicely.

I surround my output with squared brackets like that so I can see any wayward spaces that might be in the data. Naturally, you can write the cfset like like this: Trim(ListLast(checkDb.FILEPATH,"/")) and not be bothered with it.

Good luck!

This post has been edited by Craig328: 07 April 2011 - 08:45 AM

[midasxl]

Nice! That works very well, thanks for your help. Here is what I eventually got working:

<cfloop query="checkDb">
<cfset theString = "#checkDb.FILEPATH#">
<cfset theArray = ListToArray(theString, "/")>
<cfset length = #ArrayLen(theArray)#>
<cfoutput>#theArray[length]#</cfoutput>
</cfloop>

I was using this to separate the string into an array separated by the "/". I needed to target the last item in the array, and since the paths were different array lengths (some are 3, others are 4), I had to get the length of each array and use that to get what I needed. Of course the ListLast works the same and with much less code.

What I am trying to ultimately do is to take the database output and compare it with a cfdirectory query to try and find orphan files.

I have a cfdirectory call:

<cfdirectory name="getAllFiles" action="list" directory="pathToFolderOfFiles" type="file" recurse="yes">

Then I also have the database call (checkDb).

What I would like to do is a comparison of the cfdirectory results and the database results. The cfdirectory returns only the file names so I had to go through the chore of parsing out the actual names of the files from the database results which included the path as well.

So now I have both the cfdirectory call and the database call giving me just the filenames. How do I go about comparing them (the cfdirectory has over 200 more files than the database has records, so I know there are orphan files)?

I figured something like this:

<cfquery name="compare" datasource="">
SELECT * FROM getAllFiles
WHERE getAllFiles.name NOT IN (SELECT getAllFiles.name FROM checkDb)
</cfquery>

I know that's wrong because I can't target the database directly or it will return the name of the file AND the path. Not sure how to go about this. Any ideas?

Thanks again!!

[Craig328]

Well, if it were me, I'd think of creating a list of unique filenames from either source and then loop over the other source doing a ListFind function for the filename in the list of filenames.

So, using your example, I'd make the checkDb looping thing with the ListLast function build a listing of filenames from the database and then while looping over the cfdirectory recordset, do a ListContains() check against the checkdb list inside the loop. The ListContains function returns either zero (if not found) or the position of the first matching element in the listing (if it is found). You'd just need to check the function output versus zero and then proceed with further processing depending on what you want to do.

Depending on the database, you COULD do a combination of database functions like REVERSE, FIELD, LEFT and REVERSE again to get the file name from the query and then use something like ValueList() to get the string of file names to compare against...but the first suggestion is probably easier to do.

[midasxl]

Thanks for the suggestions. Here is what I have attempted to do, which is not performing as expected:

<!--- get a result set from a directory of files --->
<cfdirectory name="getAllFiles" directory="#request.uploadArea#" type="file" action="list" recurse="yes">

<!--- get a result set from the database --->
<cfquery name="checkDb" datasource="#request.dsn#">
SELECT name FROM TBLFILECONTENT
</cfquery>

<!--- The result set from the database call returns filenames with the path to the file as well.  Need to parse out just the filename --->
<cfset parseDb = QueryNew ("name")>
<cfloop query = "checkDb">
<cfset QueryAddRow(parseDb)>
<cfset QuerySetCell(parseDb, "name", "#ListLast(checkDb.FILEPATH, "/")#)>
</cfloop>

<!--- I created a new query object so that I can compare apples to apples (query set to query set).  Now I would like to compare the "getAllFiles" result set with the parsed "parseDb" result set --->

<cfquery name="orphanFiles" dbtype="query">
SELECT name FROM getAllFiles
WHERE name NOT IN (SELECT name FROM parseDb)
</cfquery>

<cfloop query="orphanFiles">
<cfoutput>#orphanFiles.name#<br></cfoutput>
</cfloop>

Now I would expect that to work but I get no result. What I am expecting to see is all the files from 'getAllFiles' that are not in the 'parseDb' result set. This does not work however.

I am able to do this:

<cfquery name="orphanFiles" dbtype="query">
SELECT name FROM getAllFiles
</cfquery>

<cfloop query="orphanFiles">
<cfoutput>#orphanFiles.name#<br></cfoutput>
</cfloop>

Or this:

<cfquery name="orphanFiles" dbtype="query">
SELECT name FROM parseDb
</cfquery>

<cfloop query="orphanFiles">
<cfoutput>#orphanFiles.name#<br></cfoutput>
</cfloop>

Both of the above return a full list of every file name from the respective result set; just what I am looking for. But when I try to compare them via the 'NOT IN' sql statement I get nothing.

I've also tried:

<cfset parsedFileName = ListLast(checkDb.FILEPATH, "/")>
<cfset getAllFilesList = ValueList(getAllFiles.name, ",")>

I'm guessing this turns both query result sets into lists, which I verify when I cfdump the variables. Then I try to compare the two lists.

<cfset newList="">
<cfloop list="#getAllFilesList#" index="i">     
<cfif ListFindNoCase(parsedFilesList,i)>       
<cfset newList = ListAppend(newList,i) />       
</cfif>   
</cfloop> 

I imagine this would sequentially check each file in the getAllFilesList with the parsedFilesList and sequentially add them into the newList variable as it finds a match.

Anyways, none of these are working well for me. Thanks again for anymore insight!

Cheers!

[Craig328]

Okay. Part of the problem is that trying to do a select subquery using QofQ is dicey at best. The support for it isn't all that solid. What's more, line 13 in your first code block looks suspect to me.

That said, whenever I run into the code not doing something I expect it to do, I have the code do a series of dumps or cfoutputs at each necessary stage to determine what's coming out of my code. So, with that in mind, try running this and see what you get:

<!--- get a result set from a directory of files --->
<cfdirectory name="getAllFiles" directory="#request.uploadArea#" type="file" action="list" recurse="yes">

<!--- get a result set from the database --->
<cfquery name="checkDb" datasource="#request.dsn#">
SELECT name FROM TBLFILECONTENT
</cfquery>

<!--- The result set from the database call returns filenames with the path to the file as well.  Need to parse out just the filename --->
<cfset parseDb = QueryNew ("name")>
<cfloop query = "checkDb">
<cfset QueryAddRow(parseDb)>
<cfset variables.fileName = ListLast(checkDb.FILEPATH,"/")><cfoutput>checkDb.FILEPATH: [#checkDb.FILEPATH#] - variables.fileName: [#variables.fileName#]<br></cfoutput>
<cfset QuerySetCell(parseDb, "name", variables.fileName)>
</cfloop>

Try that first and see what you see. BTW, what database backend are you using? There are some nifty database functions you could use to pull the file name out on the initial query.

This post has been edited by Craig328: 11 April 2011 - 12:19 PM
Reason for edit:: forgot a closing cfoutput