PHP/MySQL - Fidelity program implementation

I would like to implement a fidelity program, similar to the one on stackoverflow, on my website.

I want to be able to give some kind of reward to users who have visited my website for 30 da开发者_如何学编程ys in a row.

[MySQL] What would be the best table architecture?

[PHP] What kind of algorithm should I use to optimize this task?

---

I prefer more raw data in the database than the approach that @Matt H. advocates. Make a table that records all logins to the site (or, if you prefer, new session initiations) along with their time and date:

CREATE TABLE LoginLog (
    UserId INT NOT NULL REFERENCES Users (UserId),
    LoginTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)

CREATE INDEX IX_LoginLog USING BTREE ON LoginLog (UserId ASC, LoginTime DESC)

Just insert the UserId into the table on login. I, of course, made some assumptions about your database, but I think you will be able to adapt.

Then, to check for discrete logins for each of the preceding thirty days:

SELECT COUNT(*)
FROM (SELECT   DATE(log.LoginTime) AS LoginDate,
               COUNT(*) AS LoginCount
      FROM     LoginLog log
      WHERE    log.LoginTime >= DATE(DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 DAYS))
      GROUP BY LoginDate
      HAVING   COUNT(*) > 0) a

If the result is 30, you're golden.

I will admit that I haven't touched MySQL in a while (working mainly on SQL Server and on PostgreSQL lately), so if the syntax is off a bit, I apologize. I hope the concept makes sense, though.

---

From your description above, this could be accomplished fairly simply and with one table.

• | ID | Table PK, auto-incrementing
• | EMAIL | website visitor unique ID. Ostensibly an email, but could be any piece of data that uniquely ID's the visitor
• | FIRST_CONSECUTIVE_DAY | timestamp
• | LAST_CONSECUTIVE_DAY | timestamp
• | HAS_BEEN_REWARDED | bool, default false(0)

Thats it for the table. :)

The algorithm is in three parts: When a user logs in, once they have been verified...

1) Check the users LAST_CONSECUTIVE_DAY. If the LAST_CONSECUTIVE_DAY is today, do nothing. If the LAST_CONSECUTIVE_DAY is yesterday, set LAST_CONSECUTIVE_DAY to todays date. Otherwise, set FIRST_CONSECUTIVE_DAY and LAST_CONSECUTIVE_DAY to todays date.

2) Use TIMESTAMPDIFF to compare LAST_CONSECUTIVE_DAY and FIRST_CONSECUTIVE_DAY by the DAY unit. If it returns 30 go on to step 3, otherwise move on with the application.

3) Your user has visited the website every single day for 30 days in a row! Congratulations! Check HAS_BEEN_REWARDED to see if they have done it before, if still false give them a prize and mark HAS_BEEN_REWARDED as true.