Announcement

Collapse
No announcement yet.

SQL query for users with 0 posts and signatures

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

  • Videx
    replied
    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.

    Leave a comment:


  • borbole
    replied
    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

    Leave a comment:


  • Gorf
    replied
    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

    Leave a comment:


  • Gorf
    replied
    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.

    Leave a comment:


  • borbole
    replied
    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.

    Leave a comment:


  • Vodkaholic
    replied
    Why not just delete all Zero posters in the admincp

    Admincp >> Users >> Prune / Move Users

    Leave a comment:


  • Gorf
    replied
    anyone?

    Leave a comment:


  • Gorf
    started a topic [Forum] SQL query for users with 0 posts and signatures

    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!
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Working...
X