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_dura
tion) 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'