Viewing Dormant Players

[dbest]

I had to struggle yesterday to figure out how to find dormant players in the game. Dormant players are those who have not logged in for a certain period of days, 30 days in my case. Decided to make a post so that it may help someone in the future and also so that anyone can improve upon my implementation.


Every time a user logins to the game, the userid & the time of login is stored in a table called "logins", as such:

Code: [Select]

INSERT INTO logins(id_user,time_login) VALUES ('$user_id',NOW())
The userid & other user details are stored in a table called "users".

To view all users who have not logged during the past 30 days, the following query can be used:

Code: [Select]

SELECT user_id FROM users WHERE user_id NOT IN (SELECT id_user FROM logins WHERE DATEDIFF(CURDATE(), time_login) < 30)
This may not be the best way to do it, so anyone with a better way of doing this, please feel free to post their implementation.

[saljutin]

why do you store every login? do you need to know times for every user for last week?

[dbest]

good question. main reason is to get rid of dormant users.

[jannesiera]

why do you store every login? do you need to know times for every user for last week?

It can be handy for statistics .

[Nox]

nice... I see it mostly as security-related feature so many ways to percieve it

I too have login log and beside that I also have lastclick entry so it's possible just

Code: [Select]

select * from users where lastclick < NOW() - interval 30 day
edit: of course, ip there, didn't want to get into details

[saljutin]

well for security u should add some IP as well

but why dont you use something like:

SELECT id_user,MAX(time_login) FROM logins GROUP BY id_user

and then use that in php to compare that time of last login to the one now (time())
I think above query should work, but I didnt test it

[dbest]

i have the IP address in my table too, but didnt mention it, as it was not the focus..

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[die4me]

That is a good idea i used that and put a last date active on there profile

[dbest]

Could do that too ST-Mike. But as you mentioned there are other reasons to create a separate logins table.

[Harkins]

Ignoring the other reasons to have a seperate login table, to find out which users are dormant why not just store UNIX_TIMESTAMP() into whatever main users table every time they login (or are active?) and then do a search on that (SELECT `id`,`username` FROM `users` WHERE `time_login/time_activity` < UNIX_TIMESTAMP()-(86400*30). I would have thought that's the best way to find dormant users.

No need deal with unix timestamps and multiplication, the standard date and time functions are easier and clearer:

Code: [Select]

select id, username from users where last_activity_at >= date_sub(now(), interval 30 day);


[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.