How to find the inactive users?

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
trv
Registered User
Posts: 64
Joined: Sun Nov 07, 2004 4:39 pm

How to find the inactive users?

Post by trv »

In phpbb_users, each user has fields

user_session_time
user_lastvisit
user_regdate
and user_posts

What's the best think to try to find the inactive users?
By inactive i mean users with 0 or 1 posts, that have not loged in for over 1 years since they registered lets say..

It shouldn't be hard, just an sql query involving the fields mentioned above. What's session_time field?

Can anyone suggest an ideal sql query for what i'm trying to do?

[ are there any mods acting like this? ]

Andrew Kucienski
Registered User
Posts: 1050
Joined: Mon Oct 24, 2005 2:06 am
Location: Far far away!!!

Post by Andrew Kucienski »

Admin Toolkit
An Admins most helpful tool for user management. Now Supports Mass User Deletion!
Change User's: names, passwords, emails, active status and avatar/pm permissions.
Ban/Unban Users, change Post and Resync Counts, and promote/demote users to admin.
Completely independent from your phpbb user account settings. No installation required, just upload one file.

trv
Registered User
Posts: 64
Joined: Sun Nov 07, 2004 4:39 pm

Post by trv »

Thank you for your reply, but this script is not quite what i'm looking for..

Andrew Kucienski
Registered User
Posts: 1050
Joined: Mon Oct 24, 2005 2:06 am
Location: Far far away!!!

Post by Andrew Kucienski »

Can you explain a little better? The admin toolkit allows you to sort users by inactive/active and by post count. You can then delete in bulk if you want. Or are you looking to incorporate this into some other script?

php script for users with 1 or less posts (I don't know how to do the date): The results include ID and name for readability.

Code: Select all

SELECT `phpbb_users`.`user_posts`, `phpbb_users`.`user_id`, `phpbb_users`.`username`
FROM phpbb_users
WHERE (`phpbb_users`.`user_posts` <=1)
SQL Query for the same:

Code: Select all

$sql = 'SELECT `phpbb_users`.`user_posts`, `phpbb_users`.`user_id`, `phpbb_users`.`username`'
        . ' FROM phpbb_users'
        . ' WHERE (`phpbb_users`.`user_posts` <=1) LIMIT 0, 30 ';
Last edited by Andrew Kucienski on Thu Jan 26, 2006 2:36 pm, edited 1 time in total.

trv
Registered User
Posts: 64
Joined: Sun Nov 07, 2004 4:39 pm

Post by trv »

The problem with admin toolkit is that i don't know who is active or inactive defined it the script!
As i said, i want to see which users are inactive based strictly in the deference of days from the registered date until the last seen date, and the number of posts at the same time (lets say 0 posts)


So i want to find what users have 0 posts and at the same time have not visited the board for 1 year (these would be inactive users for me ;) )

Hope i explained my self a little better

Andrew Kucienski
Registered User
Posts: 1050
Joined: Mon Oct 24, 2005 2:06 am
Location: Far far away!!!

Post by Andrew Kucienski »

Using today's date in unix timestamp format (1106750319 = Wednesday, January 26th 2005, 14:38:39 (GMT) ) and selecting all users with less than 1 post (<1) and limited to 30 responses. This will displaye user_id and username.

SQL

Code: Select all

SELECT `phpbb_users`.`user_id` , `phpbb_users`.`username` 
FROM phpbb_users
WHERE (
(
`phpbb_users`.`user_lastvisit` >1106750319
)
AND (
`phpbb_users`.`user_posts` <1
)
)
LIMIT 0 , 30 
PHP

Code: Select all

$sql = 'SELECT `phpbb_users`.`user_id`,`phpbb_users`.`username` '
        . ' FROM phpbb_users'
        . ' WHERE ('
        . ' ('
        . ' `phpbb_users`.`user_lastvisit` >1106750319'
        . ' )'
        . ' AND ('
        . ' `phpbb_users`.`user_posts` <=1'
        . ' )'
        . ' )'
        . ' LIMIT 0 , 30 '; 
How is that?

trv
Registered User
Posts: 64
Joined: Sun Nov 07, 2004 4:39 pm

Post by trv »

thank you Andrew Kucienski, you've been very helpful here! It's a start, i will improve the code to adopt it completely to my needs. Thank you!

Locked

Return to “2.0.x Support Forum”