Welcome to Geeklog, Anonymous Tuesday, July 15 2025 @ 03:10 am EDT
Geeklog Forums
UserDB Cleanup Plugin
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.
$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;
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;
18
14
Quote
Status: offline
Forum User
Full Member
Registered: 07/06/02
Posts: 324
The "i hate tables version"
$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)
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)
16
13
Quote
Any suggestions on additional functions?
19
15
Quote
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.
14
15
Quote
Status: offline
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)
17
14
Quote
Status: offline
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...
Just FYI...
14
19
Quote
All times are EDT. The time is now 03:10 am.
- 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