0

I have a nested while statement that gives the results I need but is very inefficient and takes forever to run? Any thoughts on how these could be combined?

I first create a temporary table with all the data I need from a larger table called student grades. I then get the students and course names because I want to group these for each report. I then look for each student then get the scores for each assignment. I also want to get the average of the only of the last three assignments for each particular learning outcome ID.

//MYSQL query to create temp table
$qryTmp = "CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS (SELECT     studentsisid,coursename,assessmenttitle,studentname,outcomescore,recordid,learningoutcomeid,learningoutcomename,assessmentid
from studentgrades 
WHERE studentsisid LIKE '$studentid' AND coursename LIKE '$coursename')";
mysql_query($qryTmp);

//MYSQL query Coursename and student name
$qryCourse = "SELECT studentsisid,coursename,studentname
from tmp
WHERE studentsisid LIKE '$studentid' AND coursename LIKE '$coursename' GROUP BY studentsisid,coursename ORDER by studentname,coursename";
$resultCourse=mysql_query($qryCourse);
while ($rowCourse = mysql_fetch_assoc($resultCourse)) {
    $studentid = $rowCourse['studentsisid'];
    $name = $rowCourse['studentname'];
    $coursename = $rowCourse['coursename'];

//MYSQL query Learning Outcome ID
$qryLID ="SELECT outcomescore,learningoutcomeid,count(RecordID) as assessmentcount
from tmp 
WHERE studentsisid = '$studentid' AND coursename='$coursename'
Group by studentsisid,coursename, learningoutcomeid  ORDER BY studentname, coursename";
$resultLID=mysql_query($qryLID);

while ($rowLID = mysql_fetch_assoc($resultLID)) {
    $learningoutcomeid = (int)$rowLID['learningoutcomeid'];
    $assessmentcount = (int)$rowLID['assessmentcount'];

//MYSQL query recent 3
$qryRecent3 = "SELECT avg(outcomescore) as recentscoreavg 
        FROM 
        (SELECT outcomescore FROM tmp WHERE 
        studentsisid='$studentid' AND coursename='$coursename' AND learningoutcomeid='$learningoutcomeid' 
        ORDER BY AssessmentID DESC LIMIT 3) as r";  

$resultRecent3=mysql_query($qryRecent3);

        while ($rowRecent3 = mysql_fetch_assoc($resultRecent3)) {
            $recentscoreavg = number_format($rowRecent3['recentscoreavg'], 1);
            $assessmentcount = $rowRecent3['recentover3'];

    }
}
}
Kevin
  • 1
  • 3
  • 4
    Please do yourself and everyone reading this post a favour by formatting your code. – e4c5 Sep 07 '16 at 12:38
  • 2
    You sure you can't do the whole thing in one query using join(s)? – BadHorsie Sep 07 '16 at 12:40
  • 1
    Well qryRecent3 looks like it is doing a totally unnecessary sub query for a start – RiggsFolly Sep 07 '16 at 12:41
  • 1
    Not sure why you need to create a tmp table in which to do all this in the first place either – RiggsFolly Sep 07 '16 at 12:43
  • 2
    Please do yourself another favour and take a read: [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) – simon Sep 07 '16 at 12:47
  • RiggsRolly - the qryRecent3 runs for each student, coursename and learningoutcomeid separately hence the reason it is a subquery. I was not sure if running the qrytmp first would be quicker since it loads it all to memory and is only looking at the data I need – Kevin Sep 07 '16 at 12:47
  • Your code is vulnerable to SQL injection. you need to read up on and use [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Also regarding your last comment. if you can retrieve all the data you need in one query it's almost always more efficient than doing multiple requests. The reason it's slow is because it does a request to the database, waits for response and then goes onto the next. this overhead makes it slow. – Jester Sep 07 '16 at 12:54
  • Forget about all the PHP stuff for now. Instead just provide proper CREATE and INSERT statements, and a desired result. – Strawberry Sep 07 '16 at 13:02
  • This code works for the desired result. Essentially the question may come down to how to combine two Group By in one query. I want to first group by Student and Course, then after that grouping is done, average the outcomescore and get the recent average. I am hearing the first qrytmp is not necessary. I may be able to combine the qryLID and qryRecent3 and that may help significantly with the speed. – Kevin Sep 07 '16 at 13:12
  • If you want help with efficiency, then I think I've pointed towards a reasonable (and, dare I say, the only sensible) way forward. – Strawberry Sep 07 '16 at 13:16

0 Answers0