Vincent Vincent - 11 months ago 49
MySQL Question

PHP doing multiple sorts with same $result resource

I am not sure how this would work, but I have 6000 student marks that belong to approximately 900 students. I can do several select functions on this data to get a reasonable answer but I have to put that information into a spreadsheet to delete the students who have not met the criteria. Is there a better way?

If I do my first select to get each unique student name, then can I then sort through the results for averages over 80. This is not an issue, but I would like to drop those that did not meet this criteria and then do another select for those students that have less than 8 courses, and drop those. Then go though the final list and drop any student who does not have a 80 in English.

I can do each as an individual select statement, but I want to store each result and use that in the next step. In the end I would like to end up with a list of students who have 80 average, 8 courses, min 80 in English ...

I know this is possible (if not it would be crazy) but what would be the best and most efficient method of doing this, June marks would be 24000 marks so I am guessing using 6 select queries is not the best method? I am using PHP and MYSQL for this.

I hope that this is enough information and I really appreciate any insight into this.


PS here is the table structure:

table: students_marks
id stud_id gr student_# year course term mark per

415 31703 9 3100992316 2500 PHYE9 F1 78 78
416 31703 9 3100992316 2500 FR9 F1 50 50
417 31703 9 3100992316 2500 ENG9 F1 55 55

id student_# First Last gr
50 3100992316 Amanda B 9

I seperated it into two tables just to keep the first sort for unique students easier but after reading the answer below I see it probably would have been better to put all the data into one table. Oh and if you are wondering why mark and percent, IB has marks out of 7 so they have to be converted to something normal when doing calculations.

Nua Nua
Answer Source

If all your data is in the same table you could do all those filters in the same Select statement. Something like

SELECT AVG(Grades) As Average, StudentID,StudentName, Count(StudentCourses) As "Courses" FROM Students Where Average >= 80 AND Courses >= 8;

Otherwise you will need to do a join or two. I would have to see your table to give a better answer.