First time visit here and looking forward to picking up a few pointers with regard to database integration within a web environment.
I have one particular issue at the moment and being new to MySQL is causing me problems so I'll just jump straight in there if I may.
I have access to a database table which lists exams together with student results. It is essentially a join table between one containing the exam details and one containing student details. I do not have access to the individual tables
Essentially, the data I can access comprises of a joint primary key comprising 'ExamID' and StudentID together with the student's mark for each exam, not surprisingly called 'Mark'
I want to be able to list, for each exam, the student that got the top mark - or the 2nd or 3rd for that matter.
Initially, I thought I'd simply use a SELECT DISTINCT ExamID statement to stick the exam IDs into a PHP array through which I could loop running another SELECT statement similar to
SELECT StudentID FROM 'tblresults' WHERE ExamID = ExamArrayIndex ORDER BY Mark LIMIT 0, 1
The ExamArrayIndex would be the current value of ExamID taken from the array as I loop through.
This seems a rather inefficient way of doing this and I wonder if there was a way to combine the two sql statements to achieve the same ends.
Any guidance would be appreciated