PHP / MySQL - Fidelity implementation

Question

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

I want to receive rewards to users who have visited my site for 30 consecutive days.

[MySQL] What will be the best table architecture?

[PHP] What algorithm should be used to optimize this task?

Answer 1

I prefer more raw data in the database than the approach that @Matt H protects. Create a table that writes all logins to the site (or, if you want, new session sessions) along with its 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)

UserId . , , , , .

, :

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

30, .

, MySQL ( SQL Server PostgreSQL), , , . , .

Answer 2

.

• | ID | PK,
• | EMAIL | . , , .
• | FIRST_CONSECUTIVE_DAY |
• | LAST_CONSECUTIVE_DAY |
• | HAS_BEEN_REWARDED | bool, default false (0)

.:)

: , ...

1) LAST_CONSECUTIVE_DAY. LAST_CONSECUTIVE_DAY , . LAST_CONSECUTIVE_DAY, LAST_CONSECUTIVE_DAY . FIRST_CONSECUTIVE_DAY LAST_CONSECUTIVE_DAY .

2) TIMESTAMPDIFF, LAST_CONSECUTIVE_DAY FIRST_CONSECUTIVE_DAY DAY. 30, 3, .

3) - 30 ! ! HAS_BEEN_REWARDED, , , HAS_BEEN_REWARDED .