Announcement

Collapse
No announcement yet.

SQL query for users with 0 posts and signatures

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

  • [Forum] SQL query for users with 0 posts and signatures

    Can someone aid me in this? I'm not much of a SQL whiz.

    For the most part our spam isn't bad on our board. But every now and then I like to flush it out and look for those users that slip by. For the most part the ones that slip by have links in their signatures and 0 posts. If I could build that list I would be happy as a clam.

    Thanks!

  • #2
    anyone?

    Comment


    • #3
      Why not just delete all Zero posters in the admincp

      Admincp >> Users >> Prune / Move Users

      Comment


      • #4
        Originally posted by Gorf View Post
        Can someone aid me in this? I'm not much of a SQL whiz.

        For the most part our spam isn't bad on our board. But every now and then I like to flush it out and look for those users that slip by. For the most part the ones that slip by have links in their signatures and 0 posts. If I could build that list I would be happy as a clam.

        Thanks!
        Try to run this query at the SQL box in phpmyadmin. It should delete the signatures for all users whose postcount is 0.

        Code:
        UPDATE usertextfield LEFT JOIN user ON user.userid = usertextfield.userid SET signature = '' WHERE user.posts = 0;
        Make a backup of the usertextfield and the user tables. Also if your db tables have a prefix you should apend it next to the tables name at the query above. Hope it helps.

        Comment


        • #5
          These are both good suggestions, and I thank you for them. However not all users with 0 posts and signatures are spam bots. Hence the reason I simply want to build a list to browse through.

          Comment


          • #6
            borbole, you gave me the clue I needed though. I have successfully managed to figure it out.

            Code:
            select vb_user.posts,vb_user.username,vb_user.password,vb_user.homepage,vb_sigparsed.signatureparsed from vb_sigparsed LEFT JOIN vb_user ON vb_user.userid = vb_sigparsed.userid WHERE vb_user.posts = 0

            Comment


            • #7
              Originally posted by Gorf View Post
              borbole, you gave me the clue I needed though. I have successfully managed to figure it out.

              Code:
              select vb_user.posts,vb_user.username,vb_user.password,vb_user.homepage,vb_sigparsed.signatureparsed from vb_sigparsed LEFT JOIN vb_user ON vb_user.userid = vb_sigparsed.userid WHERE vb_user.posts = 0
              Ah, I thought you wanted to delete the signatures for all the members with 0 posts. Whereas from this query it looks like you wanted only to view them. Anyway glad to see that you got what you wanted

              Comment


              • #8
                If they have 0 posts, who cares what's in their sigs - nobody will see them anyway. They'll just make it look like you have more members - people actually install mods to fake this! But IMHO you should update your registration q&a to keep them out.

                Comment

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