Delete Orphan Attachments Query

[SnapOff Racing]

I was wondering if anyone had any suggestions of a script or a query that I could use that would allow me to delete orphan attachments from my database. I can usually whip up a query that will do what I want it to do except in this case I am not having much luck mainly because with this query I am assuming I am gonna have to use the thread/post table as some sorta reference for deleting orphan attachments from the attachment table.

I found this query when I searched the forums but it's for deleting all attachments in a specific forum. From looking at the query though it looks like it might be almost what I am looking for.

[SQL]DELETE FROM attachment USING attachment, post, thread WHERE attachment.postid = post.postid AND thread.threadid = post.threadid AND thread.forumid = X[/SQL]

Anyone have any ideas?

[Marco van Herwaarden]

How do you end up with orphan attachments anyways?

[SnapOff Racing]

When you delete a thread or post and don't UNCHECK the save attachments button just like some of my mods have mistakenly done.

[Marco van Herwaarden]

Ok let me try to get this correct:
- Your staff soft deletes a thread and ticks the keep attachments.
- Now you have a soft deleted thread still like the original with the attachments.

At this point you still have the option to restore the thread.

- Attachment is not orphaned as the thread still physically exists.

Once you permanently delete the thread, the attachment will also be removed, regardless of the option choosen.

Is the above correct?

Also how many orphans are we talking about?

[SnapOff Racing]

Your right! After some testing I discovered the even if the Keep Attachments box is CHECKED the attachments will be deleted anyways if you are Physically Removing a post or thread. I thought that by having the Keep Attachments box which is checked by default that I probably had a lot of orphan attachments in my database but after taking what you said into consideration and testing it out I think everything should be fine.

Thanks for the help :up:

[Marco van Herwaarden]

I think it is a minor bug that it does show the option to keep attachments on a physical removal. Actually i think on 1 page it does show, on the other not.