0
\$\begingroup\$

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"
\$\endgroup\$
6
  • \$\begingroup\$ There seems to be part of your code missing. What happens in the second while loop? \$\endgroup\$
    – tim
    Mar 3 '15 at 21:29
  • \$\begingroup\$ The second while loop is used to get the two variables $recentscoreavg and $recentover3 from $query5 \$\endgroup\$
    – Kevin
    Mar 3 '15 at 21:52
  • \$\begingroup\$ Yes, I see that, but what happens then? You never close the loop, so the code you posted here would not actually compile. \$\endgroup\$
    – tim
    Mar 3 '15 at 21:57
  • \$\begingroup\$ 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. \$\endgroup\$
    – Kevin
    Mar 3 '15 at 22:06
  • \$\begingroup\$ What you may and may not do after receiving answers. I've rolled back Rev 4 → 2. \$\endgroup\$ Mar 3 '15 at 22:32
2
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$
    – Kevin
    Mar 3 '15 at 23:03
  • 1
    \$\begingroup\$ 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). \$\endgroup\$ Mar 3 '15 at 23:11
1
\$\begingroup\$

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.
\$\endgroup\$
1
  • \$\begingroup\$ Thanks Tim. I appreciate it. I will go through and clean up some of this. \$\endgroup\$
    – Kevin
    Mar 3 '15 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.