Displaying weekly row data into columns

I have a table like teachers rate (1-5) students every week. Each teacher can rate each student only once druing a week.
And I won’t know how many weeks are there it could be any number between 1 to 52

id, week_number, student, teacher, rating
1, 1, Student1, Teacher1, 3
2, 1, Student2, Teacher2, 5
3, 2, Student1, Teacher1, 5
4, 2, Student2, Teacher2, 4
4, 2, Student1, Teacher2, 5

I want to display yearly rating data for a selected student say Student1

Teacher, Week 1, Week2, Week 3…
Teacher1, 3, 2, -
Teacher2, -, 5, -

week_number above actually links to a weeks table through id so I can find out how maany weeks are going to be there. But I am not able to produce the output I want.

It is trying to split row data into rows and columns. The above is simplified version of actual table.

Any working solutions?

this type of reformatting is best done in the application language (php or whatever you’re using)

trying to do it with sql is ~really~ messy, especially if the number of weeks is not known

Yeah have been trying in php but have not been successful so far. I have read there are better solutions in sql. People seem to have done it but couldn’t find what I needed. There are even more complications which I have left it out from above.

what you read is wrong :slight_smile:

I might be wrong. Could you please give me some starting point in how to go about it. I mean weeks results is in rows but I want to display them in columns. To add to complication there is ‘period’ field as well. So a teacher could add rating for a student more than once but only once for a period in one week.

I get data in this format through query:

“teacher”;“period”;“week”;“rating”
“Kurt D”;“2”;“6”;“2”
“Teacher B”;“3”;“7”;“2”
“Teacher B”;“7”;“7”;“2”
“Teacher B”;“7”;“6”;“3”
“Teacher B”;“3”;“6”;“1”
“Teacher B”;“1”;“6”;“2”

Teachers + Period needs to be on left side and weeks at top.

What I am trying to display is Rating given by each Teacher in each period for each week.

:confused:

if you mean with php, sorry, i don’t do php

if you mean with sql, sorry, no, i won’t

No one else here does PHP?

I’ve done a little bit of PHP

The “better SQL solutions” are when you can optimize the query to limit the result set and maybe order it. eg. instead of SELECT * and then using PHP to weed out, SELECT only what you are planning to use, instead of SELECT date and using PHP to sort, use ORDER BY

I guess there are two ways I might do it.

I might format the results while I’m looping through them.
More likely, I would “separate” the output formatting code from the query code.

If you [FPHP]var_dump/FPHP a typical row returned by the query what does it look like?

This is the data I am getting returned through query and I need all these fields that I need:

“teacher”;“period”;“week”;“rating”
“Kurt D”;“2”;“6”;“2”
“Teacher B”;“3”;“7”;“2”
“Teacher B”;“7”;“7”;“2”
“Teacher B”;“7”;“6”;“3”
“Teacher B”;“3”;“6”;“1”
“Teacher B”;“1”;“6”;“2”

var_dump for two rows might looks like this:

array(2) {
[0]=>
array(4) {
[“rating”]=>
string(1) “4”
[“week”]=>
string(1) “6”
[“period”]=>
string(1) “2”
[“teacher”]=>
string(6) “Kurt D”
}
[1]=>
array(4) {
[“rating”]=>
string(1) “2”
[“week”]=>
string(1) “7”
[“period”]=>
string(1) “7”
[“teacher”]=>
string(9) “Teacher B”
}
}

It’ll needs to be displayed like

Teacher-Period --------Week 1 -------------- Week 2 -----------and so on---- Teacher Avg
Kurt D - Period 2 ----- Rating Week 1 ------ Rating week2-----------------------
Teacher B - Period 3
Teacher B-Period 7
Teacher B- Period 7
Teacher B- Period 3
…Week1 Avg ----Week2 Avg-----

One way or the other it could be done with manipulation in PHP.

It is more complicated than the above (like when there is zero for a rating ignore it when calculating average) but for now this is enough

(done in the span of 4 minutes without looking very hard, so take it for what it’s worth.)


$maxweek = 0;
while($row = mysql_fetch_assoc($result)) {
 $teachers[$row['teacher']][$row['period']][$row['week'] = $row['rating'];
 $maxweek = ($maxweek > $row['week']) ? $maxweek : $row['week'];
}
//Output header row here
foreach($teachers AS $teacher) {
  foreach($teacher AS $period) {
   ksort($period); //Get the weeks in the right order.
   $avg = 0;
   $div = 0;
   for($i = 1; $i < $maxweek; $i++) {
     if (isset($period[$i])) {
          echo $period[$i];
          $avg += $period[$i];
          $div++;
     }
     else { echo 0; }
   }
   echo ($avg / $div); //There's your average.
  }
}

Thank you very much StarLion.

It isn’t producing what I want but I think I can build upon it.

for data like this:

array(7) {
[0]=>
array(4) {
[“rating”]=>
string(1) “1”
[“week”]=>
string(1) “6”
[“period”]=>
string(1) “2”
[“teacher”]=>
string(6) “Kurt D”
}
[1]=>
array(4) {
[“rating”]=>
string(1) “2”
[“week”]=>
string(1) “7”
[“period”]=>
string(1) “7”
[“teacher”]=>
string(9) “Teacher B”
}
[2]=>
array(4) {
[“rating”]=>
string(1) “5”
[“week”]=>
string(1) “7”
[“period”]=>
string(1) “3”
[“teacher”]=>
string(9) “Teacher B”
}
[3]=>
array(4) {
[“rating”]=>
string(1) “2”
[“week”]=>
string(1) “6”
[“period”]=>
string(1) “7”
[“teacher”]=>
string(9) “Teacher B”
}
[4]=>
array(4) {
[“rating”]=>
string(1) “1”
[“week”]=>
string(1) “6”
[“period”]=>
string(1) “3”
[“teacher”]=>
string(9) “Teacher B”
}
[5]=>
array(4) {
[“rating”]=>
string(1) “1”
[“week”]=>
string(1) “6”
[“period”]=>
string(1) “1”
[“teacher”]=>
string(9) “Teacher B”
}
[6]=>
array(4) {
[“rating”]=>
string(1) “5”
[“week”]=>
string(1) “1”
[“period”]=>
string(1) “3”
[“teacher”]=>
string(9) “Teacher B”
}
}

It produced result like this (I added spaces and line breaks for clarity:

0 0 0 0 0 1 1
0 0 0 0 0 2 2
5 0 0 0 0 1 3
0 0 0 0 0 1 1

This has done it. Thank you very very much StarLion. Looks like I came to right forum where FEW people like you and Mittineague are ready to help.


	$maxweek = 0;
	$teachers = array();
	foreach ($data as $row)
	{
		$teachers['Period '. $row['period'] .' - '. $row['teacher']][$row['week']] = $row['rating'];
 		$maxweek = ($maxweek > $row['week']) ? $maxweek : $row['week'];
	}
	
	//Output header row here
	echo "Teacher Name, ";
	for ($i = 1; $i <= $maxweek; $i++)
   	{
   		echo "Week $i, ";			
   	}
   	echo "Average <BR>";
	foreach($teachers AS $name=>$teacher)
	{
  		ksort($teacher); //Get the weeks in the right order.
   		$avg = 0;
   		$div = 0;
   		echo $name . ', ';
   		   			
   		for($i = 1; $i <= $maxweek; $i++)
   		{
     		if (isset($teacher[$i]))
     		{
          		echo $teacher[$i] . ', ';
          		$avg += $teacher[$i];
    		    $div++;
     		}
     		else { echo 0 . ', '; }
   		}
   		echo ceil($avg / $div) . "<BR>"; //There's your average.*/
  	
	}	
	 
}

Produces this output:

Teacher Name, Week 1, Week 2, Week 3, Week 4, Week 5, Week 6, Week 7, Average
Period 1 - Teacher B, 0, 0, 0, 0, 0, 1, 0, 1
Period 2 - Kurt D, 0, 0, 0, 0, 0, 1, 0, 1
Period 3 - Teacher B, 5, 0, 0, 0, 0, 1, 5, 4
Period 7 - Teacher B, 0, 0, 0, 0, 0, 2, 2, 2

So looking at it, I see a couple of errors in the code… the for loop should have run <= rather than <, i missed a close bracket inside the while loop…

I have no idea how it managed to generate more data than you inputted though.


$maxweek = 0;
while($row = mysql_fetch_assoc($result)) {
 $teachers[$row['teacher']][$row['period']][$row['week']] = $row['rating'];
 $maxweek = ($maxweek > $row['week']) ? $maxweek : $row['week'];
}
print_r($teachers); //Debugging: Show output of teachers array.
//Output header row here
foreach($teachers AS $teachername => $teacher) {
  foreach($teacher AS $periodnum => $period) {
   ksort($period); //Get the weeks in the right order.
   $avg = 0;
   $div = 0;
   echo $teachername." (Period".$periodnum.") ";
   for($i = 1; $i <= $maxweek; $i++) {
     if (isset($period[$i])) {
          echo $period[$i]." ";
          $avg += $period[$i];
          $div++;
     }
     else { echo 0; }
   }
   echo ($avg / $div)."<br />"; //There's your average.
  }
}  

I wanted to just join Teacher name and Period for display purpose. So if a teacher ‘Teacher B’ teaches a student in period 1 and 2. I wanted to just display them as:

Teacher B - Period 1, rating for week 1 for period 1, rating for week 2 for period 1, Average for all Weeks for Teacher B Period 1
Teacher B - Period 2, rating for week 1 for period 2, rating for week 2 for period 2, Average for all Weeks for Teacher B Period 2

And output produced by your new code and your first code which I changed a bit to produce this data is almost same except that periods are not in order.

Period 1 - Teacher B, 0, 0, 0, 0, 0, 1, 0, 1
Period 2 - Kurt D, 0, 0, 0, 0, 0, 1, 0, 1
Period 3 - Teacher B, 5, 0, 0, 0, 0, 1, 5, 4
Period 7 - Teacher B, 0, 0, 0, 0, 0, 2, 2, 2

This is what your new code produces:

Teacher B (Period1) 0 0 0 0 0 1 0 1
Teacher B (Period3) 5 0 0 0 0 1 5 4
Teacher B (Period7) 0 0 0 0 0 2 2 2
Kurt D (Period2) 0 0 0 0 0 1 0 1

Problem is solved. Thanks Again.