Checking if a WordPress User Has Ever Logged In

Written by Mike on February 25, 2013

As I’ve mentioned on Twitter a few times, we recently launched a Buddypress-powered site for our accepted students. We invited a bunch of students and many of them logged in and joined. Accounts were created for each student, and an email was sent to them with their login information.

After a week or so, our awesome Admission team came to me and asked who hadn’t logged in yet. Hmm. Good question. We didn’t have a plugin or anything turned on to track this, so I did some creative coding. Please – if there’s a more efficient way of doing this, let me know.

The first step I did was to query the wp_users table to get the user ID’s of all the users. Something like this:


$query = "SELECT ID,user_email,display_name,user_registered from wp_XXXXXX_users ORDER BY ID";

$result = mysql_query($query);

Now here’s where it gets tricky. The wp_users table in WordPress by default doesn’t tell you the last time someone logged in, just when the account was created. If we want to know when they last did anything, we need to go looking in the wp_usermeta table. In that table is a meta_key field. There are many for each user ID, so we are looking for the one called last_activity. In that field will be the date and time the user last did anything in our site. If they’ve never logged in, they’ll have no activity.

Again, this isn’t efficient, but basically we loop through the IDs we queried before and see if there’s a last_activity value in the meta_key field. If there is, great, that user has logged in. If there isn’t, we can assume they haven’t logged in.

while ($row = mysql_fetch_assoc($result)) {

 $userid = $row['ID'];

 $q = "SELECT * from wp_XXXXXXX_usermeta where user_id = $userid and meta_key = 'last_activity'";

 $r2 = mysql_query($q);

 $num_rows = mysql_num_rows($r2);

 if( mysql_num_rows($r2) > 0){
	//This person has logged in
 }else{
       // Do whatever you need to here. I made a pretty table of user info
	echo "
Not logged in: ".$row['user_email'];
 }

}

If you’re running a site with a few users, this method isn’t too terribly taxing. Our WordPress site, however, has several thousand users so this query is pretty taxing. We don’t run it very often, which is good, because I would not recommend such a inefficient query be run in a production environment.

And since I know you were going to ask, the wp_XXXXXXX_usermeta table is named that way for a purpose. It’s good WordPress security practice to rename your tables from the default titles, because hackers often look for installs that haven’t been changed, and can write their malware to look for tables named in most cases the default, such as wp_usermeta.

The best time to change it is when you’re first installing WordPress, but it is possible to do it after. I’d recommend using a plugin like Chris Weigman’s Better WP Security. It will do the heavy lifting for you. But if you’re feeling adventurous, you can do it manually, by adding this line to your wp-config.php file:

$table_prefix  = 'wp_2f2fss_';

You can make it whatever you like, just make sure you end it with an underscore. The next step is to rename your real database tables. This is slightly more tricky if you don’t have access to the database itself or something my PHPMySQL admin. You can learn more about this process here.