Delete spam accounts

[greatlogix]

Hi,
I want to delete all members who registered more than 365 days ago and have post count zero. What should be the query for it and how can i add this in cron jobs? please advise.

[vbgamer45]

You should be able to do a search on members in the admin area of your SMF board that is the best way to do it.

The other way would have some issues with some data remaining and is not as clean.

[greatlogix]

In admin members list is 9748 pages long. It's very difficult to browse all pages and delete spam accounts.

So it's not possible to do it in one SQL query? Can you please list table names where I have to look and delete account details

[Sir Osis of Liver]

If members have 0 posts, they probably haven't done anything else, so shouldn't cause problems to delete them.  Deleting those who have never logged in would be better, if it serves your purpose.

You can use this in a file in your forum root, and run it as a cronjob -

Code Select Expand



<?php

// Delete all members with 0 posts and registered > 365 days

include("Settings.php");

function OpenDb($hostname,$uid,$pwd,$dbname){
$link = @mysql_connect($hostname,$uid,$pwd);
if($link && mysql_select_db($dbname)){
return($link);
}
else{
return(FALSE);
}
}

$date = strtotime('-1 year', time());

OpenDb($db_server,$db_user,$db_passwd,$db_name) or die("Failed Opening Database");
mysql_query(" DELETE FROM smf_members WHERE posts = 0 AND date_registered < $date; ");

?>



I think that's right (PUI ).  Back up your db before you try it.


edit:  Fixed it.  Thx, Arantor.

[Arantor]

Please don't encourage this use of Settings.php directly. SMF supports other database types, and persistent connections are rarely a good idea.

Of course an SMF scheduled task would be better...

[Sir Osis of Liver]

What makes it a persistent connection?  Doesn't connection close after script executes?

[Arantor]

You using mysql_pconnect makes it a persistent connection.

Persistent connections are a thorny business, yes, essentially the connection doesn't close, the idea being that you open a pool of connections and let scripts connect through the pool to save the overhead of creating a new connection every thread.

On the one hand this is great if you're in a setup where connection pooling is viable. Most hosts, though, it is not.

[Sir Osis of Liver]

Hmm, looks like mysql_pconnect opens a persistent connection in php4-5, but it's deprecated as of 5.5.0, and mysqli_connect() must be used with p: host prefix to open a persistent connection.

So it appears you must request a persistent connection to get one.

* Krash needs another beer.

[Chen Zhen]

greatlogix,

  This may work for you:

Code Select Expand


global $sourcedir, $smcFunc;

require_once($sourcedir . '/Subs-Members.php');
@ini_set('memory_limit', '128M');
$members = array();

$request = $smcFunc['db_query']('', '
SELECT id_member
FROM {db_prefix}members
WHERE date_registered <= {int:current}
AND posts = {int:posts}
AND id_group != {int:group}
',
array(
'current' => time() - 31556940,
'posts' => 0,
'group' => 1
)
);
               
while ($row = $smcFunc['db_fetch_assoc']($request))
$members[] = $row['id_member'];

$smcFunc['db_free_result']($request);


deleteMembers($members, false);


Edit ->array had to be defined

[Sir Osis of Liver]

You using mysql_pconnect makes it a persistent connection.

Yikes, never saw that.

*Krash has much work to do .

[Arantor]

Hmm, looks like mysql_pconnect opens a persistent connection in php4-5, but it's deprecated as of 5.5.0, and mysqli_connect() must be used with p: host prefix to open a persistent connection.

So it appears you must request a persistent connection to get one.

ext/mysql is indeed deprecated however most hosts are still using PHP 5.4 or lower at this time, and will still install ext/mysql if desired. It's only really when we get to 5.6 that we have a problem and we have a while yet.

Note that SMF is not going to be changing to MySQLi until 2.1 (and we're not going to be backporting to 2.0, we already have MySQLi support)

@ -Underdog- That script looks good, if you're in an environment where those things already exist, which means after SSI.php is loaded or inside a scheduled task. Merely loading Settings.php will not declare the appropriate $smcFunc functions.

[Chen Zhen]

Arantor,
  I am assuming the OP knows how to put it to use or perhaps will ask of it.

[greatlogix]

Ah! great. This thread is not only problem solving but also clearing my concepts about "MySQL and connection through PHP". Thanks all for posting and sharing valuable information.

Arantor,
  I am assuming the OP knows how to put it to use or perhaps will ask of it.

Yes, I would better ask . Can you please post final piece of code which will work fine in a cron file?

will this work?
require_once($sourcedir . '/SSI.php');
require_once($sourcedir . '/Subs-Members.php');
//continue code posted by Underdog

Thanks again.

[Chen Zhen]

greatlogix,

  Actually I was thinking you could implement the code in a portal block, as a small mod or by editing an existing source file as opposed to creating a cron job. Attached is a small plugin that will add an option to your forum that performs the task you seek.  After installation, go to Admin -> Members_Checker -> Settings .. set the number of days (ie. 365).



 

[Arantor]

Interesting. Why go to all the trouble of loading files every page load and executing multiple hooks when you could just do it as a scheduled task in SMF's setup?

As per, http://www.simplemachines.org/community/index.php?topic=329894.0 just add the function to ScheduledTasks.php (because 2.0's ScheduledTasks can't do loading external files) and add the row to the database to declare the task. If you do need to add settings, there is a perfectly good page already there which can be easily hooked for the purpose.