MYSQL Script that will delete all members with 0 posts but has a signature or we

[Biology Forums]

Can someone provide me a MYSQL script that will delete.

All members with 0 posts with a signature.

OR

All members with 0 posts with a website added.

I need to reduce my members list, which has over 200 000 members, and only a tiny fraction are active.

Thank you.

[kat]

If you go to "Manage Members" and click the title of the "Posts" column, it sorts the members into post-count order, if that helps.

There's the "Remove Inactive Members" section, in "Forum Maintenance", too.

Come to that, if you go to phpmyadmin and select the smf_members table, you can sort that in post-count order and delete all the "Zero posts" entries, I guess. (Backup, first, just in case).

No need to reinvent the wheel, for this, unless you really want to.

[Illori]

you should not just delete them from the database, that will leave parts elsewhere that are not deleted.

[Biology Forums]

Thanks Kate, but that doesn't do quite what I need. Remember, I want to delete members that have a website added OR a signature.

Thank Illori, but if they haven't posted, how will that affect anything? These are your typical spammers who signup just to add their website for backlinks.

By the way, this is for SMF 1.x

[margarett]

It's pretty much the total number of users and last registered user, and maybe membergroup attributions, if any.
Anyway it is possible to perform that script on a php file that calls the correct SMF functions. For me, 1.1 is the challenge

[Irisado]

Seems quite specific to 1.1, so I've moved it to 1.1 Support for the time being.  I can move it back to Scripting Help if that becomes more appropriate as the topic progresses .

[Arantor]

Off the top of my head...

Code Select Expand


<?php
require_once('/path/to/SSI.php'); // wherever this is, you figure this one yourself
require_once($sourcedir . '/Subs-Members.php');

// We should only be running this as an admin because this ain't cheap on the DB.
isAllowedTo('admin_forum');

global $db_prefix;

$members = array();
$request = db_query("
SELECT ID_MEMBER
FROM {$db_prefix}members
WHERE posts = 0
AND (signature != '' OR websiteUrl != '')", __FILE__, __LINE__);
while ($row = mysql_fetch_assoc($request))
$members[] = (int) $row['ID_MEMBER'];
mysql_free_result($request);

if (!empty($members))
{
deleteMembers($members);
}

echo count($members), ' account(s) passed to deletion routine.';

I personally can't wait for 1.1.x to die a swift and merciful death because writing queries like this is painful. However I think this should work, can't be bothered to set up a 1.1.x box to test it on though. Run on a test install before deployment, i.e. at your own risk.

[Biology Forums]

Awesome! It worked, except it didn't show the message

Code Select Expand

echo count($members), ' account(s) passed to deletion routine.';

Thank you so much. Got rid of 8 pages of members...

[kat]

[KirkhamsEbooks]

Off the top of my head...

Code Select Expand


<?php
require_once('/path/to/SSI.php'); // wherever this is, you figure this one yourself
require_once($sourcedir . '/Subs-Members.php');

// We should only be running this as an admin because this ain't cheap on the DB.
isAllowedTo('admin_forum');

global $db_prefix;

$members = array();
$request = db_query("
SELECT ID_MEMBER
FROM {$db_prefix}members
WHERE posts = 0
AND (signature != '' OR websiteUrl != '')", __FILE__, __LINE__);
while ($row = mysql_fetch_assoc($request))
$members[] = (int) $row['ID_MEMBER'];
mysql_free_result($request);

if (!empty($members))
{
deleteMembers($members);
}

echo count($members), ' account(s) passed to deletion routine.';

I personally can't wait for 1.1.x to die a swift and merciful death because writing queries like this is painful. However I think this should work, can't be bothered to set up a 1.1.x box to test it on though. Run on a test install before deployment, i.e. at your own risk.

will this work with 2.0.10 I really need something like this I have 1000s of members to delete with 0 posts.

Rick

[Illori]

a script written for 1.1.x will not work for 2.0 as the database has changed.