Grant User One Point Each Day

Question

Using PHP/mySQL, a user is granted a single integer point to their member account each day. The data I will use to determine if a point should be granted are these mysql fields: Creation Date (timestamp) and Last Login (UNIX TIME).

The procedure for granting these points is determined when the user logs in. My question is, what's the most efficient way of determining how many days have passed since the last login? Secondly, if a user logs in each day, how do I determine if 24 hours has passed and a point is to be granted? Days past equates to the points given (1 per day).

Currently I am using this code:

/*
** Updates Points based on days since last visit
*/
static function UpdatePoints($username)
{
    $getlog = System::$mySQL->Select("lastLog, creation FROM users WHERE username='$username'");
    $log = System::$mySQL->Fetch($getlog);

    $offset = (TIME() - $log['lastLog']) / 86400;  // 24hrs
    $lastlog = round($offset); // in days

    if($lastlog > 0)
    {
        System::$mySQL->Update("users SET points=points+".$lastlog." WHERE username='$username'");
    }
}

Markup aside, it's obvious my code is shortsighted. If the user logs in once everyday, they do not gain a point. Therefore I must determine the correct method for doing so using the Creation Date field as well. I just can't wrap my head around it today, any suggestions? Thanks.

Answer 1

This is better suited for the database than for PHP. Add a table users_points, with unique index (user_id,login_date). Sample data:

 user_id | login_date
====================== 
 19746   | 2010-09-02
 19746   | 2010-09-03

Then on every login, mark that the user has logged in on that date (if the row already exists, the index will prevent duplication):

INSERT IGNORE INTO `users_points` (`user_id`,`login_date`) VALUES (19746,CURDATE())

And to get the number of points:

SELECT COUNT(*) FROM `users_points` WHERE `user_id` = 19746

This is also good that you have a list of days when the user has logged in, in case you change the criteria and want to do a recount.

user_id is an INT, login_date is a DATE, there's a usable index, so both insert and select should be quick, and the table will be relatively small even with a huge number of users.

In case you insist on having the user score stored in some place (or maybe you want to retrieve it together with other user data), you could do this on login:

• run the insert ignore
• run the select count
• save the result in a column of table users.

Answer 2

Use a separate field to keep the date when you added the point to user's account. If this happened not today - add a point (or several) and update a field.