1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How can I check which users have signatures?

Discussion in 'XenForo Questions and Support' started by Lone Wolf, Feb 29, 2012.

  1. Lone Wolf

    Lone Wolf Well-Known Member

    I've noticed today that about 10% of my 2000 registered users are spam accounts which have unsavoury signatures or signatures with spam links.

    Is there any way to do a search which allows me to only review those users with signatures so I know which ones to delete without checking each user individually?
     
  2. borbole

    borbole Well-Known Member

    Go to your phpmyadmin and choose the database that you have installed your xenforo forum. Then at the SQL tab run this query:

    Code:
    SELECT user.username, signatures.signature
    FROM xf_user_profile AS signatures
    LEFT JOIN xf_user AS user ON ( user.user_id = signatures.user_id )
    ORDER BY user.username;
    This will show a list of all your users and their signatures. If you want to delete signatures of users with 0 posts who are usually spammers, run this query:

    Code:
    UPDATE xf_user_profile 
    LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
    SET signature = ''
    WHERE xf_user.message_count = 0;
    
    If you are using another prefix for your tables, replace xf_ with your custom prefix at the queries above. Hope it helps.
     
    Simon, Toxic, 0xym0r0n and 3 others like this.
  3. Brogan

    Brogan XenForo Moderator Staff Member

    Lone Wolf and borbole like this.
  4. Lone Wolf

    Lone Wolf Well-Known Member

    THanks Borbole and Brogan, I will be doing both those things
     
    borbole likes this.
  5. DRE

    DRE Well-Known Member

    Awesome.
     
  6. Toxic

    Toxic Member

    is this possible to reset just the Registered User Group signatures to blank?

    would it be something like this?

     
  7. borbole

    borbole Well-Known Member

    The last line in your query is not correct. Try this:

    Code:
    UPDATE xf_user_profile
    LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
    SET signature = ''
    WHERE xf_user.user_group_id = 2;
     
    Toxic likes this.
  8. Toxic

    Toxic Member

    Ty for the quick reply!

    i've also done

    UPDATE xf_user_profile
    LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
    SET about = ''
    WHERE xf_user.user_group_id = 2;

    UPDATE xf_user_profile
    LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
    SET homepage = ''
    WHERE xf_user.user_group_id = 2;

    so Registered users dont advertise until they are Members after 5 posts
     
    Simon and borbole like this.
  9. borbole

    borbole Well-Known Member

    You are welcome :)

    Note that that would only clear the current about and the homepage fields. But it won''t prevent new users from filling those fields again. To do that you will need a custom mod.
     

Share This Page