Quickest way to delete records rows?

[TEENFRONT]

Hey all,

 

Just  a quickie, basically, every week i run a cron job that removes players from the database who havent logged into our site for 4months. This is to keep the database trim and onl with active users.

 

Basically this script currently loops through each player and "if play hasnt logged in for 4months" it runs a delete query. We have some 45,000 player records in our database.

 

Im just thinking about it, and would it be faster, better, to first query the database and put all the usernames in an array that hasnt logged in for 4months, THEN run 1 single delete query?

 

If so how would i do this? as i dont calculate the date logged in untill we get to the loop of the first query..

 

My current code is like this... 

 

function convert_timestamp($timestamp, $format) {
ereg("([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})", $timestamp, $regs);
$new_timestamp = mktime($regs[4],$regs[5],$regs[6],$regs[2],$regs[3],$regs[1]);
$date = date($format, $new_timestamp);
return $date;
}

$result = mysql_query("select last_session_start, useremail, usernick from gamezer_users_1 WHERE userstatus=1 AND last_session_start !=0");

while($r=mysql_fetch_array($result))
{ 
$last_session_start = $r["last_session_start"];
$timestamp = $last_session_start;
$registered = convert_timestamp($timestamp, "d.m.Y");
$start = $registered;
$end = date('d.m.Y');

$difference = (strtotime($end)-strtotime($start));

$days = $difference/24; //hours in one day
$days = $days/60; //minutes in one hour
$days = $days/60; //seconds in one minute
$days = ceil($days); //rounding up

if($days >= 120) {
$useremail = $r["useremail"];

mail($to, $subject, $message, $headers);
mysql_query("DELETE FROM gamezer_users_1 WHERE useremail='$useremail'") or die(mysql_error()); 
}

}

 

 

How would i optimize the above to work like this.

 

ONLY select users who havent logged in for 120days from the query (not selecting everyone, then figuring out the days)

Use one query to delete all the old players (not one query for each player)

 

Id really really appreciate any pointers or help that can be given to me!

 

 

[PFMaBiSmAd]

You would normally just do this in a single DELETE query, no looping is necessary.

 

You would just form a WHERE clause in the DELETE query that deletes the rows you want, assuming that your date/time information is in format where you can use the mysql date and time functions to do the date/time comparison in the query.

[TEENFRONT]

Thanks for the reply..

 

My dates are saved like this ...    "20100301213154587988"  .. iv never actually been able to figure out what format that is, and iv had to use the convert_timestamp function that came with the developers code.

 

Do you know what this format is ? 20100301213154587988 ? and if so, how would i calculate 120 less than 20100301213154587988 ?

 

Much appreciated!

[PFMaBiSmAd]

Based on what the code is doing, that format is made up of YYYY, MM, DD, HH, MM, SS, milliseconds, and microseconds. The mysql data/time functions understand that, but I threw in a LEFT() function in the following to be safe for any future changes mysql might make to how it handles warnings. This single query will delete rows for users who have a last_session_start date more than 120 days ago -

 

$result = mysql_query("DELETE FROM gamezer_users_1 WHERE userstatus=1 AND last_session_start !=0 AND 
DATEDIFF(CURDATE(),DATE(LEFT(last_session_start,14))) > 120");

 

This only addresses the DELETE operation. If you still want to send the email(s), you would need to convert that to a SELECT query, then loop through the rows that are returned to send the email, then execute it as a DELETE query.