multiple foreach loop with mysql join

Question

I am not sure my database design and coding structure is efficient or not to fetch expected result.

For example, I have to fetch student smith details, his general information and his obtained marks in each term in each subject with each category.

Following is my database structure

Student

id  |   Roll    |   name    |   class
=====================================
1   |   1       |   smith   |   7

Subject

id  |   title
=============
1   |   Science
2   |   Math

Exam

id  |   title
=================
1   |   First Term 
2   |   Second Term

Marks

id  |   std_id  |   sub_id  |   th  |   pr  |   exm_id
======================================================
1   |   1       |   1       |   60  |   20  |   1
2   |   1       |   2       |   55  |   18  |   1
3   |   1       |   1       |   70  |   23  |   2
4   |   1       |   2       |   61  |   19  |   2

Now I am trying to fetch result as

Name : Smith
class:  7
Progress Report
    First Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18

    Second Term
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

But, following is output what I am getting, I can't fetch result as exam-wise, so all exam share all marks each other

Name : Smith
class:  7
Progress Report
    First Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

    Second Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

I've tried group.by to exam.id, though it fetch result exam-wise which is expecting result, but it repeats same student

Name : Smith
class:  7
Progress Report
    First Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18

Name : Smith
class:  7
Progress Report
    Second Term
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

Here is Mysql and PHP code

$result=$con->prepare(
    "SELECT
        student.id, student.rid, student.name AS name, student.class,

        class.title AS class, 

        GROUP_CONCAT(DISTINCT exam.title) AS exam,
        GROUP_CONCAT(subject.title, '<br/> Th - ', mark.th, ' | PR - ', mark.pr SEPARATOR ',') AS mark

        FROM student
        JOIN class ON class.id = student.class 
        JOIN mark ON mark.std_id = student.id
        JOIN exam ON exam.id = mark.exm_id
        JOIN subject ON subject.id = mark.sub_id

        WHERE student.id=:id
        GROUP BY student.id;" //If exam.id is added here, it works but repeats student
) or die($con->error);

$result->bindParam(':id',$_POST['std']);
$result->execute();
while($row=$result->fetch(PDO::FETCH_ASSOC)){
    $name=$row['name'];
    $class=$row['class'];
    $exm_array = explode(',',$row['exam']);
    $mrk_array = explode(',',$row['mark']); 
    echo "
    Name: $name<br/>
    Class: $class<br/>
    Progress Report<br/>";

    // I think there are other fine alternative way instead of foreach

    foreach(array_values($exm_array) as $i => $exam){
        echo "<span class='col100'>".$exam."<br/>Mark:</span>";
        foreach(array_values($mrk_array) as $i => $mark){
            echo "<span class='col100'> ".$mark."</span>";
        }
    }
}

Raw output

Answer 1

This would be my best guess how to implement it.

1. Load marks into PHP array
2. Loop through students with modified query (only one GROUP_CONCAT())
3. Display the name of the student
4. Get the right array of terms for student it from preloaded data
5. Loop through the array to display each term

First query, load all marks:

SELECT
    student.id,
    exam.title AS exam,
    GROUP_CONCAT(subject.title, '<br/> Th - ', mark.th, ' | PR - ', mark.pr SEPARATOR ',') AS mark
FROM student
    JOIN mark ON mark.std_id = student.id
    JOIN exam ON exam.id = mark.exm_id
    JOIN subject ON subject.id = mark.sub_id
GROUP BY
    student.id,
    exam.title

And store it in PHP array like you have $marks_array

Then loop through students:

SELECT
    student.id,
    student.rid,
    student.name AS 'name',
    student.class,
    class.title AS 'class_title'
FROM student
    JOIN class ON class.id = student.class

And inside the loop call the right records to loop again:

$marks_array[<student_id>] - this would be an array that has two values (one for each exam)

Have a try and let me know if it makes sense..