# 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 >= 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"

• There seems to be part of your code missing. What happens in the second while loop?
– tim
Mar 3 '15 at 21:29
• The second while loop is used to get the two variables $recentscoreavg and$recentover3 from $query5 Mar 3 '15 at 21:52 • Yes, I see that, but what happens then? You never close the loop, so the code you posted here would not actually compile. – tim Mar 3 '15 at 21:57 • Correct. This is part of a PHP page that then takes the variables and publishes in a table. Everything works fine now but I am trying to combine query4 and query5 so I do not have to put in an additional query and nested while statement to get the variable$recentscoreavg and $recentover3. Mar 3 '15 at 22:06 • What you may and may not do after receiving answers. I've rolled back Rev 4 → 2. Mar 3 '15 at 22:32 ## 2 Answers 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.

• I could post the entire code but it has much unrelated to this problem. I tried to edit the code to make it easier to read but it was reverted. I am going through cleaning up the code now. Thanks for the feedback. Mar 3 '15 at 23:03
• I understand. The idea here is to publish working code. We cannot work with your code in any way. We don't have the data, the structure of the database, working code, or an explanation of what it is supposed to do. You could make a working PHP fiddle (phpfiddle.org) and put it in a comment (they cannot revert that). Mar 3 '15 at 23:11

• 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.