Posted by Matthias Hoys on 06/26/05 23:14

"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

[Back to original message]