Topic: 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 smile )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

Re: Script that kills/deletes any user with 0 posts and a signature or www

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'));

3 (edited by Grez 2010-10-12 17:26)

Re: Script that kills/deletes any user with 0 posts and a signature or www

Guess you really hate spammers, right? big_smile

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...  hmm)

Added "IS"  big_smile

Eraversum - scifi browser-based online webgame

Re: Script that kills/deletes any user with 0 posts and a signature or www

Grez wrote:

Guess you really hate spammers, right? big_smile

smile 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 1

Re: Script that kills/deletes any user with 0 posts and a signature or www

This 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 )
   )

Re: Script that kills/deletes any user with 0 posts and a signature or www

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

7

Re: Script that kills/deletes any user with 0 posts and a signature or www

Damn "IS" forgot about that  neutral  big_smile

Eraversum - scifi browser-based online webgame

8

Re: Script that kills/deletes any user with 0 posts and a signature or www

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 big_smile

Eraversum - scifi browser-based online webgame