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

Deleting over 1,800 users awaiting email confirmation?

Discussion in 'XenForo Questions and Support' started by Floren, Jan 12, 2013.

  1. Floren

    Floren Well-Known Member

    I just looked at the Awaiting Confirmation group, there are over 90 pages of users. How do I safely remove them? I cannot click on the X 1800 times right? I remember I talked about this a while ago with Mike and he suggested a php script being ran from console that uses the data writers, a simple MySQL query is not enough. Can anyone post a script that everyone could use to maintain the groups in XenForo? I just need a basic example, I can tailor the rest to my needs.

    Luis likes this.
  2. MattW

    MattW Well-Known Member

    Doesn't Jaxels XenUtiles have this feature built in?
  3. Slavik

    Slavik XenForo Moderator Staff Member


    That what you after?
    Jake Bunce likes this.
  4. Chris D

    Chris D XenForo Developer Staff Member

    Probably not. That's people awaiting approval ie moderation rather than waiting for email confirmation.

    EDIT: Just realised I haven't got the time.
    Basic idea is to run a query to fetch all user_id

    $db XenForo_Application::getDb();
    $users $db->fetchAll("SELECT user_id FROM xf_user WHERE user_state = 'email_confirm'");
    You could also include email_confirm_edit but these are likely to be genuine users.

    You could do this with the DataWriter, but actually the DataWriter will delete most of this stuff using the database object, so let's just do that.

    So a foreach loop like this:

    foreach ($users AS &$user)
    $userIdQuoted $db->quote($user);

    $db->delete('xf_user'"user_id = $userIdQuoted");

    $db->delete('xf_user_field_value'"user_id = $userIdQuoted");
    $db->delete('xf_user_group_relation'"user_id = $userIdQuoted");
    $db->delete('xf_user_trophy'"user_id = $userIdQuoted");
    $db->delete('xf_user_confirmation'"user_id = $userIdQuoted");
    $db->delete('xf_user_external_auth'"user_id = $userIdQuoted");
    $db->delete('xf_user_field_value'"user_id = $userIdQuoted");

    $db->delete('xf_permission_entry'"user_id = $userIdQuoted");
    $db->delete('xf_permission_entry_content'"user_id = $userIdQuoted");

        if (
    $db->delete('xf_moderator',  "user_id = $userIdQuoted");
    $db->delete('xf_moderator_content',  "user_id = $userIdQuoted");

        if (
    $db->delete('xf_admin',  "user_id = $userIdQuoted");
    $db->delete('xf_admin_permission_entry',  "user_id = $userIdQuoted");

        if (
    $db->delete('xf_user_ban',  "user_id = $userIdQuoted");

    $db->delete('xf_profile_post'"profile_user_id = $userIdQuoted");

    $db->delete('xf_news_feed'"user_id = $userIdQuoted");
    $db->delete('xf_user_news_feed_cache'"user_id = $userIdQuoted");

    $db->delete('xf_user_alert'"alerted_user_id = $userIdQuoted");

    $db->delete('xf_user_follow'"follow_user_id = $userIdQuoted");
    $db->delete('xf_user_follow'"user_id = $userIdQuoted");

    $db->delete('xf_conversation_user'"owner_user_id = $userIdQuoted");
    // note: leaving records in conversation recipient to keep data somewhat intact for others

    $db->delete('xf_ip'"content_type = 'user' AND user_id = $userIdQuoted");
    // note: leaving content-associated IPs

    And that should do it.

    My only recommendation is to test it on a hand written array of 10 user IDs first. Just make sure it works. Then also use Zend_Debug::dump($users) to verify that the query has definitely only retrieved invalid users.

    I'm not going to be held responsible if you delete your entire user base! ;)
    Jake Bunce and vVv like this.
  5. Chris D

    Chris D XenForo Developer Staff Member

    Dante_B, MattW and Jake Bunce like this.

Share This Page