Mass Delete Users through PhpMyAdmin

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • meissen
    Senior Member
    • Dec 2005
    • 215
    • 3.5.x

    [Forum] Mass Delete Users through PhpMyAdmin

    I have about 1,000 spam users that I need to delete from my site. All of these users have zero posts, but they all have signatures with URLs in them. I can see there's references to the users in multiple user tables. I already have a CSV file with all the user IDs I want to delete, so basically I just need to know how to set up the query or queries.

    Delete rows from users where userid IN (csv file)
    Delete rows from usertextfield where userid in (csv file)

    etc etc etc.

    What tables do I need to run the delete rows against?

    Thanks in advance.
  • Zachery
    Former vBulletin Support
    • Jul 2002
    • 59097

    #2
    Please do not delete users directly from the database, you're going to cause yourself a huge headache and support nightmare.

    Have you considered pruning or just banning these users?

    AdminCP > Users > Move / Prune Users

    Comment

    • Andy
      Senior Member
      • Jan 2002
      • 5886
      • 4.1.x

      #3
      As Zachery indicated, use the Prune Users function in the Admin CP to delete these members.

      AdminCP > Users > Move / Prune Users

      Comment

      • meissen
        Senior Member
        • Dec 2005
        • 215
        • 3.5.x

        #4
        Unfortunately the Move / Prune Users does not have the ability for me to search and find these users. My criteria for searching for the members is post count = 0 and Signature like [URL= -- I need the Signature search which isn't available on the Move / Prune Users page.

        Again - these users have zero posts, how many tables can they be tied into?

        Comment

        • Zachery
          Former vBulletin Support
          • Jul 2002
          • 59097

          #5
          at least 5, and that is besides the point.

          Why can't you find these users in prune / move?

          Comment

          • Andy
            Senior Member
            • Jan 2002
            • 5886
            • 4.1.x

            #6
            Originally posted by meissen
            Unfortunately the Move / Prune Users does not have the ability for me to search and find these users. My criteria for searching for the members is post count = 0 and Signature like [ URL= -- I need the Signature search which isn't available on the Move / Prune Users page.

            Again - these users have zero posts, how many tables can they be tied into?
            Why not just delete all with 0 posts and older than say 30 days, or some other number of days since last activity?

            Comment

            • meissen
              Senior Member
              • Dec 2005
              • 215
              • 3.5.x

              #7
              Originally posted by Zachery
              at least 5, and that is besides the point.

              Why can't you find these users in prune / move?
              The criteria for finding them is based on what's in their signature which is not available function in Prune / Move... It'd be nice if the Prune / Move page had the same search capabilities as the "Search for Users" page. Likewise I realize I can go through one by one and select who to delete and who not to delete, but again this is just as tedious since I would have to either open each user's profile individually to verify they're legit or a spammer and then (un)check the box accordingly.

              Originally posted by Andy
              Why not just delete all with 0 posts and older than say 30 days, or some other number of days since last activity?
              I recently took the forum over and upon going through the settings I realized they did not have E-mail Verification set up... users were able to register and immediately post and/or edit their profile. I've resolved the settings issue to lock the forum down, but even after deleting a few hundred users manually I still have over a thousand to still delete ....

              I don't want to delete legitimate users who happened to register on the site and just haven't posted yet.

              If I do a Prune / Move search of all users who registered Prior to September 1st 2012 with 0 posts and haven't logged in in the past 5 days, it brings up 4800 users. With the Signature like [url= criteria, that would only be 1000 users to sift through...
              Last edited by meissen; Fri 7 Sep '12, 3:34am.

              Comment

              • Andy
                Senior Member
                • Jan 2002
                • 5886
                • 4.1.x

                #8
                Originally posted by meissen

                I don't want to delete legitimate users who happened to register on the site and just haven't posted yet.

                If I do a Prune / Move search of all users who registered Prior to September 1st 2012 with 0 posts and haven't logged in in the past 5 days, it brings up 4800 users. With the Signature like [ url= criteria, that would only be 1000 users to sift through...
                Change the criteria to delete all those with 0 posts and not logged in for the past 90 days. Then do the same thing in another 90 days from now.

                Also here's a thread explaining the best way to prevent future spammers.

                Comment

                • meissen
                  Senior Member
                  • Dec 2005
                  • 215
                  • 3.5.x

                  #9
                  Right so delete 4800 users just to clean out 1000 spammers.....

                  Comment

                  • Mr_Running
                    Senior Member
                    • Nov 2009
                    • 178
                    • 4.2.X

                    #10
                    Originally posted by meissen
                    Right so delete 4800 users just to clean out 1000 spammers.....
                    Chances are the other 3800 are s p a m accounts if they have not logged in the pass 90 days and have zero posts.
                    Think of it like this a forum is mainly for discussion and yes lurkers read. If they haven't logged in over 90 days and have zero post... so be it ! Clean up as the new Admin and get in under control and maybe it will only need to be done once.
                    Mr_Running
                    You Could Live a Long Time: Are you Ready to Run?

                    For the improvement of social groups, please add tags, attachments, infractions, reputation, moderation tools, notifications, thread read marking, group discussions in What's new, category permissions and subcategories.
                    More improvements: http://tracker.vbulletin.com/browse/VBIV-8784

                    Comment

                    • meissen
                      Senior Member
                      • Dec 2005
                      • 215
                      • 3.5.x

                      #11
                      Maybe your experience in forum administration is different, but I've been running another forum which I started in 2005 and every time I deleted inactive users I would always get multiple e-mails from the lurkers who tried to login and couldn't anymore. I guess I'll have to do it as Andy described, just seems silly to me that I have to delete a ton of innocent accounts.

                      Comment

                      • Mr_Running
                        Senior Member
                        • Nov 2009
                        • 178
                        • 4.2.X

                        #12
                        I agree, it's silly.
                        Maybe provide a notice for logged in users with zero posts to post within a certain amount of time, say within the next 30 days or the account will be deleted. If there is an Introduction/welcome forum...it may become very active.
                        Mr_Running
                        You Could Live a Long Time: Are you Ready to Run?

                        For the improvement of social groups, please add tags, attachments, infractions, reputation, moderation tools, notifications, thread read marking, group discussions in What's new, category permissions and subcategories.
                        More improvements: http://tracker.vbulletin.com/browse/VBIV-8784

                        Comment

                        • John Lester
                          Senior Member
                          • Jul 2000
                          • 412
                          • 4.1.x

                          #13
                          Did you look at vbulletin.org to see if there were any mods or plugins that might do what you want?
                          BrainTalk is a support group for friends, family, caregivers, and patients with neurological disorders and other health related diagnosis.

                          BrainTalk Communities Inc
                          sigpic

                          Comment

                          • meissen
                            Senior Member
                            • Dec 2005
                            • 215
                            • 3.5.x

                            #14
                            How about a compromise to Andy and Zachery --

                            Instead of a query to delete the users, could you help me build a query which will add users to an additional custom usergroup if their post count is 0 and their signature is like "[url=" ? That way I can then use the Mass Move / Prune function in AdminCP to view all the users that are in that custom usergroup and prune them through AdminCP.

                            Comment

                            • meissen
                              Senior Member
                              • Dec 2005
                              • 215
                              • 3.5.x

                              #15
                              Figured it out on my own. Thanks.

                              Comment

                              Related Topics

                              Collapse

                              Working...