Solved

Restrict mysql queries to a time range

Posted on 2009-05-18
7
464 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
syntax error, unexpected '?' in phpunit 5 28
PHP Curl Problem 10 41
MySQL stored procedure returning null values 4 27
How to hide contact form only if no validation errors 4 29
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

733 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