Well...the query needs to be modified...
<cfquery name = "selectMatchingImage" dbtype="query">
select name
from photos
where left(name, 5) = '#GetAll.mlsNumber#'
</cfquery>
I think that will work with the rest of it.
hth,
Rick
-----Original Message-----
From: Rick Faircloth [mailto:r...@whitestonemedia.com]
Sent: Tuesday, July 21, 2009 5:24 PM
To: cf-talk
Subject: RE: using CF to eliminate old Real Estate photos
Without being able to test it since I use MySQL instead of SQL server,
I think the problem is the use of %Name% in that manner.
You might try taking your MLS number query, "GetAll", and loop or output
that
over a second query, deleting the photo if its name isn't found.
<cfoutput query = "GetAll">
<cfquery name = "selectMatchingImage" datasource="#dsn#">
select name
from photos
where left(name, 5) = '#GetAll.mlsNumber#'
</cfquery>
<cfif selectMatchingImage.recordCount eq 0>
<cffile action = "delete"
file =
"#expandPath('/photos/#selectMatchingImage.name#')#">
</cfif>
</cfoutput>
That works with MySQL 5. You'd need an equivalent for SQL Server for the
left(name, 5) if that doesn't work. This also assumes that the MLS numbers
are always the first 5 digits, and not more or less.
Hope this helps somehow...
Rick
-----Original Message-----
From: Dave Long [mailto:d...@northgoods.com]
Sent: Tuesday, July 21, 2009 4:06 PM
To: cf-talk
Subject: using CF to eliminate old Real Estate photos
I program and host for about 15 Real Estate Agent/Brokers in our area using
a server with CF5 installed..
The company that provides server management for our local Realtor's
association sends a zipped file of modified photos to my server each night.
The file contains new listings and updated photos for existing listings but
does not address the problem of accumulating expired or sold listings. Even
if I set the directory capacity to 3 GB, sooner or later the directory will
fill up. Therefore, I have to come up with a scheme to remove the
out-of-date photos to avoid reaching the directory's maximum allowed space.
So far, I have succeeded in creating a complete list of current MLS numbers
from the multiple tables this provider uses (query name: GetAll) and a
complete list of files contained in the images directory (using CFDIRECTORY
name = Photos.) Both of these queries produce exactly the data I expect.
However, I believe the hitch in my get-along comes from the file naming
convention used by the provider. They allow 12 photos per listing and name
each of photo by adding a letter to the end of the MLS number, so that
photos for MLS # 99999 are named 99999a, 99999b, and so on.
Well, I thought I had it figured out. I tried to create a list of unmatched
files using this query:
<CFQUERY name="GetUnmatched" dataSource="#DSN#">
SELECT Name
FROM Photos
WHERE %Name% NOT IN (<cfqueryparam
value="#valueList(GetAll.MLS_Number)#" cfsqltype="cf_sql_integer"
list="true">)
ORDER BY Name
</CFQUERY>
and then use a CFFILE tag to delete the records found in the unmatched list.
However, I get an error when I run it. You can give it a try at
http://www.newnorthrealestate.com/GetMLSnew.cfm ( It takes a while to
generate the lists and you'll have to scroll way down to see the error
message)
Question: Can the wildcard "%" be used they way I have them in the
GetUnmatched query?
Is there a correct method for parsing the file names in the photo directory
and then doing the comparison?
Am I on the right track at all?
Thanks for taking a look at this.
Dave
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324781
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
