I need a "pruning" too for MySQL

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..

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/

"Joseph Melnick" <jmelnick@jphp.com> wrote in message
news:epydnQPwmtYXXSDfRVn-jg@rogers.com...
> Does your table have a created OR modified field?
<snip>

I'm afraid this is beyond my expertise. I can log onto cpanel and go to
PHPMyAdmin and run commands. I would load a certian table, which contains an
entry for each player's name. In that table is a date, in Linux format, that
says when it was last updated.

Andy in NJ Wrote:

"Andy in NJ" <ADMIN at FLOTSERVER DOT NET> wrote in message
news:t8ednV3Qs-NASCDfRVn-2A@comcast.com...
>
> "Joseph Melnick" <jmelnick@jphp.com> wrote in message
> news:epydnQPwmtYXXSDfRVn-jg@rogers.com...
>> Does your table have a created OR modified field?
> <snip>
>
> I'm afraid this is beyond my expertise. I can log onto cpanel and go to
> PHPMyAdmin and run commands. I would load a certian table, which contains
> an entry for each player's name. In that table is a date, in Linux format,
> that says when it was last updated.

Run the first Query in PHPMyAdmin to get the list of Players that match your
criteria and change INTERVAL 30 DAY to the number of inactive days that
makes sense. This will give you a list of player_id's, and when they were
last modified.


SELECT a.player_id,MAX(a.modified)
FROM players a
WHERE DATE_ADD( FROM_UNIXTIME(a.modified),INTERVAL 30 DAY) < now()
GROUP BY a.player_id;

Get:
player_id modified
1 2005-01-01 12:00:00
5 2005-04-28 01:39:22
....
212 2004-12-31 23:59:59


then run this Query where player is in a comma separated list of
player_id's:

DELETE FROM stats WHERE player_id IN (1, 5, ....,212);


Joseph Melnick
JM Web Consultants
Toronto, Ontario, Canada
http://www.jphp.com/

"Joseph Melnick" <jmelnick@jphp.com> wrote in message
news:Ad2dnROpdevBVCPfRVn-hw@rogers.com...
> Andy in NJ Wrote:
>
> "Andy in NJ" <ADMIN at FLOTSERVER DOT NET> wrote in message
> news:t8ednV3Qs-NASCDfRVn-2A@comcast.com...
>>
>> "Joseph Melnick" <jmelnick@jphp.com> wrote in message
>> news:epydnQPwmtYXXSDfRVn-jg@rogers.com...
>>> Does your table have a created OR modified field?
>> <snip>
>>
>> I'm afraid this is beyond my expertise. I can log onto cpanel and go to
>> PHPMyAdmin and run commands. I would load a certian table, which contains
>> an entry for each player's name. In that table is a date, in Linux
>> format, that says when it was last updated.
>
> Run the first Query in PHPMyAdmin to get the list of Players that match
> your criteria and change INTERVAL 30 DAY to the number of inactive days
> that makes sense. This will give you a list of player_id's, and when they
> were last modified.
>
>
> SELECT a.player_id,MAX(a.modified)
> FROM players a
> WHERE DATE_ADD( FROM_UNIXTIME(a.modified),INTERVAL 30 DAY) < now()
> GROUP BY a.player_id;
>
> Get:
> player_id modified
> 1 2005-01-01 12:00:00
> 5 2005-04-28 01:39:22
> ...
> 212 2004-12-31 23:59:59
>
>
> then run this Query where player is in a comma separated list of
> player_id's:
>
> DELETE FROM stats WHERE player_id IN (1, 5, ....,212);
>
>
> Joseph Melnick
> JM Web Consultants
> Toronto, Ontario, Canada
> http://www.jphp.com/
>
>
>

Why not :

DELETE FROM stats WHERE player_id IN (
SELECT a.player_id,MAX(a.modified)
FROM players a
WHERE DATE_ADD( FROM_UNIXTIME(a.modified),INTERVAL 30 DAY) < now()
GROUP BY a.player_id;
);


Matthias

Mr. Hoys Wrote:

"Matthias Hoys" <idmwarpzone_NOSPAM_@yahoo.com> wrote in message
news:42bf0c94$0$325$ba620e4c@news.skynet.be...
>
> "Joseph Melnick" <jmelnick@jphp.com> wrote in message
> news:Ad2dnROpdevBVCPfRVn-hw@rogers.com...
>> Andy in NJ Wrote:
>>
>> "Andy in NJ" <ADMIN at FLOTSERVER DOT NET> wrote in message
>> news:t8ednV3Qs-NASCDfRVn-2A@comcast.com...
>>>
>>> "Joseph Melnick" <jmelnick@jphp.com> wrote in message
>>> news:epydnQPwmtYXXSDfRVn-jg@rogers.com...
>>>> Does your table have a created OR modified field?
>>> <snip>
>>>
>>> I'm afraid this is beyond my expertise. I can log onto cpanel and go to
>>> PHPMyAdmin and run commands. I would load a certian table, which
>>> contains an entry for each player's name. In that table is a date, in
>>> Linux format, that says when it was last updated.
>>
>> Run the first Query in PHPMyAdmin to get the list of Players that match
>> your criteria and change INTERVAL 30 DAY to the number of inactive days
>> that makes sense. This will give you a list of player_id's, and when they
>> were last modified.
>>
>>
>> SELECT a.player_id,MAX(a.modified)
>> FROM players a
>> WHERE DATE_ADD( FROM_UNIXTIME(a.modified),INTERVAL 30 DAY) < now()
>> GROUP BY a.player_id;
>>
>> Get:
>> player_id modified
>> 1 2005-01-01 12:00:00
>> 5 2005-04-28 01:39:22
>> ...
>> 212 2004-12-31 23:59:59
>>
>>
>> then run this Query where player is in a comma separated list of
>> player_id's:
>>
>> DELETE FROM stats WHERE player_id IN (1, 5, ....,212);
>>
>>
>> Joseph Melnick
>> JM Web Consultants
>> Toronto, Ontario, Canada
>> http://www.jphp.com/
>>
>>
>>
>
> Why not :
>
> DELETE FROM stats WHERE player_id IN (
> SELECT a.player_id,MAX(a.modified)
> FROM players a
> WHERE DATE_ADD( FROM_UNIXTIME(a.modified),INTERVAL 30 DAY) < now()
> GROUP BY a.player_id;
> );
>
>
> Matthias

Hello Mattias,

Depending on the version of MySQL that Andy is using he may not be able to
do a sub select as you suggest.

Joseph Melnick
JM Web Consultants
Toronto, Ontario, Canada
http://www.jphp.com/