Problem getting the right calculation answer in php

[u0867587]

Hi,

 

I have a calculation issue in my php. Below is my code:

 

$output = "";
$studentId  = false;
$courseId  = false;
$moduleId  = false;
        
while ($row = mysql_fetch_array($result)){    

        $moduletotal += $row['Mark'];
        
        $modulemark = (int)($moduletotal);
          
//Above here is the calculation for add in the Marks of all the sessions

//Below is the code of the display of the query output


if($studentId != $row['StudentUsername'])  {        

	$studentId = $row['StudentUsername'];        
	$output .= "<p><strong>Student:</strong> {$row['StudentForename']} {$row['StudentSurname']} ({$row['StudentUsername']})";   

   }    
   
   	if($courseId != $row['CourseId']) {    

	$courseId = $row['CourseId'];        		
	$output .= "<br><strong>Course:</strong> {$row['CourseId']} - {$row['CourseName']} <br><strong>Year:</strong> {$row['Year']}</p>";    

	}    

if($moduleId != $row['ModuleId'])  {        

   $moduleId = $row['ModuleId'];        
   $output .= "<p><br><strong>Module:</strong> {$row['ModuleId']} - {$row['ModuleName']} $modulemark</p>";   

  }  
    
   $output .= "<p><strong>Session:</strong> {$row['SessionId']} {$row['Mark']}</p>";
			   
  }

 

 

Student: Mayur Patel (u0867587)

Course: INFO101 - Bsc Information Communication Technology 

Year: 3

 

 

Module: CHI2550 - Modern Database Applications 72 (72 is the answer to the calculation for the first module but this is incorrect at it should also add the 67 and thus become 139)

 

Session: AAB 72

 

Session: AAE 67

 

Module: CHI2513 - Systems Strategy 200 (200 is the answer to the calculation for this module but this is incorrect it should be only 61. But what it has done is that it has added the 72 and 67 from the first module and added it with the 61 in this module)

 

Session: AAD 61

 

 

 

SO WHAT MY QUESTION IS THAT HOW CAN I FIND THE TOTAL OF EACH MODULE MARK WHICH THE VARIABLE $modulemark = (int)($moduletotal); IS TRYING TO DO BY ADDING UP ALL THE SESSIONS MARKS WITHIN EACH MODULE.

 

I HAVE TRIED USING SUM(gr.Mark) in the query and using GROUP BY SessionId, ModuleId, StudentUsername, and CourseId but it ends up not being able to display any records. That is why I want to do the calculation using php rather than SQL.

 

BELOW IS THE QUERY IF YOU WANT TO SEE IT:

 

$query = "
          SELECT st.CourseId, c.CourseName, st.Year, st.StudentUsername, st.StudentForename, st.StudentSurname,
          s.ModuleId, m.ModuleName, m.Credits, s.SessionId, s.SessionWeight, gr.Mark, gr.Grade
          FROM Course c
          INNER JOIN Student st ON c.CourseId = st.CourseId
          JOIN Grade_Report gr ON st.StudentId = gr.StudentId
          JOIN Session s ON gr.SessionId = s.SessionId
          JOIN Module m ON s.ModuleId = m.ModuleId
          WHERE
          (st.StudentUsername = '".mysql_real_escape_string($studentid)."')
          ORDER BY c.CourseName, st.StudentUsername, m.ModuleName, s.SessionId
          ";