Hi All,

I am looking for a way to delete multiple records and their associated files with a slight twist. Here's the deal:

I am creating a classifieds type website where a user can post a classified ad, and upload multiple images. The problem is, I have all of the classified ad information stored in one table, and the images stored in another to allow the user to upload any number of images. Like this:

<b><u>TblAds</u></b>
adID
adTitle
adDescription
adPrice

<b><u>TblAdImages</u></b>
imageID
adID
imageField

Keep in mind that there can be an unlimited amount of images related to the classified ad.

When an ad is deleted, I need to be able to delete the record in tblAds, AND, delete all associated records from TblAdImages along with the associated image files (including the thumbnails created by smart image processor).

Any thoughts?

Any help is very much appreciated.
Thanks

Howdy!

What DB are you using? I ask because SQL2000 has cascade delete so you could leave it up to the DB to handle the records in the related table, you could set a DElete Trigger. I personally do most of mine through SP's so I know what's going on.

The other way to do this is to modify the DELETE statement and add another 'DELETE FROM myTable where' blah blah blah!

Something like this:

Create for TblAds (rsTblAds for example), then create the delete statement...

DELETE FROM TblAds WHERE adID = rsTblAdsFields.Item("adID".Value ;
DELETE FROM TblAdImages WHERE adID = rsTblAdsFields.Item("adID".Value

Notice the semi colon at the end of the first delete statement, this allows you to send multiple SQL statements in one go, don't you just love SQL!

Anyhow, this is just an example, hopefully it'll help, let us know how you get on.



Digga

Sharing Knowledge Saves Valuable Time!!!

Hey DIgga,

I am using MS Access, however, there is a cascade delete function on the relationships in Access as well. I can use that and it works just fine. The main record from tblAds gets deleted and all the related records from tblAdImages get deleted as well. I guess my MAIN PROBLEM is the fact that all of the images relating to tblAdImages do not get deleted from the server. SO, after a while, as users insert and delete ads, all of the images stay on the server and eventually take up too much HD Space.

Any ideas on how can I remove the images too?

Thanks

Hi

I know nothing about the smart image processor, however i do know how to delete files using asp and this should give you some idea.

The webserver file system is accessed using FSO - the FileSystemObject. You'll need to create an instance of the object like this:

Set fso = CreateObject("Scripting.FileSystemObject"

You need to set the path to the file(s) and I'm assuming that you'll be pulling the information about the images from a db so the delete method can be contained in a loop and the files deleted by looping through the recordset, something like this:

Dim delMyFile ' declare variable to hold the path

While ((NOT rsMyImageData.BOF) AND (NOT rsMyImageData.EOF))

' setting the path and name for each image you wish to delete
delMyFile = rsMyImageData.Fields.Item("imagePath".Value

' delete the file in the path specified
fso.DeleteFile delMyFile

' move to the next record in the recordset
rsMyImageData.MoveNext()

' loop through the recordset again
Wend

' kill the fso object created earlier
Set fso = Nothing

If all the thumbs are sitting in their own folder then you can use the DeleteFolder method of FSO instead.

This code is assuming you're using ASP but can be written in any language, it' also only an example and I might have missed something, get back to us if you get stuck.

IIS must have permissions on the destination folder you wish to delete from.

Backup all folder and files before you play, this will delete files permanently, try it out on a test folder(s) first.

I think I've covered all, let us know how you get on.

Digga

Sharing Knowledge Saves Valuable Time!!!

Thanks Digga,

That worked great. I do have one more question though. With the Smart Image Processor, a second image file is created and used as a thumbnail. The image is name for the thumbnail has a "_small" attached.

For example: If I uploaded an image named "myImage.jpg", 2 files would get created and stored on the server: "myImage.jpg" and "myImage_small.jpg". Any ideas on how I can insert that "_small" into the code of a second datapath to be deleted?

Thanks...

Like this:

delMyFile = Replace(rsMyImageData.Fields.Item("imagePath".Value, ".jpg", "_small.jpg"

You could dim another variable so you have two delete lines:

delMyFile = rsMyImageData.Fields.Item("imagePath".Value
delMyFileThumb = Replace(rsMyImageData.Fields.Item("imagePath".Value, ".jpg", "_small.jpg"

fso.DeleteFile delMyFile
fso.DeleteFile delMyFileThumb

Digga

Sharing Knowledge Saves Valuable Time!!!

Edited by - Digga the Wolf on 22 Sep 2004 10:43:53

Digga,

Thanks a million. Everything works perfectly!

You're Welcome!

Digga

Sharing Knowledge Saves Valuable Time!!!

Hi

Just had a thought about the logic when deleting the _small thumbs. If you have different file types - jpg, bmp, gif etc., then a different way of handling this will need to be written as the example I gave you will only handle jpg.

Let me know what you need.

Digga

Sharing Knowledge Saves Valuable Time!!!

Hey Digga,

No, for this specific scenario, I only Need to remove JPG files. So, the code you gave me worked just fine.

However, if you would like to post some way to have it detect what file type it is, and then delete it, I am sure I will need to use that concept in the future.

Thanks Digga!

Hiya

I've created a test page to play with:

<pre id=code><font face=courier size=2 id=code>&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;%
If Request.QueryString("txtTest" &lt;&gt; "" Then

Dim myArray, myImage, myImageOut, myCounter, myUpper, myLower

' set the value of the myImage variable,
' could be rsMyImageData.Fields.Item("imagePath".Value
myImage = Request.QueryString("txtTest"

' throw the image name into an array and split it up by "."
' this is important to get the file extension out and also
' caters for filenames with "." included, like my.image.name.jpg
' and not all file extensions are three characters long, jpeg for example
myArray = Split(myImage,".",-1,1)

' find the upper index number of the array
myUpper = UBound(myArray)

' find the lowest index number of the array, normally 0
myLower = LBound(myArray)

' set myCounter to the lowest index of the array
myCounter = myLower

' combine the elements of the array, putting back the "."
Do While myCounter &lt; myUpper
myImageOut = myImageOut & myArray(myCounter) & "."
myCounter = myCounter + 1
Loop

' remove the last "." inserted in the myImageOut string in the loop above
' and add the _small and the file extension
myImageOut = Left(myImageOut, Len(myImageOut)-1) & "_small." & myArray(myUpper)

End If
%&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;
&lt;/head&gt;

&lt;body&gt;
&lt;%
' test the input and output
Response.Write("&lt;p&gt;&lt;strong&gt;My Original Image Name: &lt;/strong&gt;" & myImage & "&lt;br&gt;"
Response.Write("&lt;strong&gt;My New (_small) Image Name: &lt;/strong&gt;" & myImageOut & "&lt;/p&gt;"
%&gt;
&lt;form action="" method="get"&gt;&lt;input name="txtTest" type="text" id="txtTest"&gt;
&lt;input name="Submit" type="submit" id="Submit" value="Submit"&gt;
&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;
</font id=code></pre id=code>

If you have any questions, just post back.

Digga

Sharing Knowledge Saves Valuable Time!!!