How to bulk delete attachments from one user?

[North East Man]

Is there a way either in SMF 2.0.15 or via phpMyadmin to delete all the attachments from a single user.

They have left our site and we are looking to retain their topics and posts by assigning them to an anonymous name but we want to delete all their attachments - there are many many hundreds.

In the admin pages I can see a way to list every attachment that user has made but no option to delete and in phpMyadmin I can't see a table that stores the user ID alongside the attachment ID.  If I could delete the link in the database so that the attachment wouldn't show in the posts but couldn't delete the actual file, I could live with that as space is not an issue.  Ideally, of course I would like to be able to delete the file as well.

Is anybody able to point me in the right direction?

[efk]

Admin/Forum/Attachments and Avatars/Browse Files

This is probably not what you are looking for, but if name is anonymous probably is listed like that and from there you can delete it. Filter with Posted by should work, it will take you some time, but if you don't find easier solution this can help.

[Arantor]

You can't go from attachment ID to user ID, you join the attachments table to the messages table by message ID, and then you can look up the user ID from the messages table.

[North East Man]

Thanks elk,

I had found the admin page you suggest and can browse all the attachments that user has added to their posts but I don't have an option to delete from there.  Their posts have not yet been anonymised so their full user account is still there - I'm just trying to figure out a way to keep their thousands of posts which are very useful but comply with their request to delete their attachments.

[North East Man]

Hi Arantor,

I had a feeling that it would be some kind of table join.  So do I need to create a query in MySQL that joins the tables and then somehow deletes all attachment where the user ID is 1341?

That's a test of my limited SQL knowledge!

[Arantor]

Take a backup in case.

But IIRC my MySQL syntax correctly:

Code Select Expand

DELETE smf_attachments FROM smf_attachments JOIN smf_messages ON (smf_attachments.id_msg = smf_messages.id_msg AND smf_messages.id_member = 1)

(change the id_member at the end from 1 to whatever ID it is)

[North East Man]

Many thanks Arantor,

I will give that a go in the morning.

[Illori]

do keep in mind that does not actually delete the attachments from the file system, just from the database.

[North East Man]

Arantor,

That worked a treat, thank you.  My problem is solved.

Illori, yes, I will just have to accept that the attachments will just have to stay on the server.  For now, that is not an issue as I have plenty of capacity on the server.  There is no way to identify orphaned attachments I suppose.

Many thanks again

[Arantor]

The maintenance routines should be able to do that for you, if I remember correctly.