sql query??

joyce posted this at 05:33 — 20th December 2001.

Joined: Nov 2001

i would like to select the highest score of the exam monthly. for eg, for january, i will select the highest score for exams that were taken in month january. for february, i will select the highest score for exams in february.

this is my table structure:

(table A)

quiz_id  int(10)  
taker_name  varchar(255)   
taker_email  varchar(255)   
show_record  tinyint(1)  
no_total  int(11)   
no_correct  int(11)   
time_begin  int(12)  
time_finish  int(12)  

how will my sql query be??
currently my query is like this:

SELECT taker_name FROM table A 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

Mark Hensler posted this at 07:36 — 20th December 2001.

He has: 4,048 posts

Joined: Aug 2000

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?

Mark Hensler
If there is no answer on Google, then there is no question.

joyce posted this at 02:39 — 24th December 2001.

They have: 164 posts

Joined: Nov 2001

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 posted this at 06:07 — 24th December 2001.

He has: 4,048 posts

Joined: Aug 2000

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.

Mark Hensler
If there is no answer on Google, then there is no question.

joyce posted this at 06:20 — 24th December 2001.

They have: 164 posts

Joined: Nov 2001

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...