Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 10 down vote accepted

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.

share|improve this answer
    
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 '12 at 22:01
5  
The COUNT(DISTINCT DATE(... means you'll get 1 added to the count for each distinct day they log in. –  Simon Righarts Jan 14 '12 at 22:19
    
Proves how little I now about SQL. Thanks for clearing it up! –  jSherz Jan 14 '12 at 22:48
1  
There are actually 8 dates between now()-interval 7 day and now() –  ypercube Jan 22 '12 at 21:55
    
It might be better with BETWEEN currentdate()-interval 6 day AND now() –  ypercube Jan 22 '12 at 21:57
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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