MySQL query in nested loop to get scores and averages for students

Question

I am trying to get the scores and average for all assignments for records in a MYSQL database. The code below works but seems to take quite a bit of time to run because of the nested while loop. Any suggestions on how to combine the queries into one?

$query4 ="SELECT studentsisid,coursename,learningoutcomeid,learningoutcomename,
count(RecordID) as assessmentcount, 
count(if(outcomescore >= 3,outcomescore, NULL)) as over3,
avg(outcomescore) as scoreavg
from studentscores 
WHERE studentsisid LIKE '$studentid' AND coursename='$coursename'
Group by studentsisid,coursename, learningoutcomeid  
ORDER BY studentname,   learningoutcomename";

$result4=mysql_query($query4);

if (mysql_numrows($result4) == 0){
die("No result.");
}

while ($row4 = mysql_fetch_assoc($result4)) {
  $learningoutcomeid = (int)$row4['learningoutcomeid'];
  $coursename = $row4['coursename'];
  $studentid = $row4['studentsisid'];
  $learningoutcomename = $row4['learningoutcomename'];
  $learningoutcomeid = (int)$row4['learningoutcomeid'];
  $assessmentcount = (int)$row4['assessmentcount'];
  $scoreavg = number_format($row4['scoreavg'], 1);
  $over3 = $row4['over3'];

$query5 ="SELECT count(if(outcomescore >= 3,outcomescore, NULL)) as recentover3, 
    avg(outcomescore) as recentscoreavg 
    FROM 
    (
    SELECT outcomescore FROM studentscores 
    WHERE 
    studentsisid='$studentid' AND coursename='$coursename' 
    AND learningoutcomeid='$learningoutcomeid'  
    ORDER BY RecordID DESC LIMIT 3) as r";

$result5=mysql_query($query5);

while ($row5 = mysql_fetch_assoc($result5)) {
$recentscoreavg = number_format($row5['recentscoreavg'], 1);
$recentover3 = $row5['recentover3'];

I am trying to get the data below and the code above does accomplish the goal:

studentsisid;coursename;loid;loname;assessmentcount‌;over3;scoreavg;recentover3;recentscoretavg
1234";"Course1";"7982";"LearningOutcome1";"1";"1";"3.2";"1";"3.2" 
1234;"Course1";"7995";"LearningOutcome2";"4";"4";"2.5";"2";"2.5" 
1234;"Course1";"7991";"LearningOutcome3";"6";"6";"3.8";"3";"3.2" 
1234;"Course1";"7889";"LearningOutcome4";"1";"1";"3.4";"1";"3.2"
1234;"Course1";"7839";"LearningOutcome5";"2";"2";"2,6";"1";"3.2"

Answer 1

You can replace:

  $learningoutcomeid = (int)$row4['learningoutcomeid'];
  $coursename = $row4['coursename'];
  $studentid = $row4['studentsisid'];
  $learningoutcomename = $row4['learningoutcomename'];
  $learningoutcomeid = (int)$row4['learningoutcomeid'];
  $assessmentcount = (int)$row4['assessmentcount'];
  $scoreavg = number_format($row4['scoreavg'], 1);
  $over3 = $row4['over3'];

by:

extract($row4);
$scoreavg  = number_format($scoreavg,1);

Note that there are several weird things in your code: 2x 'learningoutcomeid' and 'studentsisid'. Sloppy coding?

Your code here is also incomplete and not working. Sorry, that means no answer to your question.

What is also a serious problem is that the code is undocumented. There are NO comments to explain what's being done. Suppose you hand over this project to someone else, how is this next person to make sense of it? I looked at it for a while, and I cannot make sense of it. There seems to be just one table you use: 'studentscores', but it's structure eludes me.

Answer 2

I can't help you with rewriting your code completely, but a couple of points on readability and security:

• use proper indentation. It is really hard to see where which loops ends without it.
• do not use mysql, it is deprecated (use mysqli or PDO instead).
• do use prepared statements to defend against SQL injection.
• be consistent with your casing. All your SQL keywords should be all upper case, not just some.
• you should also be consistent with your spacing.
• don't write all lower case. learningoutcomename is really hard to read for example.
• query4 and row4 are not very good names, as they don't tell me anything (except that you have quite a lot of queries, probably too many for one function).
• why do you use like instead of equals for the student id? This seems a bit odd.