我知道这个问题可能会被问几次,但在某种程度上,我无法理解,因此我要问这个问题。
以下是问题所在
我有三张桌子,它们彼此有关系,就像多到多的关系一样
表
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 | | | |
+-----------+ +-------------+ +---------------+我必须使用这三张带有连接表的表来提取数据。
连接表(连接表)
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 |
+-----------------------+ +---------------------------+使用mysql和php
有什么方法可以用一个查询来提取这个数据吗?
例如
我得把数据
$student = 'Vikram';所以我想要的数据就像这个数组
$result = [
'name' => 'Vikram',
'subjects_teacher' => [
[
'subject' => 'Math',
'teacher' => 'Ajay'
],
[
'subject' => 'Physics',
'teacher' => 'Ajay'
],
[
'subject' => 'Chemistry',
'teacher' => 'Manoj'
],
[
'subject' => 'Biology',
'teacher' => 'Shiv'
]
]
];关系Dig

因此,请在本例中帮助我,因为我已经看过许多教程,并搜索了它,但没有得到这个。
发布于 2017-01-03 00:37:31
试试看,这也许能帮助你理解
优先逼近
$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);第二种方法“不推荐”
$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);发布于 2017-01-02 23:20:10
我还没有对此进行测试,我也不确定模式结构对于联接来说是否非常容易,但是类似于
来澄清这是怎么回事。我们从一个表中选择,并使用一个公共列创建一个“连接”到另一个表。然后,我们可以使用连接表中的值。
所以我们第一次加入
left join Student_Subject on Student.id = Student_Subject.student_id使用id列(从学生)和student_id列(从Student_Subject )连接student_id。
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'https://stackoverflow.com/questions/41434283
复制相似问题