|
|
Posted by Joseph Melnick on 06/25/05 23:37
Andy in NJ Wrote:
"Andy in NJ" <ADMIN at FLOTSERVER DOT NET> wrote in message
news:R-udncBZysoNOiDfRVn-iA@comcast.com...
>I am looking for a tool that will allow me to "prune" my MySQL database by
>entering a specific number of days since a particular entry was updated,
>and if it exceeds that, it deletes the entry. Specifically, I have a
>database of player statistics from an online game server and I want to make
>it so if someone hasn't had their stats added to/updated, I want them
>deleted..
Hello Andy,
Does your table have a created OR modified field?
Given Table below:
create table stats (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
player_id INT NOT NULL,
stat INT NOT NULL DEFAULT '0',
modified DATETIME );
Running a query like the one shown below will identify data for player_id 's
that you want to prune.
SELECT a.player_id,MAX(a.modified)
FROM stats a
WHERE DATE_ADD( a.modified,INTERVAL 30 DAY) > now()
GROUP BY a.player_id;
NEXT:
DELETE FROM stats
WHERE player_id = IDENTIFIED TO BE DELETED
with PHP:
<?php
// deleteinactiveplayerdata.php
// delete inactive players stats older than this number of days old.
$lastmodified = "30";
$dbhost = "mydatabasehost";
$dbuser = "mydatabaseuser";
$dbpass = "mypassword";
$dbname = "mydatabase";
// connect to database
$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
// identify inactive players where most recent stat modified datetime
// plus $lastmodified days is less than now.
$query = "SELECT a.player_id, MAX(a.modified) max_modified " .
"FROM stats a " .
"WHERE DATE_ADD( a.modified,INTERVAL ".$lastmodified." DAY)
< now() " .
"GROUP BY a.player_id";
$result = mysql_query($query,$dblink);
while($row = mysql_fetch_array($result)) {
$player_id = $row["player_id"];
$max_modified = $row["max_modified"];
// delete all stats for players identified as inactive
$query2 = "DELETE FROM stats WHERE player_id = ".$player_id."";
mysql_query($query2,$dblink);
}
?>
I hope that this helps.
Joseph Melnick
JM Web Consultants
Toronto, Ontario, Canada
http://www.jphp.com/
Navigation:
[Reply to this message]
|