MySQL query in nested loop to get scores and averages for students 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 &gt;= 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 &gt;= 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"
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.