While loop displaying over different rows instead of same row

Question

I have three tables:

Student - UPN, Name, Year, House
Seclusion_Status - ID, Arrived, FTE, Rebuild, DateTimeAdded, Staff, Student_UPN (fk), Comment
Period_Rating - ID, Slot_ID, Rating, Date, Seclusion_ID (fk)

Each student can have many entries in the Seclusion_Status table, and then there are also many entries in the Period_rating table, which is linked to the Seclusion_status table with the Seclusion_ID

I am running the following query to return a record from Seclusion_Status based on a date, and then all the records in the Period_rating table that relate to the Seclusion_status record.

$sql="SELECT * FROM Seclusion_Status 
  INNER JOIN Students ON Seclusion_Status.Student_UPN=Students.UPN 
  JOIN Period_Rating ON Seclusion_Status.ID=period_rating.Seclusion_ID
  WHERE period_rating.Date = '$start' 
  ORDER BY Seclusion_Status.DateTimeAdded ASC";
$result=mysql_query($sql);

I am then looping through the results using a while loop:

while($rows=mysql_fetch_array($result)){

And then displaying the results in a table. The problem that I am having is that where there are multiple entries in the period_rating table, each student is being duplicated on different rows, instead of the same row.

This is the code that I am using to write out the data from the while loop:

<tbody>
<tr>            
    <!--Write out the Student name and year group, and set the colour based on their House/College-->
                      <?php if($rows['House'] == 'Acer') {
                      echo '<td width="150px" bgcolor="#003399">' . $rows['Name'] . ' <p>(' . $rows['Year'] . ')</p>' .  '</td>';
                      }else if($rows['House'] == 'Clarus') {    
                      echo '<td width="150px" bgcolor="#FF0000">' . $rows['Name'] . ' <p>(' . $rows['Year'] . ')</p>' .  '</td>';   
                      }else if($rows['House'] == 'Fortis') {    
                      echo '<td width="150px" bgcolor="#02A10C">' . $rows['Name'] . ' <p>(' . $rows['Year'] . ')</p>' .  '</td>';   
                      }else{
                      echo '<td width="150px" bgcolor="#D2D904">' . $rows['Name'] . ' <p>(' . $rows['Year'] . ')</p>' .  '</td>';       
                      }
                      ?>

        <!--Write Out the Staff Name-->
        <td><p><?php echo $rows['Staff']; ?> </p></td>

        <!--Write out the comment and the incident type in brackets-->
        <td width="210px"><p><?php echo $rows['Comment']; ?> (<?php echo $rows['Incident']; ?>) </p></td>

        <!--Start writing out the ratings for Period 1-->
            <form action="P1Append.php?PrimaryID=<?php echo $rows['PrimaryID']; ?>&startdate=<?php echo $start; ?>" method="post">
            <?php 
                if ($rows['Slot_ID'] == P1)
                {
                    if (empty($rows['Rating'])) 
                    {
                        echo '<td><select onchange="this.form.submit()" style=" width:30px; height:30px;font-size:12pt; background-color:white;" type="text" name="P1" id="P1" maxlength="15"   size="1"><option disabled selected></option><option>G</option><option>A</option><option>R</option></td>';
                    }else if ($rows['Rating'] == G)
                    {
                        echo '<td bgcolor="#02A10C">' . $rows['Rating'] . '</td>';
                    }else if ($rows['Rating'] == A)
                    {
                        echo '<td bgcolor="#ff9900">' . $rows['Rating'] . '</td>';
                    }else if ($rows['Rating'] == R)
                    {
                        echo '<td bgcolor="#FF0000">' . $rows['Rating'] . '</td>';
                    }
                }else
                {
                    echo '<td><img src="images\add.png" width="20px"></td>';
                }
            ?>
            </form>

            <!--Start writing out the ratings for Period 2-->
            <form action="P2Append.php?PrimaryID=<?php echo $rows['PrimaryID']; ?>&startdate=<?php echo $start; ?>" method="post">
            <?php 
                if ($rows['Slot_ID'] == P2)
                {
                    if (empty($rows['Rating'])) 
                    {
                        echo '<td><select onchange="this.form.submit()" style=" width:30px; height:30px;font-size:12pt; background-color:white;" type="text" name="P2" id="P2" maxlength="15"   size="1"><option disabled selected></option><option>G</option><option>A</option><option>R</option></td>';
                    }else if ($rows['Rating'] == G)
                    {
                        echo '<td bgcolor="#02A10C">' . $rows['Rating'] . '</td>';
                    }else if ($rows['Rating'] == A)
                    {
                        echo '<td bgcolor="#ff9900">' . $rows['Rating'] . '</td>';
                    }else if ($rows['Rating'] == R)
                    {
                        echo '<td bgcolor="#FF0000">' . $rows['Rating'] . '</td>';
                    }
                    }else
                    {
                    echo '<td><img src="images\add.png" width="20px"></td>';
                }
            ?>
            </form>

Here is an example of the data

Students:

UPN | name | House | Year
---------------------------
112  | john  | Acer  | Year 9
113  | jack  | Acer  | Year 9

Seclusion_Status:

id | Student_UPN | Arrived | FTE | etc
-----------------------------
1  | 112          | Y      | N   | 
2  | 113          | N      | N   |

Period_rating:

id | Slot_ID | Rating | Seclusion_ID |
----------------------------------------
1  | P1      | G      | 1
2  | P2      | R      | 1
3  | P3      | G      | 1

Whats currently being returned is:

 Name | Student_UPN | Slot_ID | Rating
---------------------------------------------
John  | 112         | P1      | G
John  | 112         | P2      | R
John  | 112         | P3      | G

What I would like is:

Name | Student_UPN | P1 Rating | P2 Rating | P3 Rating
---------------------------------------------
John  | 112         | G        | R         | R

Hope that makes a little bit more sense?

So following the linked question, I have updated my query to this, but it is still not working?

 $sql="SELECT *
                MAX(case when period_rating.Slot_ID = 'P1' THEN Rating ELSE Null END) 'P1',
                MAX(case when period_rating.Slot_ID = 'P2' THEN Rating ELSE Null END) 'P2',
                MAX(case when period_rating.Slot_ID = 'LF' THEN Rating ELSE Null END) 'LF',
                MAX(case when period_rating.Slot_ID = 'BR' THEN Rating ELSE Null END) 'BR',
                MAX(case when period_rating.Slot_ID = 'P3' THEN Rating ELSE Null END) 'P3',
                MAX(case when period_rating.Slot_ID = 'P4' THEN Rating ELSE Null END) 'P4',
                MAX(case when period_rating.Slot_ID = 'LC' THEN Rating ELSE Null END) 'LC',
                MAX(case when period_rating.Slot_ID = 'P5' THEN Rating ELSE Null END) 'P5',
                MAX(case when period_rating.Slot_ID = 'P6' THEN Rating ELSE Null END) 'P6',
                MAX(case when period_rating.Slot_ID = 'DT' THEN Rating ELSE Null END) 'DT'
            FROM Seclusion_Status
            INNER JOIN Students
                ON Seclusion_Status.Student_UPN=Students.UPN
            INNER JOIN Period_Rating
                ON Seclusion_Status.ID=period_rating.Seclusion_ID
            WHERE period_rating.Date = '$start'
            GROUP BY Seclusion_Status.Student_UPN
            ORDER BY Seclusion_Status.DateTimeAdded ASC";

Can you pls provide sample source data and sample desired output? At the moment I can't really picture what you are after. — Shadow, Mar 04 '16 at 13:01
What is the best way for me to provide this? Basically for each row in the table I want the following columns: Student Name, Staff, Comment, Period 1, Period 2, Period 3 etc Period 1 should take the data from the period_rating table where the ID is P1, Period 2 where the ID is P2 etc — user3122230, Mar 04 '16 at 13:05
@Shadow I have now edited the original question with some sample data which hopefully shows what I am trying to do?? — user3122230, Mar 04 '16 at 14:15
Now it makes a lot more sense. This technique is called dynamic pivoting or dynamic cross tabulated (crosstab) query. It's been discussed several time already here on SO — Shadow, Mar 04 '16 at 14:16
Possible duplicate of [MySQL pivot row into dynamic number of columns](http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) — Shadow, Mar 04 '16 at 14:17
Many thanks @shadow. Now I know what the technique is called, I will do a bit of reading up, and see if I can figure it out!! — user3122230, Mar 04 '16 at 14:19
Check out the duplicate link I gave you. The accepted answer describes both static and dynamic pivots within mysql. — Shadow, Mar 04 '16 at 14:21
Really struggling with this now. I have updated the original question to the query that I am trying, but still having no luck. Can anyone look at the query and see where I am going wrong? — user3122230, Mar 07 '16 at 11:13
What do you mean by that the last query is not working? What error message do you receive or unexpected behaviour you encounter? — Shadow, Mar 07 '16 at 11:29

score 0 · Answer 1 · answered Mar 07 '16 at 11:45

0

You seem to be on the right track, however, the case statements within your max() functions do not seem to make sense to me based on the table structure you provided:

MAX(case when period_rating.Rating = 'P1' THEN Student END) 'P1'

P1, P2, P3, etc values seem to be in the Slot_ID field, not in the Rating field.
I do not see Student field in your table structure. Based on the description in your question, you would like to return the Rating field in the column.
I would place an explicit null in the else branch of each case statement.

An overall comment: if you are after Name and Student_UPN fields in the output, then replace * in the select list with these 2 columns and list them in the group by list as well.

answered Mar 07 '16 at 11:45

Shadow

33,525
10
51
64

Thanks for the pointers. I have updated the query in the original question, and I am still not getting any output (although there is no error message showing). What do you mean with the null in the else branch? – user3122230 Mar 07 '16 at 12:19
Khm, you could have read the manual on case: http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case – Shadow Mar 07 '16 at 12:27
If you do not get any error message but no records are retrieved, that means your query does not return any rows. It could be a result of the inner joins or the where criteria. Since I cannot see your data, I cannot help you with that. Start removing where criteria, then change inner joins to left/right joins to see where things go south. I would use a mysql management application (e.g. sg like phpmyadmin) to run the sql statement first to see if it works. When you sorted the sql, then move the sql into your php code. – Shadow Mar 07 '16 at 12:31
I did read the manual, but when I googles MySQL Case it took me to another page that didnt mention the ELSE bit, which is why I was confused. – user3122230 Mar 07 '16 at 12:35

score 0 · Accepted Answer · answered Mar 07 '16 at 16:06

Okay, So I finally got there in the end, by using the following query:

SELECT seclusion_status.ID, seclusion_status.Arrived, seclusion_status.FTE, seclusion_status.Rebuild, seclusion_status.Text, seclusion_status.DateTimeAdded, seclusion_status.Staff, seclusion_status.Student_UPN, seclusion_status.Incident, seclusion_status.Comment, students.Name, students.UPN, students.Year, students.House, period_rating.ID, period_rating.Slot_ID, period_rating.Rating, period_rating.Date, period_rating.Seclusion_ID,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'P1' THEN period_rating.Rating ELSE NULL END)) AS Period1_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'P2' THEN period_rating.Rating ELSE NULL END)) AS Period2_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'LF' THEN period_rating.Rating ELSE NULL END)) AS LF_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'BR' THEN period_rating.Rating ELSE NULL END)) AS BR_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'P3' THEN period_rating.Rating ELSE NULL END)) AS Period3_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'P4' THEN period_rating.Rating ELSE NULL END)) AS Period4_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'LC' THEN period_rating.Rating ELSE NULL END)) AS LC_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'P5' THEN period_rating.Rating ELSE NULL END)) AS Period5_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'P6' THEN period_rating.Rating ELSE NULL END)) AS Period6_Rating,
            GROUP_CONCAT((CASE period_rating.Slot_ID WHEN 'DT' THEN period_rating.Rating ELSE NULL END)) AS DT_Rating
        FROM Seclusion_Status
        INNER JOIN Students
            ON Seclusion_Status.Student_UPN=Students.UPN
        INNER JOIN Period_Rating
            ON Seclusion_Status.ID=period_rating.Seclusion_ID
        WHERE period_rating.Date = '$start'
        GROUP BY period_rating.Seclusion_ID
        ORDER BY Seclusion_Status.DateTimeAdded ASC

While loop displaying over different rows instead of same row

2 Answers2