How to pull data using many to many relation using php and mysql

Question

I know this question may be asked few times but in some point i am not be able to understand for that reason i am asking this.

Here is the question

I have three table which has relation between each other like many to many relation

Table

   Teacher            Student                Subject     
+-----------+     +-------------+       +---------------+
| id| name  |     | id| name    |       | id| name      |
+-----------+     +-------------+       +---------------+
| 1 | Ram   |     | 1 | Vikram  |       | 1 | Math      |
| 2 | Ajay  |     | 2 | Sunil   |       | 2 | English   |
| 3 | John  |     | 3 | Mohan   |       | 3 | Physics   |
| 4 | Eric  |     | 4 | Pawan   |       | 4 | Chemistry |
| 5 | Manoj |     | 5 | Deepak  |       | 5 | Biology   |
| 6 | Shiv  |     | 6 | Alex    |       | 6 | Social Sci|
|   |       |     | 7 | Shawn   |       | 7 | Hindi     |
|   |       |     | 8 | Mark    |       | 8 | History   |
|   |       |     | 9 | Joe     |       |   |           |
+-----------+     +-------------+       +---------------+

I have to pull data using those three table with junction table

Connecting Table (Junction Table)

      Teacher_Subject              Student_Subject       
+-----------------------+   +---------------------------+
|teacher_id |subject_id |   | student_id  | subject_id  |           
+-----------------------+   +---------------------------+
|     1     |     7     |   |       1     |     1       |           
|     1     |     8     |   |       1     |     3       |           
|     2     |     1     |   |       1     |     4       |           
|     2     |     3     |   |       1     |     5       |           
|     3     |     2     |   |       2     |     4       |           
|     4     |     6     |   |       2     |     7       |           
|     5     |     4     |   |       3     |     1       |           
|     6     |     5     |   |       3     |     4       |           
|           |           |   |       3     |     5       |           
|           |           |   |       4     |     2       |           
|           |           |   |       4     |     6       |           
+-----------------------+   +---------------------------+

with mysql and php

is there any way to pull this data with a single query

for example

I have to pull data where

$student  = 'Vikram';

so data i want be like this array

$result = [
    'name' => 'Vikram',
    'subjects_teacher' => [
        [
          'subject' => 'Math',
          'teacher' => 'Ajay'
        ],

        [
          'subject' => 'Physics',
          'teacher' => 'Ajay'
        ],

        [
          'subject' => 'Chemistry',
          'teacher' => 'Manoj'
        ],

        [
          'subject' => 'Biology',
          'teacher' => 'Shiv'
        ]
      ]
  ];

Relation Dig

So please help me in this case because i have seen many tutorials and googled it but didn't get this.

Answer 1

Try this may be it might help you to understand

First approach

$sql  = "SELECT
  Student.id, 
  Student.name,
  Teacher.name AS teacher,
  subject.name AS subject
FROM
  Student
LEFT JOIN
  Student_Subject ON Student.id = Student_Subject.student_id
LEFT JOIN
  Teacher_Subject ON Student_Subject.subject_id = Teacher_Subject.subject_id
LEFT JOIN
  Teacher ON Teacher_Subject.teacher_id = Teacher.id
LEFT JOIN subject ON
  Student_Subject.subject_id = subject.id
WHERE
  Student.name = 'Vikram'";

$result = mysqlQuery($sql); // your custom function like using pdo or mysqli

$finalResult = [];
foreach ($result as $key => $value) {

    if (!isset($finalResult[$value['id']]['name'])) {
        $finalResult[$value['id']]['name'] = $value['name'];
    }

    $finalResult[$value['id']]['subjects_teacher'][] = [
            "teacher" => $value['teacher'],
            "subject" => $value['subject'],
        ];
}

print_r($finalResult);

second approach "not recommended"

$sql  = "SELECT
  Student.name,
  jt.subjects_teacher
FROM
  Student
LEFT JOIN (
    SELECT 
        ss.student_id,

        CONCAT('[',GROUP_CONCAT(CONCAT('{\"teacher\":\"', t.name,'\",\"subject\":\"', s.name, '\"}') ),']') AS subjects_teacher

    FROM `Student_Subject` ss 
    LEFT JOIN
      Teacher_Subject ts ON ss.subject_id = ts.subject_id
    LEFT JOIN
      Teacher t ON ts.teacher_id = t.id
    LEFT JOIN 
        subject s ON ss.subject_id = s.id
    GROUP BY ss.student_id
) jt ON jt.student_id = Student.id

WHERE
  Student.name = 'Vikram'";

$result = mysqlQuery($sql); // your custom function like using pdo or mysqli
foreach ($result as $key => &$value) {
    $value['subjects_teacher'] = json_decode($value['subjects_teacher'], true);
}

print_r($result);

Answer 2

I haven't test this and I'm not sure that the schema structure is very easy for joins but something like this

To clarify what's going on here. We select from one table and use a common column to create a 'join' to another. We can then use the values from that joined table.

So our first join

left join Student_Subject on Student.id = Student_Subject.student_id

Joins Student_Subject using the id column from Student and the student_id column from Student_Subject.

Student.id = Student_Subject.student_id

select 
Student.name, 
Teacher.name,
Subject.name
from Student
left join Student_Subject on Student.id = Student_Subject.student_id
left join Teacher_Subject on Student_Subject.subject_id = Teacher_Subject.subject_id
left join Teacher on Teacher_Subject.teacher_id = Teacher.id
left join Subject on Student_Subject.id = Subject.id
where Student.name = 'Vikram'