joyce — Mon, 24 Dec 2001 06:20:23 +0000

thanks for reminding..

i found out..the query should be like this:

SELECT taker_name FROM quirex_record WHERE quiz_id = '$quiz_id' and month(FROM_UNIXTIME(time_finish)) = $month and year(FROM_UNIXTIME(time_finish)) = $year ORDER BY (no_correct/no_total) DESC, no_total DESC, (time_finish - time_begin) ASC, time_finish DESC LIMIT 1

i should put FROM_UNIXTIME instead of UNIX_TIMESTAMP..

my mistake...

Mark Hensler — Mon, 24 Dec 2001 06:07:07 +0000

A few ideas....

1) If the times are stored in unix timestamp, you don't need to use the mySQL function UNIX_TIMESTAMP. That converts DateTime fields into unix timestamps. So running that function on a unix timestamp may be messing it up.

2) Put quotes around $month and $year in the query string.

3) Standard debug... if the query is not working the way you expect, print it out. Make sure that that PHP is generating the query the way you expect. If it is, try copy & pasting it into phpMyAdmin and see what it does there.

joyce — Mon, 24 Dec 2001 02:39:12 +0000

well, this query will get me the highest score for all the month..

SELECT taker_name FROM quirex_record WHERE quiz_id = '$quiz_id' ORDER BY (no_correct/no_total) DESC, no_total DESC, (time_finish - time_begin) ASC, time_finish DESC LIMIT 1

dates are stored in unix_timestamp..i want one query to return the highest for each month..for eg like if this month is december...it will only select the highest score for december only..

i've changed my query into this: it executed correctly but there was no results selected out. wat is wrong with my query??

<?php
$month = date(n);
$year = date(Y);

SELECT taker_name FROM quirex_record WHERE quiz_id = '$quiz_id' and month(UNIX_TIMESTAMP(time_finish)) = $month and year(UNIX_TIMESTAMP(time_finish)) = $year ORDER BY (no_correct/no_total) DESC, no_total DESC, (time_finish - time_begin) ASC, time_finish DESC LIMIT 1
?>

Mark Hensler — Thu, 20 Dec 2001 07:36:18 +0000

I have a few questions first...
1) How to you score an exam?
2) How do you have the dates stored? (it looks like 2 timestamps)
3) Do you want one query to return the highest for each month, or one query to return the highest for one month?