Solved

Restrict mysql queries to a time range

Posted on 2009-05-18
7
460 Views
Last Modified: 2013-12-12
I have a database that tracks the training modules that a user has read, and the amount of time they have spent viewing the module. Users are not allowed to take a quiz on the subject until they have viewed the training module for the time necessary to read it.

Therefore if a module takes five minutes to read. The user may read for two minutes, go to another section, return and read for another two minutes, leave, and return for the final minute. Each time they leave the module I record the time they spent viewing it.

 I calculate the total time spent  by doing a sum(tutorial_tracking_duration) and the code works,. But I want to restrict the process so that they must have viewed the module for the alloted time (in this case five minutes) within a twenty four hour period. Not the last twenty-four hours, but they must have viewed the training for the allotted time within any twenty-four hour period.

I can't think of a way to do that without a series of temporary tables. Is there an elegant way to do this?

My current code, which does not have the 24 hour restriction, shows below. Note the SUBSTRING_INDEX is because some modules have multiple pages, designated as name_1, name_2 etc. and the SUBSTRING_INDEX strips the _#. $module_series would contain "name"
select SUBSTRING_INDEX(tutorial_tracking_module,'_',-1), SUM(tutorial_tracking_duration) from tutorial_tracking where tutorial_tracking_emp ='$emp' and SUBSTRING_INDEX(tutorial_tracking_module,'_',1)='$module_series'

Open in new window

0
Comment
Question by:birwin
  • 3
  • 2
  • 2
7 Comments
 
LVL 4

Expert Comment

by:Adam Chan
ID: 24410681
Hi birwin,

You can save a timestamp of when the user starts reading the module(start_time), then count the duration as you normally do. But do a check so if the current time > start_time +24hr, then you reset the duration to zero.
0
 
LVL 4

Expert Comment

by:max-hb
ID: 24411704
Hi!
Calculating if the user has read the required time within ANY 24h period is not posible in mysql AFAIK.
If you refione your requirements so that the given time of reading has to be taken within one day, then you may use "group by" to get your results.

This requires that your "tutorial_tracking_duration" has some kind of timestamp on it, eg. "tutorial_tracking_duration_timestamp" as in my example below:




select

SUBSTRING_INDEX(tutorial_tracking_module,'_',-1),

SUM(tutorial_tracking_duration) from tutorial_tracking

where

tutorial_tracking_emp ='$emp'

and

SUBSTRING_INDEX(tutorial_tracking_module,'_',1)='$module_series'

group by 

year(tutorial_tracking_duration_timestamp),

month(tutorial_tracking_duration_timestamp),

day(tutorial_tracking_duration_timestamp)

Open in new window

0
 
LVL 6

Author Comment

by:birwin
ID: 24413702
The database holds the following:
tutorial_tracking_id  // auto increment
tutorial_tracking_emp // employee number
tutorial_tracking_duration
tutorial_tracking_session
tutorial_tracking_module  
tutorial_training_timestamp  
addamez: That is an interesting idea, but how would I determine if they had previously fully read the training, and then dropped back to the training module for a few seconds a few days later to verify some fact.
 max-hb: Also an interesting solution, but I can expect that someone might start taking the lesson at 11:45 PM and not finish until 12:15 AM, (some of the training modules take longer than 5 minutes) so I think whatever solution I use has to stick to a clock rather than calendar solution.
In reviewing the database, I see I am also tracking the session. This may be a separate question, but how would I restrict the answer to within a session rather than within 24 hours. I may be able to insist that the tutorial be taken within a session.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Accepted Solution

by:
max-hb earned 500 total points
ID: 24414064
> In reviewing the database, I see I am also tracking the session. This may be a separate
> question, but how would I restrict the answer to within a session rather than within 24 hours.
> I may be able to insist that the tutorial be taken within a session.
You could probably group your results by session id.

regards
 maxhb
0
 
LVL 4

Expert Comment

by:Adam Chan
ID: 24414498
> how would I determine if they had previously fully read the training, and then dropped back to the training module for a few seconds a few days later to verify some fact.

A simple true/false flag (i.e. tutorial_tracking_complete) could be set once tutorial_tracking_duration >= the minimum time required has been reached (5min) within the allowed time period (24hrs).

As such, your tracking logic should look something like the pseudo code in the snippet below

As for the session problem, you could store the session along with the true/false flag above to indicate the session in which the user completed the tutorial; or you could set the tutorial_tracking_complete to false when a user leaves a session.
if (current_timestamp - start_timestamp > allowed_time)

  tutorial_tracking_duration = 0;

else if (tutorial_tracking_duration >= alloted_time)

  tutorial_tracking_complete = true;

else

  // nothing happened, keep tracking
 
 

// only allow user to take quiz if the relevant tutorial_tracking_complete is true

Open in new window

0
 
LVL 6

Author Comment

by:birwin
ID: 24415685
I finally decided to limit the tutorial reading to a single session, and use this code to provide the largest total for any session:
select SUM(tutorial_tracking_duration) as dur  from tutorial_tracking where tutorial_tracking_emp ='$emp' and SUBSTRING_INDEX(tutorial_tracking_module,'_',1)='$module_series' GROUP BY tutorial_tracking_session ORDER BY  dur DESC
The order of the GROUP BY and ORDER BY is important.
0
 
LVL 6

Author Closing Comment

by:birwin
ID: 31582503
I wanted an all mysql solution. Your suggestion of using GROUP BY for the sessions lead me to ther right answer.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now