MantisBT - ATutor
View Issue Details
0003568ATutorToolspublic2008-09-15 22:572009-07-21 08:51
IndieRect 
harris 
normalminoralways
closedfixed 
1.6.1 
1.6.2 
SVN
0003568: Course stats show either 0 or 1
Course stats tools/course_stats.php show either 0 or 1 in the table columns (no-one entered a course/one or more entered).

It's because of SQL queries in bounce.php, lines 54 and 56. NOW() appears to be never equal to login_date as the former is of DATETIME type while the latter is of DATE type.
The fact is when I type "SELECT * FROM `AT_course_stats` WHERE login_date=NOW()" in phpMyAdmin it returns zero rows while "SELECT * FROM `AT_course_stats` WHERE login_date=CURDATE()" returns plenty.

I suggest replacing NOW() with CURDATE().

It may have something to do with MySQL version: before we upgraded to 5.0.51 we didn't have such a problem.
No tags attached.
Issue History
2008-09-15 22:57IndieRectNew Issue
2008-09-15 22:57IndieRectAffects version => SVN
2008-11-13 09:50gregStatusnew => assigned
2008-11-13 09:50gregAssigned To => harris
2008-11-18 07:44harrisNote Added: 0003391
2008-11-18 07:58harrisNote Added: 0003392
2008-11-18 07:58harrisNote Added: 0003393
2008-11-18 07:59harrisNote Edited: 0003393
2008-11-18 10:16harrisNote Edited: 0003393
2008-11-18 10:19harrisNote Edited: 0003393
2008-11-18 10:26harrisStatusassigned => resolved
2008-11-18 10:26harrisFixed in Version => 1.6.2
2008-11-18 10:26harrisResolutionopen => fixed
2008-11-18 10:26harrisNote Added: 0003394
2009-07-21 08:51gregStatusresolved => closed

Notes
(0003391)
harris   
2008-11-18 07:44   
Might be related to
http://bugs.mysql.com/bug.php?id=28929 [^]
(0003392)
harris   
2008-11-18 07:58   
...Prior to MySQL 5.0.42, when DATE values are compared with DATETIME values, the time portion of the DATETIME value is ignored, or the comparison could be performed as a string compare. Starting from MySQL 5.0.42, a DATE value is coerced to the DATETIME type by adding the time portion as '00:00:00'. To mimic the old behavior, use the CAST() function to cause the comparison operands to be treated as previously. For example:

date_col = CAST(NOW() AS DATE);

ref: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html [^]
(0003393)
harris   
2008-11-18 07:58   
(edited on: 2008-11-18 10:19)
All the comparisons with NOW() in mysql 5.0.42+ in ATutor will probably break.


Edited:
More precisely, only the tables that has "DATE" will be affected

(0003394)
harris   
2008-11-18 10:26   
Solution:
Used CURDATE() as suggested, compatible with mysql 4.x, 5.x

ref: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_curdate [^]

Affected:
bounce.php

Version #:
8238