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


$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.

1 Answer 1



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.

  • I did think of that, but I need to know that the user was logged in on all days of the week - not just at least 7 times.
    – jSherz
    Jan 14, 2012 at 22:01
  • 5
    The COUNT(DISTINCT DATE(... means you'll get 1 added to the count for each distinct day they log in. Jan 14, 2012 at 22:19
  • Proves how little I now about SQL. Thanks for clearing it up!
    – jSherz
    Jan 14, 2012 at 22:48
  • 1
    There are actually 8 dates between now()-interval 7 day and now() Jan 22, 2012 at 21:55
  • 1
    It might be better with BETWEEN currentdate()-interval 6 day AND now() Jan 22, 2012 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.