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

Can you show the output of the query, before you start doing anything to it with PHP? — Moseleyi
– Moseleyi, Commented Aug 31, 2018 at 15:28
@Moseleyi, I am not sure, what you mean, but I've edited post, added snapshot while running query in mysql, Please check it once — Dipak
– Dipak, Commented Aug 31, 2018 at 16:23
Okay I see what you're trying to do.. now my question is why are you trying to do such complicated thing in SQL. You still need to loop through it so why GROUP_CONCAT if you're "exploding" it. You might as well return a nice associative array and then deal with displaying it there. The query is fine and works as it should.. — Moseleyi
– Moseleyi, Commented Aug 31, 2018 at 18:23

Moseleyi · Accepted Answer · 2018-08-31 19:19:16Z

1

This would be my best guess how to implement it.

Load marks into PHP array
Loop through students with modified query (only one GROUP_CONCAT())
Display the name of the student
Get the right array of terms for student it from preloaded data
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..

edited Aug 31, 2018 at 19:19

answered Aug 31, 2018 at 18:25

Moseleyi

2,9754 gold badges34 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dipak Over a year ago

@Moselyi, Thanks Man !!, this way works, but as your suggestion in comment, I found I was doing wrong using huge GROUP_CONCAT and making complicated query, So I ignore GROUP_CONCAT and GROUP BY, and switch to while loop and if condition with join, I hadn't thought this way query would be faster and optimized

Collectives™ on Stack Overflow

multiple foreach loop with mysql join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related