You are not logged in. Please login or register.
PunBB Forums → Feature requests → Script that kills/deletes any user with 0 posts and a signature or www
Any user or your board who has not posted does not need (or deserve 
)a signature, except for spam.
That's why I'd appreciate a script that that kills/deletes any user with 0 posts and a signature or website link.
Looking at my phpmyadmin tables
num_posts <1 + signature (not null)
and / or
num_posts <1 + url (not null)
then
delete user from table
This is one (well parts of) I was shown for PhpBB
Should look easier for PunBB as we have the IP stored ...
// note before: these are snippets taken directly from an admin script; I'm not going to show everything!
// `$cnx' is the connection resource for the phpBB2 db
// `sudoers' needs to be setup before to allow the `sudo` CLI command to work
$C_USR_WHERE = "(`user_active` > 0) and (`user_posts` < 1) and ((`user_website` > '') or (`user_sig` > ''))";
$C_USR_MAN_ON = ( mysql_result( mysql_query("SELECT COUNT(*) FROM users WHERE $C_USR_WHERE", $cnx ), 0 ) > 0 )
? TRUE
: FALSE;
...
$select = '*';
$from = 'users';
$where = $C_USR_WHERE;
$order = 'user_id';
$r = mysql_query("
SELECT $select
FROM $from
WHERE $where
ORDER BY $order
LIMIT $cStart, $ShowRows
", $cnx );
$users = array();
while( $row = mysql_fetch_assoc( $r )) {
$users['user_id'][] = $row['user_id'];
$users['username'][] = $row['username'];
$users['user_website'][] = $row['user_website'];
$users['user_sig'][] = $row['user_sig'];
$users['user_interests'][] = $row['user_interests'];
$users['user_email'][] = $row['user_email'];
$users['time'][] = (( $row['user_lastvisit'] > $row['user_session_time'])
? $row['user_lastvisit']
: (( $row['user_session_time'] > $row['user_regdate'])
? $row['user_session_time']
: $row['user_regdate']
)
);
$oldSetting = ignore_user_abort( TRUE ); // in case it takes forever
if( !empty( $users['username'])) foreach( $users['username'] as $key => $username ) {
// phpBB2 does not store IPs in user record
// first check if there is a chance of getting it
if( $users['time'][ $key ] > $five_weeks_ago ) {
$str = "/bin/egrep -hm 1 '\"$username\"' /var/log/httpd/access_log /var/log/httpd/access_log.1 /var/log/httpd/access_log.2 /var/log/httpd/access_log.3 /var/log/httpd/access_log.4";
if(
( $str = trim(`sudo $str 2>&1`)) and
(( $pos = strpos( $str, ' ')) !== FALSE )
) {
$users['user_ip'][ $key ] = substr( $str, 0, $pos );
} else {
$users['user_ip'][ $key ] = '';
}
} else {
$users['user_ip'][ $key ] = '';
}
$userHidden->val = $users['user_id'][ $key ];
$Cip->val = $users['user_ip'][ $key ];
$Submit->val = 'Remove';
$remove = $Form->show( $userHidden->show() . $Cip->show() . $Start->show() . $Submit->show());
$Submit->val = 'Clear';
$clear = $Form->show( $userHidden->show() . $Clear->show() . $Start->show() . $Submit->show());
$rowStyleTog = ( $rowStyleTog ) ? FALSE : TRUE;
$Rows .= hTR(
hTD( $remove ).
hTD( $username ).
hTD( $users['user_email'][ $key ]).
hTD( $users['user_website'][ $key ]).
hTD( $users['user_sig'][ $key ]).
hTD( $users['user_interests'][ $key ]).
hTD( $clear )
,'','','','','','','','', (( $rowStyleTog ) ? $StyleHi : ''));
}
ignore_user_abort( $oldSetting ); // don't mind user-abort now
$TableHead = $Form->show( hTHead(
hTR( hTD( $search->show(), 6 )).
$NavHead.
hTR( hTD('', 6 )).
hTR(
hTH().
hTH('Username '. _T_DESC ).
hTH('Email Address').
hTH('Website').
hTH('Signature').
hTH('Interests').
hTH()
)
,'', 'middle'));Guess you really hate spammers, right? 
Anyway I believe the SQL query should be something like this
DELETE FROM punbb_users WHERE num_posts = 0 AND registered < NOW()-2592000 AND (signature IS NOT NULL OR url NOT NULL)//should delete all users older than month with zero posts and sig or url not null
But I suggest you to make a backup since I haven't tested it (yup, still without Internet connection at home... I HATE MY PROVIDER arghh... 
)
Added "IS"
Guess you really hate spammers, right?
Not even. It's more along the lines: why let them make money or defraud your forum users...
Thanks for code but I get this;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL OR url NOT NULL)' at line 1This worked (thx Grez and Alex Kemp)!:
just noticed I had given the table a prefix (so fer instead of punbb)
and more importantly:
IS NOT NULL is the correct syntax it turns out
DELETE FROM fer_users
WHERE
( num_posts = 0 ) AND
( registered < ( NOW() - 2592000 )) AND
(
( signature IS NOT NULL ) OR
( url IS NOT NULL )
)FYI; Just received this response/instruction to set it as a daily cron job.
Might be useful to others
=============================
Put your above sql in a file called /root/delete.sql and create a shell script as follows /root/delsqlusers.sh:
Code:
---------
#!/bin/bash
mysql -u 'DBuser' -p'DBpassword' -h localhost punbbDBName < /root/delete.sql
---------
Test it with dummy data and once it worked set up real db name and call /root/delsqlusers.sh using a daily cron job.
=============================
contrib by:
rockdalinux @
http://nixcraft.com/shell-scripting/154 … -post.html
Damn "IS" forgot about that 
Guess this script should be used on this forum as well... I was updating Who uses PunBB at Wiki, and found out, that in last two years this forum grown with 26k new users, but only 3k topics and 20k posts
PunBB Forums → Feature requests → Script that kills/deletes any user with 0 posts and a signature or www
Powered by PunBB, supported by Informer Technologies, Inc.