MySQL Forums
Forum List  »  PHP

Complex Query
Posted by: Steven Donovan
Date: November 20, 2008 02:44PM

I have a Team-Based Learning and grading application I built and I have to compute and display grades both to individual students and to administrators who record the grades to then be entered into the campus system. I have a discrepency between what the student would see and what the admin sees. This is for a peer evaluation grade for all 2nd year medical students.

First I get the academic year.

//Get the academic year
$tl_config = $db->sql_fetchrow($db->sql_query("SELECT * FROM `".$prefix."_tl_config`"));
$Academic_Year = $tl_config['Academic_Year'];

I then get what term it is. First or Second.

//Get Term
$getTerm = $db->sql_fetchrow($db->sql_query("SELECT term FROM ".$prefix."_tl_M2settings"));
if (!$getTerm) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
$Term = $getTerm['term'];

I then get the Quantitative Scores based on how their teammates rated them.

//Need to get the Quantitative Scores and compute for 60% of grade
$get_Q1scores = $db->sql_query("SELECT gs.Group_ID, pe.rater, s.Name_First, s.Name_Last, (AVG(clskill_1) + AVG(clskill_2) + AVG(clskill_3) + AVG(clskill_4) + AVG(sdskill_1) + AVG(sdskill_2) + AVG(sdskill_3) + AVG(sdskill_4) + AVG(ipskill_1) + AVG(ipskill_2) + AVG(ipskill_3) + AVG(ipskill_4)) AS QuantSum FROM ".$prefix."_tl_M2peereval pe JOIN ".$prefix."_tl_group_students gs ON (pe.rater = gs.LDAP_USER) JOIN ".$prefix."_tl_students s ON (pe.UID = s.UID)
WHERE s.Class_Year = '2'
AND pe.Academic_Year = '$Academic_Year'
AND pe.term = '$Term'
ORDER BY s.Name_Last");
if (!$get_Q1scores) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
while($row = $db->sql_fetchrow($get_Q1scores)) {
$Name_First = $row['Name_First'];
$Name_Last = $row['Name_Last'];
$Team_ID = $row['Group_ID'];
$rater = $row['rater'];
$QuantSum = $row['QuantSum'];

I then need to get the Qualitative Scores on feedback each student provided their teammates.

$get_Q2scores = $db->sql_query("SELECT rater, Academic_Year, term, (AVG(qa_1_rating) + AVG(qa_2_rating)) AS QualSum FROM ".$prefix."_tl_M2peereval
WHERE rater ='$rater'
AND Academic_Year = '$Academic_Year'
AND term = '$Term'
GROUP BY rater");
while($row = $db->sql_fetchrow($get_Q2scores)) {
$QualSum = $row['QualSum'];

I used to get QualSum when I queried for Quantitative Scores in the first query but I was not getting the correct value. So I separated the two and tried to match on rater ='$rater', but still cannot come up with the correct value for $QualSum.

$get_Q2scores is wrapped inside of the $get_Q1scores while loop.

What I would do with QualSum is the following:

//8 is max average
$Q2SUMb = ($QualSum / 8);
//.4 is 40% of grade
$Qualiscore = ($Q2SUMb * .4);

It is 40% of grade. For example:

QualSum = 7

7/8 = .875
.875 * .4 = .35

This is what the sample student sees they got for a Qualitative Scores .35 out of a possible .40 or 35% out of 40%.

If I am seeing the admin side where I record the grade I see a different score.

My sample student received a 0.34

I think my error is for Quantitative Scores I match by pe.UID = s.UID to get all students.

For Qualitative Scores I match for rater ='$rater', however one UID has several raters who filled out evaluations.

This is not making a lick of sense is it?

Options: ReplyQuote

Written By
Complex Query
November 20, 2008 02:44PM
November 20, 2008 07:00PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.