I think doing a sort by date with limit 10 should get you the first
ten. I believe the now() function uses the server time, so no need to
do date/time calcs really.
Good luck,
Jason
Brian Menke wrote:
> I'm hoping for some general advice on an approach for the following
> scenario:
>
>
>
> I have a customer who wants to put an incentive program in place for
> students taking learning modules and then completing tests. The concept is
> simple. Award the first 10 people who complete a test with a score of
> 100%... that type of thing. Students are allowed to take test more than
> once. Track each time the student takes the test and show the latest score
> ect. You get the idea. I have the database tables and relationships already
> all set up for the tests, but it's the tracking of the dates and times that
> I don't have and it got me thinking.
>
>
>
> I need to track down to the day/hour/minute level. Okay, that should be easy
> (I think). I'm going to need to do a lot of date/time calculations. Would it
> be best just to have a default of CURRENT_TIMESTAMP set for a TIMESTAMP
> field? Or, is their something else I should be using? I have limited
> experience having to munge and crunch date/time info and I want to make sure
> I have the flexibility to do what I need in the future.
>
>
>
> The next gotcha I thought up is what about different time zones. Obviously
> without this consideration, people on the East coast would have an unfair 3
> hour advantage over people on the west coast. I guess I can have a time zone
> field in my student table so I could derive the time difference. Any
> suggestions on a good time zone approach?
>
>
>
> Here are my two tables as they stand now. I'm wondering if these are set up
> in a way to allow me to do all this date time crunching I'm going to need to
> do in the future? Any suggestions are greatly appreciated :-)
>
>
>
>
>
> CREATE TABLE `students` (
>
> `store_id` varchar(6) NOT NULL,
>
> `email` varchar(64) NOT NULL,
>
> `fname` varchar(32) NOT NULL,
>
> `lname` varchar(32) NOT NULL,
>
> `role` char(2) NOT NULL default '5',
>
> `password` varchar(8) NOT NULL,
>
> `phone` varchar(24) default NULL,
>
> `reg_date` date default NULL,
>
> PRIMARY KEY (`email`),
>
> UNIQUE KEY `email` (`email`)
>
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
>
>
> CREATE TABLE `completed_modules` (
>
> `module_id` char(2) NOT NULL default '',
>
> `email` varchar(64) NOT NULL,
>
> `score` int(2) NOT NULL default '0',
>
> `time` timestamp NOT NULL default CURRENT_TIMESTAMP
>
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
>
>
> Brian Menke
>
> Visual Matter, Inc
>
> 1445 Foxworthy Ave., Suite 50-215
>
> San Jose, CA 95118
>
> 408 375 9969
>
>
>
> San Jose ~ Los Angeles
> www.visualmatter.com
>
>
>
>
>
