No announcement yet.

Mass Delete Users through PhpMyAdmin

  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    Just thought I'd add to an old thread the queries I used to get rid of spam users based on a homepage or signature set, but no other activity. It is basically what was said by meissen above, but a little more thorough. It assumes that everyone gets a single welcome PM when they join. If you don't do that then change the pmcount in the query to 0. The select is not perfect but I did quite a few iterations and changes before settling on it. For me at least I think there were very few false positives.

    Backup your users table and create a new spammers usergroup.

    run this and download the output, then using a text editor put in the format "name","name","name". I used username rather than userid as it gave me a warmer feeling.

    SELECT u.* FROM mydb.vb_user u join mydb.vb_usertextfield utf on u.userid = utf.userid where u.posts = 0 and u.lastpost = 0 and u.pmunread = 1 and (length(utf.signature) > 0 or length(u.homepage) >0) and utf.signature like "%[URL%" limit 10000;
    once these are in a nice long quoted string, run:

    update mydb.vb_user set usergroupid = MY_NEW_USERGROUP where userid in (COPY AND PASTE IN LONG LIST FROM ABOVE);
    now go to admincp->users->prune as described above and 1000 at a time delete them.
    VB 4.1.9 on the Army Rumour Service
    VBSEO 3.6.0
    CentOS 5.6 / Slackware 12


    Related Topics