The secure CMS.

Welcome to Geeklog, Anonymous Friday, November 08 2024 @ 09:06 pm EST

Geeklog Forums

UserDB Cleanup Plugin

 New Topic  Post Reply
 07/18/04 08:16am (Read 2,911 times)  

tokyoahead

Anonymous
Hi,

I have started to write a small function that should help to clean up the user Db. IF anyone would like to contribute, please feel free to join in.

To start off, paste this into a PHP-Enabled static page.
This first part lists users that have never logged in, the second lists the top ten of the users that did not login for the longest time. I imagine more functions, a feature to send emails to user to ask them if they want to keep the account or prefer beeing deleted.

Text Formatted Code

$sql="SELECT username, {$_TABLES['users']}.regdate, {$_TABLES['users']}.uid FROM {$_TABLES['users']} ";
$sql.="LEFT JOIN {$_TABLES['userinfo']} ON {$_TABLES['users']}.uid={$_TABLES['userinfo']}.uid ";
$sql.="WHERE {$_TABLES['userinfo']}.lastlogin=0 AND {$_TABLES['users']}.uid>1 ";
$sql.="ORDER BY {$_TABLES['users']}.regdate";

$result = DB_query($sql);

$nrows = DB_numRows($result);

$display.=COM_startBlock("Users that never logged in: ($nrows Users found)");

$display.="<Table>";
$display.="<TR><TD><B>No</B></TD><TD><B>Name</B></TD><TD><B>Reg. Date</B></TD></TR>";
for ($i=0;$i<$nrows;$i++)
    {
    $j=$i+1;
    $user = DB_fetchArray($result);
    $display.="<TR><TD>$j:</TD><TD><A HREF=\"";
    $display.=$_CONF['site_admin_url']."/user.php?mode=edit&uid=". $user['uid'] ."\">";
    $display.=$user['username']."</A></TD>";
    $display.="<TD>".$user['regdate']."</TD></TR>";
    }

$display.="</Table>";
$display.=COM_endBlock();


$sql="SELECT username, {$_TABLES['userinfo']}.lastlogin, {$_TABLES['users']}.uid FROM {$_TABLES['users']} ";
$sql.="LEFT JOIN {$_TABLES['userinfo']} ON {$_TABLES['users']}.uid={$_TABLES['userinfo']}.uid ";
$sql.="WHERE {$_TABLES['users']}.uid>1 AND {$_TABLES['userinfo']}.lastlogin>0 ";
$sql.="ORDER BY {$_TABLES['userinfo']}.lastlogin LIMIT 10";

$result = DB_query($sql);

$nrows = DB_numRows($result);

$display.=COM_startBlock("Users that did not login for a long time: (Top 10)");

$display.="<Table>";
$display.="<TR><TD><B>No</B></TD><TD><B>Name</B></TD><TD><B>Last Login</B></TD></TR>";
for ($i=0;$i<$nrows;$i++)
    {
    $j=$i+1;
    $user = DB_fetchArray($result);
    $display.="<TR><TD>$j:</TD><TD><A HREF=\"";
    $display.=$_CONF['site_admin_url']."/user.php?mode=edit&uid={$user['uid']}\">";
    $display.=$user['username']."</A></TD>";
    $lastlogin=date("Y.m.d H:i:s",$user['lastlogin']);
    $display.="<TD>$lastlogin</TD></TR>";
    }

$display.="</Table>";
$display.=COM_endBlock();


echo $display;



 
 Quote
 07/18/04 12:38pm  
Status: offline

destr0yr

Forum User
Full Member
Registered: 07/06/02
Posts: 324
The "i hate tables version"

Text Formatted Code

$sql="SELECT username, {$_TABLES['users']}.regdate, {$_TABLES['users']}.uid FROM {$_TABLES['users']} ";
$sql.="LEFT JOIN {$_TABLES['userinfo']} ON {$_TABLES['users']}.uid={$_TABLES['userinfo']}.uid ";
$sql.="WHERE {$_TABLES['userinfo']}.lastlogin=0 AND {$_TABLES['users']}.uid>1 ";
$sql.="ORDER BY {$_TABLES['users']}.regdate";

$result = DB_query($sql);

$nrows = DB_numRows($result);

$display.= COM_startBlock('Users that never logged in: (' . $nrows . ' Users found)');

$display.= '<div style="width: 100%;">';
$display.= '<div style="width: 5%;
                        display: inline;
                        font-weight: bold;
                        border-bottom: 1px solid #cccccc;
                        padding: 5px 5px 2px 5px;
                        margin: 2px;">No</div>
            <div style="width: 40%;
                        display: inline;
                        font-weight: bold;
                        border-bottom: 1px solid #cccccc;
                        padding: 5px 5px 2px 5px;
                        margin: 2px;">Name</div>
            <div style="width: 40%;
                        display: inline;
                        font-weight: bold;
                        border-bottom: 1px solid #cccccc;
                        padding: 5px 5px 2px 5px;
                        margin: 2px;">Reg. Date</div>';
                       
for ($i=0;$i<$nrows;$i++) {
    $j=$i+1;
    $user = DB_fetchArray($result);
    $display.= '<div style="width: 5%;
                            display: inline;
                            padding-left: 5px;
                            margin-left: 2px;">' . $j . ':</div>
                <div style="width: 40%;
                            display: inline;
                            padding-left: 5px;
                            margin-left: 2px;"><a href="';
    $display.= $_CONF['site_admin_url'] . '/user.php?mode=edit&uid=' . $user['uid'] . '">';
    $display.= $user['username'].'</a></div>';
    $display.='<div style="width: 40%; display: inline; padding-left: 5px; margin-left: 2px;">'.$user['regdate'].'</div>';
    $display.='';
    }

$display.=COM_endBlock();


$sql="SELECT username, {$_TABLES['userinfo']}.lastlogin, {$_TABLES['users']}.uid FROM {$_TABLES['users']} ";
$sql.="LEFT JOIN {$_TABLES['userinfo']} ON {$_TABLES['users']}.uid={$_TABLES['userinfo']}.uid ";
$sql.="WHERE {$_TABLES['users']}.uid>1 AND {$_TABLES['userinfo']}.lastlogin>0 ";
$sql.="ORDER BY {$_TABLES['userinfo']}.lastlogin LIMIT 10";

$result = DB_query($sql);

$nrows = DB_numRows($result);

$display.=COM_startBlock("Users that did not login for a long time: (Top 10)");

$display.= '<div style="width: 100%;">';
$display.= '<div style="width: 5%;
                        display: inline;
                        font-weight: bold;
                        border-bottom: 1px solid #cccccc;
                        padding: 5px 5px 2px 5px;
                        margin: 2px;">No</div>
            <div style="width: 40%;
                        display: inline;
                        font-weight: bold;
                        border-bottom: 1px solid #cccccc;
                        padding: 5px 5px 2px 5px;
                        margin: 2px;">Name</div>
            <div style="width: 40%;
                        display: inline;
                        font-weight: bold;
                        border-bottom: 1px solid #cccccc;
                        padding: 5px 5px 2px 5px;
                        margin: 2px;">Reg. Date</div>';
for ($i=0;$i<$nrows;$i++)
    {
    $j=$i+1;
    $user = DB_fetchArray($result);
    $display.='<div style="width: 5%;
                           display: inline;
                           padding-left: 5px;
                           margin-left: 2px;">' . $j . ':</div>
                <div style="width: 40%;
                           display: inline;
                           padding-left: 5px;
                           margin-left: 2px;"><a href="';
    $display.= $_CONF['site_admin_url'].'/user.php?mode=edit&uid=' . $user['uid'] . '">';
    $display.= $user['username'].'</a></div><div style="width: 40%; display: inline; padding-left: 5px; margin-left: 2px;">';
    $lastlogin= date("Y.m.d H:i:s",$user['lastlaogin']);
    $display.='' . $lastlogin . '</div>';
    }

$display.='</div>';
$display.=COM_endBlock();


echo $display;


 

staticpage it, uncheck in a block, select "execute php", and change security settings to the appropriate groups (ie. user admin)
-- destr0yr
"I love deadlines. I like the whooshing sound they make as they fly by." -- Douglas Adams
 Quote
 07/18/04 05:13pm  

tokyoahead

Anonymous
Any suggestions on additional functions?
 Quote
 07/18/04 06:59pm  

trench

Anonymous
SOmeone should follow up on this and make a final copy for the download section. This would be a good addition for all GL users.
 Quote
 07/20/04 09:54pm  
Status: offline

destr0yr

Forum User
Full Member
Registered: 07/06/02
Posts: 324
Quote by tokyoahead: Any suggestions on additional functions?

- A "delete all non-logged in users" option.
- Select how many users to display... maybe search by criteria and dates. (ie. delete all users that have not logged in since 2002)
-- destr0yr
"I love deadlines. I like the whooshing sound they make as they fly by." -- Douglas Adams
 Quote
 02/22/05 10:27am  
Status: offline

rjrufo

Forum User
Regular Poster
Registered: 06/14/03
Posts: 95
For some reason, this didn't work for me until I added global $_TABLES, $_CONF; before your code.

Just FYI...
 Quote
 New Topic  Post Reply
All times are EST. The time is now 09:06 pm.
  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content