How to display a students rank out of hundred students using PHP & MySQL?
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'
开发者_StackOverflow社区 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)
);
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.
精彩评论