RE: using CF to eliminate old Real Estate photos
It sounds to me like the photos aren't in the database at all, but probably just in a directory? If they are in a DB table, then as mentioned the primary key/ indexing really needs to be rethought. Rather than run a purge via the query (which is going to be a big resource load, from the looks of it) I'd put the delete photos functionality into whatever method is used to close a listing. E.g. house sells, close the listing and delete the photos. Good luck -Ed ~| 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:324799 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: using CF to eliminate old Real Estate photos
substring(name,1,CHAR_LENGTH(name)-5) If the name was 123456a.jpg, this would give you 123456. I hate to be the bad guy but for the record, your troubles stem from a poor database design. Ideally you shouldn't have to resort to string manipulation to tie a record in your photo table to the MLS number it is associated with. I don't know what columns you have in your photos table, but one of them needs to be the MLS number (or whatever other unique key you might use to specify a listing). The column should have an index on it and probably have a foreign key constraint to your master listing table. It is not possible for you to properly use an index right now on your name column if you are performing string manipulation on it. Performance might suffer if you have a large number of records in that table since every record must be scanned, and the substring must be performed each value to find the matching ones. Also, where is the GetAll query coming from? If it is a select being run against the same data source, why not do a join and get your list in a single query? What do you do with old listings? Do you delete them from whatever table they are stored, or do you set some status column? ~Brad Original Message Subject: RE: using CF to eliminate old Real Estate photos From: "Dave Long" Date: Tue, July 21, 2009 4:41 pm To: cf-talk Thanks for the info, Rick but unfortunately, the MLS numbers currently vary between five and six digits. If that Real Estate market stays this bad, they may go to seven or eight digit numbers. However, is it possible to create a list from the query "Photos" and Remove a character from the right side of the field? ~| 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:324785 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: using CF to eliminate old Real Estate photos
Thanks for the info, Rick but unfortunately, the MLS numbers currently vary
between five and six digits. If that Real Estate market stays this bad, they
may go to seven or eight digit numbers.
However, is it possible to create a list from the query "Photos" and Remove
a character from the right side of the field?
Thanks again.
> -Original Message-
> From: Rick Faircloth [mailto:r...@whitestonemedia.com]
> Sent: Tuesday, July 21, 2009 4: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.
>
>
>
>
>
> selectname
> from photos
> where left(name, 5) = '#GetAll.mlsNumber#'
>
>
>
>
>
> file =
> "#expandPath('/photos/#selectMatchingImage.name#')#">
>
>
>
>
>
> 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 # 9
> are named 9a, 9b, and so on.
>
> Well, I thought I had it figured out. I tried to create a
> list of unmatched files using this query:
>
>
> SELECT Name
> FROM Photos
> WHERE %Name% NOT IN ( value="#valueList(GetAll.MLS_Number)#" cfsqltype="cf_sql_integer"
> list="true">)
> ORDER BY Name
>
>
> 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:324783
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: using CF to eliminate old Real Estate photos
Well...the query needs to be modified... selectname from photos where left(name, 5) = '#GetAll.mlsNumber#' 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. selectname from photos where left(name, 5) = '#GetAll.mlsNumber#' 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 # 9 are named 9a, 9b, and so on. Well, I thought I had it figured out. I tried to create a list of unmatched files using this query: SELECT Name FROM Photos WHERE %Name% NOT IN () ORDER BY Name 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
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. selectname from photos where left(name, 5) = '#GetAll.mlsNumber#' 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 # 9 are named 9a, 9b, and so on. Well, I thought I had it figured out. I tried to create a list of unmatched files using this query: SELECT Name FROM Photos WHERE %Name% NOT IN () ORDER BY Name 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:324780 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 # 9 are named 9a, 9b, and so on. Well, I thought I had it figured out. I tried to create a list of unmatched files using this query: SELECT Name FROM Photos WHERE %Name% NOT IN () ORDER BY Name 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:324773 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
