Over flow images


I have a problem with over flow images.  

How can I remove images that belongs to an inactive user from my folder.

so, if the user have an ad online and his/her membership expired or their membership is set to inactive.  I need to remove all their images that are stored in my server folder. Otherwise I will have tones of pictures stored.  

In my database I have a path stored to the images.

<cfif len(SmlPathImage1) and fileExists(expandPath(SmlPathImage1))>

My folder name is "LoadImages "

this will delete all ads that belong to the inactive users

  Delete from ADS
  where UserID in (select UserTbl.UserID
     from UserTbl
     where UserTbl.UserStatus = "I")






Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brijesh ChauhanStaff IT EngineerCommented:
You can just use cffile action as delete to delete the files if you are deleting the user..

<cfif len(SmlPathImage1) and fileExists(expandPath(SmlPathImage1))>

    action = "delete"
    file = "#expandPath(SmlPathImage1)#">

Brijesh ChauhanStaff IT EngineerCommented:
So you have to do this

1. Get ALL the inactive users which you want to DELETE (Get the path of images), make it as a LIST

<cfquery name="getInactivePath" datasource="xxx">
select UserTbl.SmlPathImage1
     from UserTbl
     where UserTbl.UserStatus = "I"

<cfset picDeleteList = ValueList(getInactivePath.SmlPathImage1);

2. DELETE inactive users (as with query in your post)

3. DELETE the pictures of users which are inactive, with help of LIST created in step 1 (Loop through the list and use CFFILE to delete it).
I don't see it stated, but I am going to assume that the smlPathImage column is in the ADS table, not the UserTbl

So, along the same logic as brijesh, but some changes: fetch records from the ADS table based on inactive users and delete all of it's files before deleting the record from the Ad table.    Users are not deleted.

If you have already deleted the record in the ads table, it's a lot trickier to go and find files that have no database record associated with them.  Hopefully that is not the case.

<cfquery name="getFiles" datasource="#request.datasource#">
  select *
   from ADS
  where UserID in (select UserTbl.UserID
     from UserTbl
     where UserTbl.UserStatus = "I")
<cfloop query="getFiles">
  <cfif len(getFiles.SmlPathImage1)>
    <cffile action = "delete" file = "#expandPath(getFiles.SmlPathImage1)#">
  <cfif len(getFiles.SmlPathImage2)>
    <cffile action = "delete" file = "#expandPath(getFiles.SmlPathImage2)#">
  <cfif len(getFiles.SmlPathImage3)>
    <cffile action = "delete" file = "#expandPath(getFiles.SmlPathImage3)#">
  <cfif len(getFiles.SmlPathImage4)>
    <cffile action = "delete" file = "#expandPath(getFiles.SmlPathImage4)#">
  <!---- files are deleted, now we can remove the data record ----->
  <cfquery name="removeAdd" datasource="#request.datasource#">
   delete from ADS where AdID = #val(getFiles.adID)#


Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

lulu50Author Commented:

this is great!!!  
what you posted is the answer to my question.

I do have the path to the image stored in the ADS table.

but, this is another question that I have to open a new question for it.

what if I want to delete all images that have no database records associated with them.

because, say the user have uploaded 4 images. than a few days later the user wanted to change the images so, the path to the images got changed but the previous images are still stored in the my folder. so, now I have 8 images stored in my folder for the same ad but 4 of them have path stored in my database.

to clean up my folder I also do have to delete all images that have no database records associated with them.

right now it will delete all images if the user is inactive but not any previous images uploaded by the same user.

ah, I just remembered that when the user upload the image to my folder I did rename each image so, if the user upload an image name chair.gif  the image is renamed before stored in my folder. It is renamed as the user id then the image name like this:

stored in my folder like this:
say the userid is 4

 I concatenate the user id with the name of the image. this is the original image

 I concatenate the user id with the name of the image "sml" because I resized the image before save it in my folder

so, this is what I think I need to do

instead of concatenate the userid with the image name I should concatenate the Adid with the image name or maybe concatenate them all

the same user can have more than 4 images stored in my folder but for each ad can only have 4 images that's why I have use the adid.

I can grab the AdID (primary key) and do a count if the AdID exist more than 4 time than check the path /LoadImages/5_bgSearch.gif  and file if exist.  If do not exist than they need to be deleted.


lulu50Author Commented:

When I store images, I am always afraid of the name of the file the user is giving me.  It's entirely possible the way the user named the image, will be a bad name for saving on your disk.

What if they named the file :   Sam's #1 (Best) Picture.jpeg

Saving a name like that to the disk could cause you problems.  So this is what I do...

I change the name to match the record ID of the database record.  In your case, the Ad ID.   But since you are storing 4 images in for different fields, you can add something to it.  Perhaps a format like   AdID_Number.jpeg  such as     10_1.jpg  10_2.jpg   10_3.jpg  these would be use by ad ID 10 and be images 1, 2, 3.... out of four.

This could solve a couple problems - it would protect against bad file names and it would allow you to automatically over write images that are replaced (if image 3 is replaced for ad ID 10, you would write over 10_3.jpg)

Let me know if that appeals to you, otherwise, there are other strategies we can use.

Also, consider this... what if they ever get more than 4 images per ad?   Should you code a child table instead of having four seperate fields?

  ... etc....

this could make coding easier for you now because you don't have to repeat everything 4 times for the 4 different fields, you just repeat the same code for X number of records in the database....

You could even add features like hiding an image so they can use one temporarily or something...

Something to think about... clients always change their mind in the future :)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.