tags:

views:

82

answers:

1

I'm trying to get the rank of a certain student out of a class of a hundred, I want to be able to display the students rank.

I'm trying to get the rank of a student by grades and grade points. For example if student 1 has 2 A+ grades with a total of 10 points and student 2 has 3 B- grades with a total of 10 points student 1 will rank higher. I was wondering how would I be able to do this using PHP & MySQL?

Here is the PHP & MySQL code I have so far.

$gp = array();
$dbc = mysqli_query($mysqli,"SELECT grades.*, homework_grades.*, users_homework.*
                             FROM grades 
                             LEFT JOIN homework_grades ON grades.id = homework_grades.grade_id
                             LEFT JOIN users_homework ON homework_grades.users_homework_id = users_homework.id
                             WHERE users_homework.user_id = '$user_id'
                             AND users_homework.grade_average = 'A+'");

if (!$dbc) {
    print mysqli_error($mysqli);
} else {
    while($row = mysqli_fetch_array($dbc)){ 
    $gp[] = $row['grade_points'];
}
}

echo array_sum($gp);

Here is my MySQL tables.

CREATE TABLE homework_grades ( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
grade_id INT UNSIGNED NOT NULL, 
users_homework_id INT UNSIGNED NOT NULL, 
user_id INT UNSIGNED NOT NULL, 
PRIMARY KEY (id) 
);



CREATE TABLE grades ( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
letter_grade VARCHAR NOT NULL, 
grade_points FLOAT UNSIGNED NOT NULL DEFAULT 0, 
PRIMARY KEY (id) 
);



CREATE TABLE users_homework (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
homework_content LONGTEXT NOT NULL,
grade_average VARCHAR DEFAULT NULL,
PRIMARY KEY (id)
);
+2  A: 

UPDATED

You need an aggregate. Do a SUM() and COUNT(). COUNT will tell you number of assignments, sum will tell you the total grade points. GROUP BY user_id to get this for each student.

$gp = array();
$dbc = mysqli_query($mysqli,"SELECT SUM(grade_points) as grade_points, user_id, count(*) as num_assignments
                             FROM grades 
                             LEFT JOIN homework_grades ON grades.id = homework_grades.grade_id
                             LEFT JOIN users_homework ON homework_grades.users_homework_id = users_homework.id
                             GROUP BY user_id
                             ORDER BY grade_points DESC , num_assignments ASC'");

if (!$dbc) {
    print mysqli_error($mysqli);
} else {
    $i=1;
    while($row = mysqli_fetch_array($dbc)){ 
        $user[$row['user_id']] = $i++;
        $rank[] = $row['user_id'];
        $gp[] = $row['grade_points'];
    }
}

// the rank of user 10 is
echo "the rank of user_id 10 is {$user[10]}";
echo "the rank of all users are: " ;
print_r($rank);

This will add up the grade points, and count the number of assignments. Sorting will make sure that a student with 10 points from 3 assignments, will have a higher rank than a student with 10 points from 4 assignments.

See the COUNT SUM and GROUP BY aggregates.

Byron Whitlock
I'm using grade_points which equals a certain grade for example A+ = 5B+ = 4 and so on.
codeMONKEY
But how would I know where the student ranks?
codeMONKEY
The query would return all the students sorted by rank. So query all students, then use php to pull out which one you want. Their rank is the key to the `$gp[]` array. Since you only have 100 students, don't worry about efficiency here. it will be in the sub microsecond range.
Byron Whitlock
You lost me I think I got off the wrong road what are you exactly talking about. A sample code would help me understand a little better thanks.
codeMONKEY
I'll try to figure it out from here if I get stuck I will post another question. Thanks for the help.
codeMONKEY
I updated my answer as I think i didn't understand exactly what you were asking the first time. Does it make sense now?
Byron Whitlock
It makes a little more sense to me know. I'll try to make it work with my code.
codeMONKEY