|
|
Posted by Joseph Melnick on 06/27/05 04:37
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/
Navigation:
[Reply to this message]
|