0

I have a site where I forgot to turn off member authentication and it got hit with over 100,000 spam members over a couple of weeks before we caught it. Now they're all full Members so the SQL to delete Pending members doesn't help.

SQL isn't my forte, but from what I've been able to piece together I think this might do the trick, can anyone say if I'm missing anything?

DELETE exp_members, exp_member_data, exp_member_homepage FROM exp_members 
INNER JOIN exp_member_data 
INNER JOIN exp_member_homepage 
WHERE exp_member_data.member_id = exp_members.member_id 
AND exp_members.member_id = exp_member_homepage.member_id 
AND exp_members.group_id = 'x'
AND exp_members.group_id = 'x'
AND last_visit = 0

Trying to delete those members who've never logged in.

1
  • "AND exp_members.group_id = 'x'" appears twice in your query.
    – AllInOne
    Commented Jul 1, 2014 at 18:11

1 Answer 1

0

After backing up your Database i'd run this Query for each one of those tables:

Delete * from myTable where group_id = 'group_id_2_delete' AND member_id not in (#,#,#,#); <<<< Members ids you don't want to delete

3
  • I guess I wasn't clear - we still do have a few hundred valid Members in there, so manually listing all the IDs of members that we don't want to remove isn't practical.
    – Tony
    Commented Jul 1, 2014 at 13:45
  • I guess i'm confused you have a 100 valid members and you don't want to not delete them with your query? That doesn't make sense. Commented Jul 2, 2014 at 0:34
  • If you aren't wanting to manually add those numbers run this query. Delete * from myTable where group_id = 'group_id_2_delete' AND member_id not in (Select member_id From exp_member_data where last_visit > 0); and to make sure the query is working properly do a select instead of a delete to make sure you are returning your valid results. Commented Jul 2, 2014 at 0:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.