Automatic User Removal

Seraskier · 03-14-2009, 04:37 AM

Hello,

I'm working on a script and i'm a bit stuck.

I have been thinking of how to do this with no luck.

I have a register form (http://75.181.72.104/register.php) that upon registration records the registration date and time, with the row 'lastlogin' set as 'Never' for the default.

Now, I have http://75.181.72.104/inc/cleanup.php which i'm going to run every 5-30 minutes on my server. This script runs through and selects every user that has the row 'lastlogin' set as 'Never' and figures out if they are within 24 hours of their register date and time to the current time, if so, it deletes them.

My problem is writing a function that will take their register date and time, add 24 hours (default, customizable time frame based on database row, named 'loginwindow,' which is saved as the variable, $loginwindow) and match it with the current date and time, if they match, delete the user (of course, only if they haven't logged in yet).

I am thinking of:

Code:

<?php

while($row=mysql_fetch_array($query))
{

list($curmonth,$curday,$curyear)=split(' ',date("F d Y"));
list($curhour,$curmin,$curtod)  =split(' ',date("g i A"));

list($month,$day)=split('[ ,]',$row['signupdate']);
$year=substr($row['signupdate'],-4);
list($hour,$min,$tod)=split('[: ]',$row['signuptime']);

if($month==$curmonth && $year==$curyear) //to be safe
{
   if(date("G")-$loginwindow >= $hour)
   {
      $array[]=$row['id'];
   }
}
}
?>

I know I need to use the 24-hour format on the current time, but every time I do it makes it a negative time (i.e. -10).
It was thrown together real quick to get my point across.
Should I use the mktime function?
What about leap years? Registering on the last day of the month, or what about registering on December 31, 2009....

I am adding each user to an array, then using the foreach command to delete them, so that part is taken care of.

Here is my script, I know its very sloppy and not very well done, but my server is going to be the ONLY one using this, and well, as long it works its fine with me.

For shits and giggles, you can view the logs here: http://75.181.72.104/inc/logs/New%20Folder/

I put the part where the function would need to go in all caps, so it shouldnt be too hard to miss.
The code is also moderately commented. Right now, I am just focusing on it working; I will cleanup later.

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Cleaning up...</title>
    <script type="text/javascript">
	/* IE7
	window.open('','_self','');
	setTimeout(function()
				{
					window.close();
				},500);
	*/
	function closeWindow()
	{ //IE6
		this.focus();
		self.opener=this;
		self.close();
	}
</script>
</head>

<body style="font-family:Tahoma; font-size:11px">

<?php
//gain access to database
define("access",true);
require("database.php");

//logging information
$dir="logs/New Folder";
$file=date("y-M-d-h iA");

//start execution timer
$mtime=explode(" ",microtime());
$mtime=$mtime[1]+$mtime[0];
$starttime=$mtime;

print_r("Retrieving settings...");
//get loginwindow from settings table
$settings=mysql_query("SELECT * FROM settings");
while($settings=mysql_fetch_array($settings))
{
	$loginwindow=$settings['loginwindow'];
	$sendemails=$settings['sendemails'];
}
	
//get the admin usergroup id
$usergroup=mysql_query("SELECT * FROM usergroups WHERE name='Admin'");
while($usergroup=mysql_fetch_array($usergroup))
	{ $usergroup_adminId=$usergroup['id']; }

if(isset($loginwindow) && isset($sendemails) && isset($usergroup_adminId))
{
	print_r("complete.<br /><br />");
}
else
{
	print_r("failed.<br /><br /><br />Closing window....");
	?> <script type="text/javascript">closeWindow();</script> <?php
	exit();
}

//find and count users that have never logged in
//do not count any users that are admins
print_r("Counting users...");
$query=mysql_query("SELECT * FROM users WHERE lastlogin='Never' AND usergroup!='{$usergroup_adminId}'");
$count=mysql_num_rows($query);

if($count>1 || $count==0) { $s="s"; }
if($query)
{
	print_r("found $count user$s.<br /><br />");
}
else
{
	print_r("failed.<br /><br /><br />Closing window....");
	?> <script type="text/javascript">closeWindow();</script> <?php
	exit();
}

if($count==0)
{
	//we will not log anything if theres no users that need deletion
	print_r("<br />Complete. Closing window....");
	?> <script type="text/javascript">closeWindow();</script> <?php
	exit();
}
else
{
	//create a new log file
	print_r("Creating log file...");
	if(file_exists($dir."/!-".$file."-".$count."U.txt"))
		{ $fp=fopen($dir."/!-".$file."-".$count."U.txt","a+"); }
			else
		{ $fp=fopen($dir."/".$file."-".$count."U.txt","a+"); }
		
	if($fp)
	{
		print_r("complete.<br /><br />");
		//write known content to file
		fwrite($fp,"----------\nTimestamp: ".date("h:i:s A")."\n----------\n\n");
		fwrite($fp,"Retrieving settings...complete.\n\n");
		fwrite($fp,"Counting users...found $count user$s.\n\n");
		fwrite($fp,"Creating log file...complete.\n\n");
	}
		else
	{
		print_r("failed.<br /><br /><br />Closing window....");
		?> <script type="text/javascript">closeWindow();</script> <?php
		exit();
	}
}

//retrieve current date and time
print_r("Assigning current date and time to variables...");
fwrite($fp,"Assigning current date and time to variables...");
list($curmonth,$curday,$curyear)=split(' ',date("F d Y"));
list($curhour,$curmin,$curtod)  =split(' ',date("g i A"));
if(isset($curmonth) && isset($curday) && isset($curyear) && isset($curhour) && isset($curmin) && isset($curtod))
{
	print_r("complete.<br /><br />");
	fwrite($fp,"complete.\n\n");
}
	else
{
	$brokenVariable="";
		if(empty($curmonth)) { $brokenVariable.='$curmonth, '; }
		if(empty($curday)) { $brokenVariable.='$curday, '; }
		if(empty($curyear)) { $brokenVariable.='$curyear, '; }
		if(empty($curhour)) { $brokenVariable.='$curhour, '; }
		if(empty($curmin)) { $brokenVariable.='$curmin, '; }
		if(empty($curtod)) { $brokenVariable.='$curtod, '; }
	print_r("failed.<br />&nbsp;&nbsp;&nbsp;----------<br />");
	print_r("&nbsp;&nbsp;&nbsp;Reason: A variable failed to pass.<br />&nbsp;&nbsp;&nbsp;----------<br /><br />&nbsp;&nbsp;&nbsp;----------<br />&nbsp;&nbsp;&nbsp;&nbsp;Debug:&nbsp;The following variables are considered empty:&nbsp;".substr($brokenVariable,0,-2).".");
	print_r("<br />&nbsp;&nbsp;&nbsp;----------<br /><br /><br />Closing window....");
	fwrite($fp,"failed.\n   ------\n");
	fwrite($fp,"   Reason: A variable failed to pass.\n   -------\n\n   -------\n    Debug: The following variables are considered empty: ".substr($brokenVariable,0,-2).".");
	fwrite($fp,"\n   -------\n\n\nClosing window....\n\n\n\n");
	fclose($fp);
	rename($dir."/".$file."-".$count."U.txt",$dir."/!-".$file."-".$count."U.txt");
	?> <script type="text/javascript">closeWindow();</script> <?php
	exit();
}

//determine if user is within $loginwindow
//place ids into an array
print_r("Calculating time and date...");
fwrite($fp,"Calculating time and date...");
while($row=mysql_fetch_array($query))
{
	list($month,$day)=split('[ ,]',$row['signupdate']);
	$year=substr($row['signupdate'],-4);
	list($hour,$min,$tod)=split('[: ]',$row['signuptime']);
	
	
	//THIS IS WHERE I NEED THE FUNCTION
	//ADD IT TO THE ARRAY THREE LINES DOWN (LINE 161)
	//WHICH GETS PASSED TO THE FOREACH COMMAND FOR DELETION, 27 LINES DOWN (LINE 186)
	
	$array[]=$row['id'];
}
if(count($array)>0)
{
	print_r("complete.<br /><br />");
	fwrite($fp,"complete.\n\n");
}
else
{
	print_r("failed.<br />&nbsp;&nbsp;&nbsp;----------<br />");
	print_r("&nbsp;&nbsp;&nbsp;Reason: Array failed to populate with user ids and/or failed to connect to mysql table.<br />&nbsp;&nbsp;&nbsp;----------<br /><br />&nbsp;&nbsp;&nbsp;----------<br />&nbsp;&nbsp;&nbsp;&nbsp;Debug:&nbsp;(".mysql_errno().")&nbsp;".mysql_error().".");
	print_r("<br />&nbsp;&nbsp;&nbsp;----------<br /><br /><br />Closing window....");
	fwrite($fp,"failed.\n   -------\n");
	fwrite($fp,"   Reason: Array failed to populate with user ids and/or failed to connect to mysql table.\n   -------\n\n   -------\n    Debug: (".mysql_errno().") ".mysql_error().".");
	fwrite($fp,"\n   -------\n\n\nClosing window....\n\n\n\n");
	copy($dir."/".$file."-".$count."U.txt",$dir."/!-".$file."-".$count."U.txt");
	fclose($fp);
	unlink($dir."/".$file."-".$count."U.txt");
	?> <script type="text/javascript">closeWindow();</script> <?php
	exit();
}

//if they have not logged in within $loginwindow, delete their account
print_r("Deleting unverified accounts...<br />");
fwrite($fp,"Deleting unverified accounts...\n");
foreach($array as $id)
{
	$i=mysql_query("SELECT * FROM users WHERE id='{$id}'");
	while($j=mysql_fetch_array($i))
	{
		$user=$j['username']." | ".$j['email']." | ".$j['first']." ".$j['last']." | ".$j['signupdate']." | ".$j['signuptime']." | ".$j['ip']." | ".$j['usergroup']." | ".$j['id'];
		echo "&nbsp;&nbsp;&nbsp;&nbsp;".$user."<br />";
		fwrite($fp,"    ".$user."\n");
	}
	//mysql_query("DELETE FROM users WHERE id='{$id}'");
}
print_r("Complete.");
fwrite($fp,"Complete.");

//end execution timer
$mtime=explode(" ",microtime());
$mtime=$mtime[1]+$mtime[0];
$totaltime=($mtime-$starttime);

//end of script
print_r("<br /><br /><br />Completed in $totaltime seconds. Closing window....");
fwrite($fp,"\n\n\nCompleted in $totaltime seconds. Closing window....\n\n\n\n");
fclose($fp);
if(file_exists($dir."/!-".$file."-".$count."U.txt"))
	{ rename($dir."/!-".$file."-".$count."U.txt",$dir."/".$file."-".$count."U-!.txt"); }
?>
<!--<script type="text/javascript">closeWindow();</script>-->
</body>
</html>

I am also having a little problem with my date() function, my server time says 12:36, but when I echo the function it says 1:36. I have the php.ini set to default_timezone='America/New York';
If someone can help me with that, that would be great!

I can't even think straight anymore, I need a cig >_<.

Seraskier · 03-14-2009, 05:23 AM

I think I got it!

Can someone please verify for me that it will work, or make changes to it to where it will work for all occassions.

Code:

<?php
//gain access to database
define("access",true);
require("database.php");

//settings variables
$settings=mysql_query("SELECT * FROM settings");
while($settings=mysql_fetch_array($settings))
{
	$loginwindow=$settings['loginwindow'];
}
echo "Login window: ".$loginwindow." hours<br /><br />";


//current date and time
list($hour,$minute,$second)=split(" ",date("H i s"));
list($month,$day,$year)    =split(" ",date("m d Y"));
$current=mktime($hour-$loginwindow,$minute,$second,$month,$day,$year);

//users registration date and time
$query=mysql_query("SELECT * FROM users WHERE id='93'");
while($row=mysql_fetch_array($query))
{
	list($hr,$mn,$sd)=split(":",$row['signuptime']);
	list($mh,$dy,$yr)=split(" ",$row['signupdate']);
	$signup=mktime($hr,$mn,$sd,$mh,$dy,$yr);
}

echo "Current time: ".$hour." ".$minute." ".$second." | ".$month." ".$day." ".$year."<br />";
echo "Signup time : ".$hr." ".$mn." ".$sd." | ".$mh." ".$dy." ".$yr."<br /><br />";

echo "Current mktime: ".$current."<br />";
echo "Signup mktime : ".$signup;

echo "<br /><br />";

if($signup < $current)
{
	echo "delete";
}
else
{
	echo "do not delete.";
}
?>

Seraskier · 03-14-2009, 07:02 AM

Okay, fresh code.

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>Dumping Unverified Users...</title>
</head>

<body>
<?php
//gain access to database
define("access",true);
require("database.php");


//logging directory & filename
$file=date("y-H i").".txt";
$dir="logs/UserDumps/".date("M")."/".date("d")."/".$file;

//query users who have not logged in
//retrieve information and count users
$query=mysql_query("SELECT * FROM users WHERE lastlogin='Never'");
$count=mysql_num_rows($query);

//retrieve settings
$settings=mysql_query("SELECT * FROM settings");
while($setting=mysql_fetch_array($settings))
	{ $loginwindow=$setting['loginwindow']; }

//current date and time
$current=strtotime("now");

//users registration date and time
while($row=mysql_fetch_array($query))
{
	list($hour,$minute,$second)=split(":",$row['signuptime']);
	list($month,$day,$year)    =split(" ",$row['signupdate']);
	$signup=mktime(($hour+$loginwindow),$minute,$second,$month,$day,$year);

	//determine if the user needs to be deleted
	//if so, add the user to the array
	if($signup < $current)
	{
		$array[]=$row['id'];
	}
}

//delete users contained in the array
foreach($array as $id)
{
	$i=mysql_query("SELECT * FROM users WHERE id='{$id}'");
	while($row2=mysql_fetch_array($i))
	{
		echo "<br />".$row2['username']." | ".$row2['signupdate']." - ".$row2['signuptime'];
	}
}
?>
</body>
</html>

From what I tested it works great.

hello-world · 03-15-2009, 10:41 PM

I really like it.And it does work.

Andrial12 · 03-23-2009, 02:26 PM

You could also solve this problem in in your database. I've always thought working with dates in MySQL is far easier that in PHP.

For example.

PHP Code:


			
DELETE FROM `users`

WHERE

    `signupdate` < DATE_SUB( NOW(), INTERVAL 24 HOUR )

AND `lastlogin` = `signupdate`

With this method you use datetime as a field rather than varchar. Also you would set the `lastlogin` field to the same as the signup time, that way if they're the same after 24 hours mysql will delete them.

For for information on INTERVAL:
http://dev.mysql.com/doc/refman/5.1/...ction_date-add

Hope that helps.

NB: I've not tested this so try it on test data so you don't empty your entire database :D

Seraskier · 03-23-2009, 06:36 PM

Quote:

Originally Posted by Andrial12

PHP Code:


			
DELETE FROM `users`

WHERE

    `signupdate` < DATE_SUB( NOW(), INTERVAL 24 HOUR )

AND `lastlogin` = `signupdate`

How efficient is this block of code compared to mine?
My timer says ~0.002 seconds to process.

I have a dynamic time-frame based on the database...so would be using...

PHP Code:


			
//theres more code to get the variable

//but for demonstration

$timeframe=$row['loginwindow'];



DELETE FROM `users`

WHERE

    `signupdate` < DATE_SUB( NOW(), INTERVAL {$timeframe} HOUR )

AND `lastlogin` = `signupdate`

be correct?

Andrial12 · 03-23-2009, 07:51 PM

Yes that would be correct however you may be better using the MINUTE keyword if you need to use fractions of an hour as afaik the variable has to be an integer for example:

PHP Code:


			
$sql = "

    DELETE FROM `users` 

    WHERE 

        `signupdate` < DATE_SUB( NOW(), INTERVAL {" . $timeframe . "} MINUTE ) 

    AND `lastlogin` = `signupdate`;

";

I've not got a database to hand at the moment to test it on, however I would have thought it was faster on larger tables as your passing less data between your database server and your web server. Of course if your expecting a large number of these false sign ups I would suggest putting an index on the date column too.

In truth however, selecting everything from a table is seldom a good idea. I personally would use MySQL for anything like this as thats what it was designed to do.

Also in your case I would definatly recommend using MySQL for this job, as you intend to cron the script for every 5-30 mins, this way your not passing your whole users table to your webserver every few minutes for it not to alter any records.

Hope that helps

Seraskier · 03-24-2009, 12:27 AM

I only programmed it to be 0-infinite hours, no fractions of an hour. In theory you could use .25 in the setting, but it has not been tested.

In my new update I did change it to only select the rows I needed. Also, one of the first thing it does is get the timeframe, which as of right now its set to 0, and stops the script prematurely, so I do not think it's using too much bandwidth. I am monitoring it with AWStats to get a gist of the bandwidth it does consume.
I know I do need to rewrite the code to be a bit more efficient. On every phase of the script it logs the output; I need to save it as a variable and then at the end save the output to a text file and print the results as well only if logging is required.

I will paste what you submitted into a test php file and see what I can get going. I will be sure to post the code once it's working.

Jordan

Andrial12 · 03-24-2009, 08:39 AM

Best of look. As with anything that involves MySQL you are best trying a few different solutions to see which one is fastest for you, as not all data is equal.