Loyalty reward - record for every day of a week

Question

I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week.

So, the real question, how do I get every record in a set week by a user (user_id) and know that there was a login on each day? Would MySQL be able to do this or would I have to do a query for every user and then further processing on top of that?

The only thing I can think of doing is:

SELECT * FROM `users` WHERE `users`.`time_they_logged_in` = IN WEEK X

<?php

$result = $pdo->prepare('SELECT....');
$result->bindParam(1, etc etc);

$logged_in_on = array(0, 0, 0, 0, 0, 0, 0);

foreach($result->fetchAll() as $login_record) {
    if(LOGGED IN DAY = MONDAY -> $logged_in[0] = true)
}

Idea: record for each week, with a column for each day and a total - then it's a matter of updating it on viewing a page / login and then looking for items with a total of 7.

Answer 1

Maybe.

SELECT count(distinct date(time_they_logged_in)) 
FROM `users` 
WHERE `users`.`time_they_logged_in` between now()-interval 7 day and now();

If the count is 7 they logged in at least once per day in the past week.