6 Replies - 1132 Views - Last Post: 09 November 2011 - 10:19 AM Rate Topic: -----

#1 manleybruce   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 07-November 11

Calculation only going through one row and not multiple rows

Posted 08 November 2011 - 07:32 PM

I have a slight calculation probelm.

Below is the my output:

Student: Mayur Patel (u0867587)
Course: INFO101 - Bsc Information Communication Technology Course Mark:62

Module: CHI2550 - Modern Database Applications Module Mark: 41 Mark Percentage: 68 Grade: B

Session Session Mark Session Weight
AAB 72 20%
Session Session Mark Session Weight
AAE 67 40%

Module: CHI2513 - Systems Strategy Module Mark: 31 Mark Percentage: 62 Grade: B

Session Session Mark Session Weight
AAD 61 50%

There is only one problem with this output. The only last little probelm is that the Course Mark is incorrect. It should add up to 65 (adding the mark percentage together (68 + 63 = 130) and divide by number of modules (2) and that should equal 65). Instead it adds up to 62. Now the calculations I have done is 200% correct. The problem those is that the the reason it is outputting 62 is because it is only selecting the rows for the last module (so calculation it is doing is 62 divide by 1 which equals 62). Now the reason for this is because my foreach loop where the course details and calculation is in only outputs one course detail from the database (as there is no need to show the course details multiple times). Because of this the calculation is only picking out rows from the last row only.

What I want to know is that is there a way the calculation can go through all the rows but I can keep it in the same foreach loop which displays only once course detail. If calculation is not in same foreach loop then I will get undefined variables.

Thank You

Below is the code I believe is relevant for this question. The foreach is for the course details only.

 $output = "";  
        
        $studentId = false;
        
		// load query data in a multidimensional array 
        $dataArray = array(); 
        
        while ($row = mysql_fetch_array($result)) { 
            $dataArray[$row['CourseId']]['CourseName'] = $row['CourseName']; 
            $dataArray[$row['CourseId']]['ModuleId'] = $row['ModuleId']; 
            $dataArray[$row['CourseId']]['SessionWeight'] = $row['SessionWeight']; 
            $dataArray[$row['CourseId']]['Mark'] = $row['Mark']; 
            $dataArray[$row['CourseId']]['Modules'][$row['ModuleId']]['ModuleName'] = $row['ModuleName']; 
            $dataArray[$row['CourseId']]['Modules'][$row['ModuleId']]['Sessions'][$row['SessionId']]['Mark'] = $row['Mark']; 
            $dataArray[$row['CourseId']]['Modules'][$row['ModuleId']]['Sessions'][$row['SessionId']]['SessionWeight'] = $row['SessionWeight']; 
            
                 if($studentId != $row['StudentUsername']) 
    { 

        //Student has changed 
        $studentId = $row['StudentUsername']; 

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

    } 
        } 

        // just for debugging purposes, let's do a print_r of the array 
        // eliminate this line when you don't need it anymore 
        // print_r($dataArray); 

        foreach ($dataArray as $courseId => $courseData) {  
           // elaborate course data 
           
           $markTotal = 0;  
             $markGrade = 0;  
             $weightSession = 0; 
             $moduleCount = 0;
             $courseTotal = 0;
             
              $markTotal += round($courseData['Mark'] / 100 * $courseData['SessionWeight']);  
                $weightSession  += ($courseData['SessionWeight']);  
                $markGrade += round($markTotal /  $weightSession * 100); 
                $moduleCount += count($courseData['ModuleId']);
             	$courseTotal += ($markGrade / $moduleCount);
            
             	echo $output;  
       
       }  // <-- end of courses foreach


Is This A Good Question/Topic? 0
  • +

Replies To: Calculation only going through one row and not multiple rows

#2 codeprada   User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 963
  • View blog
  • Posts: 2,382
  • Joined: 15-February 11

Re: Calculation only going through one row and not multiple rows

Posted 08 November 2011 - 08:18 PM

Since you're using mysql_fetch_array it's safe to say you're using a database. Databases can perform these calculations for you instead of you having to do it manually. There are many reasons why your calculations only include the last module. Could be a conditional constraint which specifies certain rows.

We could give you a better answer if you showed us your query along with your table structure.
Was This Post Helpful? 0
  • +
  • -

#3 manleybruce   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 07-November 11

Re: Calculation only going through one row and not multiple rows

Posted 08 November 2011 - 08:32 PM

Below is query and my database mode is in the attached file. The database model is probably missing three tables but they are nothing to do with student's results, they're just tables to store video, audio and images. Don't worry about them.

Query:
	$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)."')
				 OR
DER BY c.CourseName, st.StudentUsername, m.ModuleName, s.SessionId
";

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#4 codeprada   User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 963
  • View blog
  • Posts: 2,382
  • Joined: 15-February 11

Re: Calculation only going through one row and not multiple rows

Posted 08 November 2011 - 09:23 PM

Check out these functions offered by MySQL to perform average and so forth. Your last row is the only row that's meeting the WHERE's clause's requirement. I'm not sure why this is so but be sure to make sure the tables that you're joining contains the relevant fields to satisfy a join.
Was This Post Helpful? 0
  • +
  • -

#5 manleybruce   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 07-November 11

Re: Calculation only going through one row and not multiple rows

Posted 09 November 2011 - 04:04 AM

Hi,

I managed to sort out the calculation but there is just one last question and then this page is completley finished. The only problem is that to be able to get the answer of the calculation, I had to display the course details at the bottom, so the output looks like this below:

Student: Mayur Patel (u0867587)
Module: CHI2550 - Modern Database Applications Module Mark: 41 Mark Percentage: 68 Grade: B

Session Session Mark Session Weight
AAB 72 20%
Session Session Mark Session Weight
AAE 67 40%

Module: CHI2513 - Systems Strategy Module Mark: 31 Mark Percentage: 62 Grade: B

Session Session Mark Session Weight
AAD 61 50%

Course: INFO101 - Bsc Information Communication Technology Course Mark: 65

I want the course details to be outputted above the modules so it looks like this below:

Student: Mayur Patel (u0867587)
Course: INFO101 - Bsc Information Communication Technology Course Mark: 65

Module: CHI2550 - Modern Database Applications Module Mark: 41 Mark Percentage: 68 Grade: B

Session Session Mark Session Weight
AAB 72 20%
Session Session Mark Session Weight
AAE 67 40%

Module: CHI2513 - Systems Strategy Module Mark: 31 Mark Percentage: 62 Grade: B

Session Session Mark Session Weight
AAD 61 50%

Problem is that if I keep the course details at the bottom then the calculation works. I move it to the top and then the calculation would not work as the calculation for the $courseGrade is at the bottom and so for the Course Mark it will display 0.

So my question is how can I move the course details to the top and still have the answer of the calculation displayed for the course mark?

Below is the code:


$dataArray = array(); 
        
        while ($row = mysql_fetch_array($result)) { 
            $dataArray[$row['CourseId']]['CourseName'] = $row['CourseName']; 
            $dataArray[$row['CourseId']]['Modules'][$row['ModuleId']]['ModuleName'] = $row['ModuleName']; 
            $dataArray[$row['CourseId']]['Modules'][$row['ModuleId']]['Sessions'][$row['SessionId']]['Mark'] = $row['Mark']; 
            $dataArray[$row['CourseId']]['Modules'][$row['ModuleId']]['Sessions'][$row['SessionId']]['SessionWeight'] = $row['SessionWeight']; 
            
                 if($studentId != $row['StudentUsername']) 
    { 

        //Student has changed 
        $studentId = $row['StudentUsername']; 

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

    } 
        } 

        // just for debugging purposes, let's do a print_r of the array 
        // eliminate this line when you don't need it anymore 
        // print_r($dataArray); 

        foreach ($dataArray as $courseId => $courseData) {  
           // elaborate course data 
           
           // elaborate course data 
           $moduleCount = 0;
           $courseTotal = 0;
           $courseGrade = 0;
           
           $courseHTML = ""; 
           
           $courseHTML .= "<br><table><tr><th>Course:</th><td>" . $courseId .  " - "  . $courseData['CourseName'] . "</td>"; 

           foreach ($courseData['Modules'] as $moduleId => $moduleData) { 
             // elaborate module data 
             $moduleHTML = "";
             
             $moduleHTML .= "<br><table><tr><th>Module:</th><td>" . $moduleId . " - " . $moduleData['ModuleName'] ."</td>"; 

             $markTotal = 0;  
             $markGrade = 0;  
             $weightSession = 0; 
             $grade = "";  
             $sessionsHTML = ""; 
             
              
             foreach ($moduleData['Sessions'] as $sessionId => $sessionData) { 
                // elaborate session data 
                $markTotal += round($sessionData['Mark'] / 100 * $sessionData['SessionWeight']);  
                $weightSession  += ($sessionData['SessionWeight']);  
                $sessionsHTML .= "<table><tr><th>Session</th><th>Session Mark</th><th>Session Weight</th></tr><tr><td>" . $sessionId . "</td><td>" . $sessionData['Mark'] . "</td><td>" . $sessionData['SessionWeight'] ."%</td></tr></table>\n";  
             } 
             $markGrade = round($markTotal /  $weightSession * 100); 
             
              // To count the modules, simply add 1 to the counter
             $moduleCount++;
             // Add the mark grade to the course total
             $courseTotal += $markGrade;

              
             if ($markGrade >= 70) { $grade = "A"; }  
             else if ($markGrade >= 60 && $markGrade <= 69) { $grade = "B"; }  
             else if ($markGrade >= 50 && $markGrade <= 59) { $grade = "C"; }  
             else if ($markGrade >= 40 && $markGrade <= 49) { $grade = "D"; }  
             else if ($markGrade >= 30 && $markGrade <= 39) { $grade = "E"; }  
             else if ($markGrade >= 0 && $markGrade <= 29) { $grade = "F"; }              

             $moduleHTML .= " <th>Module Mark:</th><td>" . $markTotal . "</td><th>Mark Percentage:</th><td>" . $markGrade . "</td><th>Grade:</th><td>" . $grade . " </td></tr></table><br>";   
             $output .= $moduleHTML . $sessionsHTML; 
           }  // <-- end of sessions foreach 
         }  // <-- end of modules foreach 
         
         // at the end of each course, you can calculate the course grade
           $courseGrade = ($courseTotal / $moduleCount);
           
         $courseHTML .= " <th>Course Mark:" . $courseGrade . "</th></tr></table>";
                    
           $output .= $courseHTML;
          //Display the output  
       echo $output;  
       
       }  // <-- end of courses foreach 

Was This Post Helpful? 0
  • +
  • -

#6 codeprada   User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 963
  • View blog
  • Posts: 2,382
  • Joined: 15-February 11

Re: Calculation only going through one row and not multiple rows

Posted 09 November 2011 - 06:39 AM

Since you're doing the calculation's manually you'll have to move it above the Course information and then store the values instead of outputting them.

You should move the calculations into functions that way you'll have more control on when they're called.
Was This Post Helpful? 1
  • +
  • -

#7 manleybruce   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 07-November 11

Re: Calculation only going through one row and not multiple rows

Posted 09 November 2011 - 10:19 AM

I already sorted it out but thanks anyway. What I did is that I coded my output like this output = courseHtml . output
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1