I want to output results from a query in a certain way using php

[u0867587]

Below is my query which works fine:

 

$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)."')

          ";

 

Below is my results outputted by using php:

 

    Course: INFO101 - Bsc Information Communication Technology Year: 3

    Student: Mayur Patel (u0867587)

   

    Module: CHI2550 - Modern Database Applications

    Session: AAB 72 (A)

   

    Course: INFO101 - Bsc Information Communication Technology Year: 3

    Student: Mayur Patel (u0867587)

   

    Module: CHI2513 - Systems Strategy

    Session: AAD 61 (B)

   

    Course: INFO101 - Bsc Information Communication Technology Year: 3

    Student: Mayur Patel (u0867587)

   

    Module: CHI2550 - Modern Database Applications

    Session: AAE 67 (B)

 

How do I display it using php so that it only shows Course details and Student details only once, it will show each module in the course only once and shows each session being below each module it belongs to:

 

The output from above should look like this in other words:

 

    Course: INFO101 - Bsc Information Communication Technology Year: 3

        Student: Mayur Patel (u0867587)

       

        Module: CHI2550 - Modern Database Applications

        Session: AAB 72 (A)

        Session: AAE 67 (B)

       

        Module: CHI2513 - Systems Strategy

        Session: AAD 61 (B)

 

 

PHP code to output the results:

 

    $output1 = "";

   

            while ($row = mysql_fetch_array($result)) { //$result is the query

           

        $output1 .= "

          <p><strong>Course:</strong> {$row['CourseId']} - {$row['CourseName']} <strong>Year:</strong> {$row['Year']}<br/>

              <strong>Student:</strong> {$row['StudentForename']} {$row['StudentSurname']} ({$row['StudentUsername']})

              </p>";

             

        $output1 .= "     

        <p><strong>Module:</strong> {$row['ModuleId']} - {$row['ModuleName']} <br/>

              <strong>Session:</strong> {$row['SessionId']} {$row['Mark']} ({$row['Grade']})

              </p>";

                }

               

                echo $output1;

 

Thank You

 

[Psycho]

First change your query to ORDER the results by course, student. Note I also changed the order of the SELECT fields. I like to put them on separate lines by each table in the order the tables are included. Makes it easy to see what is coming from where

$query = "SELECT c.CourseName,
                 st.CourseId, st.Year, st.StudentUsername, st.StudentForename, st.StudentSurname,
                 gr.Mark, gr.Grade,
                 s.ModuleId,s.SessionId, s.SessionWeight,
                 m.ModuleName, m.Credits, 
          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";

 

Next, change your output code to detect changes in the course, student and module (you might need to play around with the formatting to get it how you want)

$output1 = "";
$courseID  = false;
$student  = false;
$moduleID  = false;
while ($row = mysql_fetch_array($result))
{ //$result is the query

    if($courseID != $row['CourseId'])
    {
        $course = $row['CourseId'];
        $output1 .= "<p><br><strong>Course:</strong> {$row['CourseId']} - {$row['CourseName']} <strong>Year:</strong> {$row['Year']}</p>"
    }

    if($student != $row['StudentUsername'])
    {
        $student = $row['StudentUsername'];
        $output1 .= "<p><strong>Student:</strong> {$row['StudentForename']} {$row['StudentSurname']} ({$row['StudentUsername']})</p>"
    }

    if($moduleID != $row['ModuleId'])
    {
        $moduleID = $row['ModuleId'];
        $output1 .= "<p><br><strong>Module:</strong> {$row['ModuleId']} - {$row['ModuleName']}</p>"
    }

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

echo $output1;

[u0867587]

That has worked brilliantly, Thank You very much. Big Thank You