Announcement

Collapse
No announcement yet.

MySQL Query: Delete avatars?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Query: Delete avatars?

    What would be the right MySQL query to delete the avatar images and profile images from all users in the "Banned Users" usergroup?
    jca

  • #2
    You'd need a script to delete the actual data. You can only delete the reference in a query, but the data will still be in the database.
    Best Regards
    Colin Frei

    Please don't contact me per PM.

    Comment


    • #3
      What is the quickest way to delete a user's avatar?

      Is there a better way than via Admin > User Manager > Change Avatar > Delete Avatar > Yes > Save ?

      The forum is being flooded with "Backlink Spammers", and they ALWAYS add an avatar.
      jca

      Comment


      • #4
        Originally posted by Colin F View Post
        You'd need a script to delete the actual data. You can only delete the reference in a query, but the data will still be in the database.
        Do these 3.0 era scripts still work?

        http://www.vbulletin.com/forum/showthread.php?t=101614

        http://www.vbulletin.com/forum/showthread.php?t=106531
        jca

        Comment


        • #5
          Originally posted by jca View Post
          What is the quickest way to delete a user's avatar?

          Is there a better way than via Admin > User Manager > Change Avatar > Delete Avatar > Yes > Save ?

          The forum is being flooded with "Backlink Spammers", and they ALWAYS add an avatar.
          Simply go to the users profile on the forum < Click "Edit User Profile" < You'll see their page with their usergroup, other info < stay on that page, and scroll down until it has their avatar < click change avatar and delete it.

          It takes like one second... .just sounds long.

          Comment


          • #6
            Thanks, but that is the same series of screens as I described, just starting with the user's profile page, which then leads into the same Admin section screens.

            Unfortunately the "Change Avatar > Delete Avatar > Yes > Save?" menu sequence is a bit cumbersome when trying to delete a lot of user avatars.

            Hopefully the older scripts in the two linked threads will work?
            jca

            Comment


            • #7
              Have you tried pruning the usergroup - that deletes all relevant data.
              Lats...

              Comment


              • #8
                No, we need to keep all the "Banned Users" so we can see what IP ranges need to be blocked over time (based on previous spammer accounts), tracking viral spam campaigns, etc.
                jca

                Comment


                • #9
                  Okay, if both types of images are stored in the database, try these 2 queries...
                  Code:
                  DELETE FROM
                      customavatar
                  USING
                      customavatar
                  LEFT JOIN
                      user
                  ON
                      user.userid = customavatar.userid
                  WHERE
                      user.usergroupid = 88
                  and...
                  Code:
                  DELETE FROM
                      customprofilepic
                  USING
                      customprofilepic
                  LEFT JOIN
                      user
                  ON
                      user.userid = customprofilepic.userid
                  WHERE
                      user.usergroupid = 88
                  Change 88 to the usergroupid of the banned group.
                  Lats...

                  Comment


                  • #10
                    Originally posted by Lats View Post
                    Okay, if both types of images are stored in the database, try these 2 queries...
                    Thanks for the two queries. They seem to have worked.

                    Did these queries delete the actual images from the table(s), or just the reference to the images in the member's profile info?
                    jca

                    Comment


                    • #11
                      These two older 3.0 scripts did not seem to work, by the way.
                      jca

                      Comment


                      • #12
                        Originally posted by jca View Post
                        Did these queries delete the actual images from the table(s)...
                        Yes.
                        Lats...

                        Comment


                        • #13
                          Originally posted by Lats View Post
                          Okay, if both types of images are stored in the database, try these 2 queries...
                          Code:
                          DELETE FROM
                              customavatar
                          USING
                              customavatar
                          LEFT JOIN
                              user
                          ON
                              user.userid = customavatar.userid
                          WHERE
                              user.usergroupid = 88
                          and...
                          Code:
                          DELETE FROM
                              customprofilepic
                          USING
                              customprofilepic
                          LEFT JOIN
                              user
                          ON
                              user.userid = customprofilepic.userid
                          WHERE
                              user.usergroupid = 88
                          Change 88 to the usergroupid of the banned group.
                          Thanks! This works!

                          Comment

                          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                          Working...
                          X