determine actions over period of time PHP MySQL

Question

I don't have any code or database to display, because I am in the planning stage, and I can't figure out the correct way to do this.

I want to determine if a user has performed a specific action each day over the course of a week. If they have I want to perform an action. Each time there is a break in days, I need to reset.

So for example:

Day 1 | task performed 25 times
Day 2 | task performed 13 times
Day 3 | task performed 18 times
Day 4 | task not performed... start over at Day 1.
.....
Day 1 | task performed 3 times
Day 2 | task performed 11 times
Day 3 | task performed 14 times
Day 4 | task performed 7 times
Day 5 | task performed 3 times 
Day 6 | task performed 10 times
Day 7 | task performed 23 times

echo 'You have successfully completed 71 tasks consecutively over a period of 7 days';

I guess the real question becomes, what would be the best way to achieve this? Should I try to store 7 cookies based on date, and destroy all existing cookies if the newest cookie value is more than 24 hours old? Should I try setting and updating the date via the database?

Suggestions as potential solutions to achieve this is what I am looking for, keeping in mind this is a very database intensive app I am working on, so the less database calls IMO the better.

UPDATES

So I am trying to plan this out using a database and I am just running into issues at every angle.

If I try to set an incremental column there is no way to tell when the last update was.

If I set a table, its EXTREMELY complicated as I have to base the value off consecutive dates. So I can't do a simple 7 day search, I have to test that each result is within 24 hours of the previous result set in the database, else how would I be able to distinguished someone who completed task on day 1, 3, and 7 vs the one who did it 1, 2, 3, 4, 5, 6, and 7.

Cleaning up the database is equally troublesome. How would I do a cleanup. I can't simply test that the data is within 7 days of the current date because that doesn't account for someone completing tasks on day 5 but not day six... and then on day 7... and equally its complicated because each user will have a different start date. So I may have started tasks today and another user 5 days ago. I can safely assume all dates over 7 days are expired, but that doesn't really help determine breaks in days.

I am so confused how to actually accomplish this task.

I can propose a schema in a bit after I do a few things and you make any other changes. But define the tasks a user is doing in that window of time and if they are fixed (that list) upon the user's start datetime of it all. It really is a simplistic model — Drew, Oct 17 '16 at 00:32
The task is singular. I am working on creating a badge system for a traffic exchange. I am trying to assign a badge to a user if they have surfed on the exchange for 7 consecutive days. So the task is "the user loaded page surf.php". Nothing more or less. — Bruce, Oct 17 '16 at 00:36
Ok so you don't have a separate task table with id's and stuff feeding this — Drew, Oct 17 '16 at 00:38
No actually I don't. Each time a user loads surf.php a "Credit counter" is incremented (after a captcha completion). So there really is no table feeding this at all, though I could easily make it so. — Bruce, Oct 17 '16 at 00:39
And you want to award Billy (id 1234) the badge if you find that he hits that page any 7 days in a row contiguously — Drew, Oct 17 '16 at 00:41

Web Dev · Answer 1 · 2016-10-17T00:12:39.427

1

Keep your data in database, because the problems with cookies is user can clean his/her browser anytime. Data in database can be processed whenever you want it to be.

lets assume you keep data in this format in database:

Description | task_id | created 
Task perfor. | 2      | 2016-10-17
Task perfor. | 2      | 2016-10-17
Task perfor. | 2      | 2016-10-18
Task perfor. | 2      | 2016-10-18

You can delete data simply by running query:

delete from table where created = date

One query can cleanup your data, that is not messy at all. Even the huge databases are handled like this.

edited Oct 17 '16 at 00:12

answered Oct 16 '16 at 23:46

Web Dev

306
3
5

The problem is I can't figure out how. lol I keep trying to set up a database plan, and each time there are problems. I can't simply set a date because there is no way to determine a date update from the last date update. So I would have to store 7 dates. Then I would have to test the last date to the current date. If its been more than 24 hours I have to reset all 7 of the days..... and this becomes very messy and database intensive. – Bruce Oct 16 '16 at 23:56
It will not be messy, just create a column name `created` in your table and make your query decisions based on that column. Each row will have `DataTime` stored on each insert and you can do queries based on that. – Web Dev Oct 16 '16 at 23:59
Ahhh I see what you are thinking. So effectively create a table entirely designed to store the action completed as opposed to an incremental column that stores the number of actions. Then store the time/date for each action and perform the action based on that. I could do it that way, but it would still require a huge cleanup each time there is a break in days to delete all prior records as there is no need outside of this particular action to store that information. – Bruce Oct 17 '16 at 00:05
unfortunately, its not that easy to cleanup as this is a multi user app, so each user is going to have their own times to evaluate the cleanup off of. I cannot do a systematic sweep like that. I have to determine each date for each user that is older that the current date + 24 hours if and only if its not in a consecutive string of other dates... meaning a break in dates. – Bruce Oct 17 '16 at 00:15
I am going to update the question to make a few of my problems more clear. – Bruce Oct 17 '16 at 00:19

score 1 · Accepted Answer · answered Oct 17 '16 at 02:25

there are several ways to do this.

if you're going with the db route, you can have a reference from this SQL fiddle

I don't know how db intensive it would be, but i think that would summarize the work needed if you are going this route. the advantage of this route is you dont have to delete or reset anything so you will have historical data, for example you can get how many "tries" the user do by changing the above query to having count(distinct d) < 7 (it would return as many rows as many the user tried to complete the task)

the other route is you can use file based data such as using JSON file on the server side for each user that doing the tasks. with this route, you need to maintain the data yourself for example you have this simple JSON structure:

{
   "day1": "2016-09-01",
   "last_task": "2016-09-03",
   "accumulated_task": "56"
}

what I mean by maintain the data yourself is, you need to update it every time changes are applied, such as if current date is 2016-09-05 and last_task is 2016-09-03, then you need to reset it. the advantage of this route is of course no db overhead, but many "manual labor".

Drew · Answer 3 · 2016-10-17T03:37:28.603

This will assume for our test that the page id = 9 ... that is viewed, counting for a badge (7 consecutive days for a user seeing that page). That fact was pulled out of op comments (7 days and a badge). As for page # 9 we just made that up here in this answer.

So if a user views the page 7 consecutive days, we want the user in the output. Note, page number is 9.

Schema and Data Load

create schema db40076704;
use db40076704;

create table pageViews
(   id int auto_increment primary key,
    userId int not null,
    viewDT datetime not null,
    pageId int not null
    -- include decent index choices here
    -- include Foreign Key constraints here
);
truncate pageViews;
insert pageViews (userId,viewDT,pageId) values
(101,'2016-09-05 21:00:00',9),
(101,'2016-09-06 11:00:00',9),
(101,'2016-09-06 15:55:00',9),
(101,'2016-09-06 15:57:00',9),
(101,'2016-09-07 21:00:00',9),
(101,'2016-09-08 21:00:00',999999),
(101,'2016-09-09 21:00:00',9),
(101,'2016-09-10 21:00:00',9),
(101,'2016-09-11 21:00:00',9),

(150,'2016-09-01 21:00:00',9),
(150,'2016-09-06 11:00:00',9),
(150,'2016-09-06 15:55:00',9),
(150,'2016-09-06 15:57:00',9),
(150,'2016-09-07 21:00:00',9),
(150,'2016-09-08 10:44:00',9),
(150,'2016-09-09 21:00:00',9),
(150,'2016-09-10 21:00:00',9),
(150,'2016-09-11 23:00:00',9),
(150,'2016-09-12 23:00:00',9),

(200,'2016-09-08 10:44:00',9),
(200,'2016-09-10 21:00:00',9),
(200,'2016-09-12 21:00:00',9),
(200,'2016-09-14 23:00:00',9),
(200,'2016-09-16 23:00:00',9),
(200,'2016-09-18 23:00:00',9),
(200,'2016-09-20 23:00:00',9);

Inner Query showing detail for debugging

select userId, 
date(viewDT), 
    (@rn := if(@curUser = userId 
            AND (@prevDate=DATE(viewDT) OR @prevDate=DATE_SUB(DATE(viewDT),INTERVAL 1 DAY)), @rn, 
                if(@curUser := GREATEST(userId,-1), @rn+1, @rn+1) 
            ) 
    ) rn, 
@prevDate:=DATE(viewDT) as dummy1 
from pageViews 
join (select @curUser:=-1,@prevDate:='',@rn:=0) params 
where pageId=9 
order by userId,viewDt;
+--------+--------------+------+------------+
| userId | date(viewDT) | rn   | dummy1     |
+--------+--------------+------+------------+
|    101 | 2016-09-05   | 1    | 2016-09-05 |
|    101 | 2016-09-06   | 1    | 2016-09-06 |
|    101 | 2016-09-06   | 1    | 2016-09-06 |
|    101 | 2016-09-06   | 1    | 2016-09-06 |
|    101 | 2016-09-07   | 1    | 2016-09-07 |
|    101 | 2016-09-09   | 2    | 2016-09-09 |
|    101 | 2016-09-10   | 2    | 2016-09-10 |
|    101 | 2016-09-11   | 2    | 2016-09-11 |
|    150 | 2016-09-01   | 3    | 2016-09-01 |
|    150 | 2016-09-06   | 4    | 2016-09-06 |
|    150 | 2016-09-06   | 4    | 2016-09-06 |
|    150 | 2016-09-06   | 4    | 2016-09-06 |
|    150 | 2016-09-07   | 4    | 2016-09-07 |
|    150 | 2016-09-08   | 4    | 2016-09-08 |
|    150 | 2016-09-09   | 4    | 2016-09-09 |
|    150 | 2016-09-10   | 4    | 2016-09-10 |
|    150 | 2016-09-11   | 4    | 2016-09-11 |
|    150 | 2016-09-12   | 4    | 2016-09-12 |
|    200 | 2016-09-08   | 5    | 2016-09-08 |
|    200 | 2016-09-10   | 6    | 2016-09-10 |
|    200 | 2016-09-12   | 7    | 2016-09-12 |
|    200 | 2016-09-14   | 8    | 2016-09-14 |
|    200 | 2016-09-16   | 9    | 2016-09-16 |
|    200 | 2016-09-18   | 10   | 2016-09-18 |
|    200 | 2016-09-20   | 11   | 2016-09-20 |
+--------+--------------+------+------------+
25 rows in set (0.00 sec)

Final Answer using the above

SELECT userId,rn,count(*) days_In_A_Row 
from 
(   SELECT userId, 
    DATE(viewDT), 
        (@rn := if(@curUser = userId 
                AND (@prevDate=DATE(viewDT) OR @prevDate=DATE_SUB(DATE(viewDT),INTERVAL 1 DAY)), @rn, 
                    if(@curUser := GREATEST(userId,-1), @rn+1, @rn+1) 
                ) 
        ) rn, 
    @prevDate:=DATE(viewDT) as dummy1 
    FROM pageViews 
    JOIN (SELECT @curUser:=-1,@prevDate:='',@rn:=0) params 
    WHERE pageId=9 
    ORDER BY userId,viewDt 
) xDerived 
GROUP BY userId,rn 
HAVING days_In_A_Row>6; 

+--------+------+---------------+
| userId | rn   | days_In_A_Row |
+--------+------+---------------+
|    150 | 4    |             9 |
+--------+------+---------------+

So user 150 viewed page 9 at least 7 days in a row (in fact 9 days in a row). That user earns a badge in your system.

A little info about the mysql variable (the @ variables). To safely use variables one must take care to not assume that any output column for a select will fire before another. That is a fact stated clearly in the manual page entitled User-Defined Variables:

In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

That said, we know that using functions such as GREATEST(), LEAST(), and COALESCE() will be forced higher in precedence.

Also the nonsense in the code of GREATEST(N,-1) will always return N. But we needed to force precedence of that column being calculated before the

@prevDate:=DATE(viewDT) as dummy1

line. Also see Baron Schwartz's Obligatory Reading Advanced MySQL user variable techniques.

This would be the correct answer if I was strictly sticking with database. However, as I was open to methods of doing this.... I can't possibly think there will be a faster method than the json method suggested. 0 database connections. Very light files on the fs. I can handle a couple million ~1k files a whole lot easier than dozens of records for millions of users in a database :) — Bruce, Oct 17 '16 at 08:27
I thought you asked for a db solution, my bad. Of course roll your own methods without a db and see how you like it — Drew, Oct 17 '16 at 15:23

determine actions over period of time PHP MySQL

3 Answers3

Schema and Data Load

Inner Query showing detail for debugging

Final Answer using the above