首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何使用php和mysql使用多到多的关系来提取数据

问如何使用php和mysql使用多到多的关系来提取数据
EN

Stack Overflow用户
提问于 2017-01-02 23:02:37
回答 2查看 887关注 0票数 2

我知道这个问题可能会被问几次,但在某种程度上,我无法理解,因此我要问这个问题。

以下是问题所在

我有三张桌子,它们彼此有关系,就像多到多的关系一样

表

代码语言:javascript
复制
   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     |       |   |           |
+-----------+     +-------------+       +---------------+

我必须使用这三张带有连接表的表来提取数据。

连接表(连接表)

代码语言:javascript
复制
      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

有什么方法可以用一个查询来提取这个数据吗?

例如

我得把数据

代码语言:javascript
复制
$student  = 'Vikram';

所以我想要的数据就像这个数组

代码语言:javascript
复制
$result = [
    'name' => 'Vikram',
    'subjects_teacher' => [
        [
          'subject' => 'Math',
          'teacher' => 'Ajay'
        ],

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

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

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

关系Dig

​

​

因此,请在本例中帮助我,因为我已经看过许多教程,并搜索了它,但没有得到这个。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2017-01-03 00:37:31

试试看,这也许能帮助你理解

优先逼近

代码语言:javascript
复制
$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);

第二种方法“不推荐”

代码语言:javascript
复制
$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);
票数 1
EN

Stack Overflow用户

发布于 2017-01-02 23:20:10

我还没有对此进行测试,我也不确定模式结构对于联接来说是否非常容易,但是类似于

来澄清这是怎么回事。我们从一个表中选择,并使用一个公共列创建一个“连接”到另一个表。然后,我们可以使用连接表中的值。

所以我们第一次加入

代码语言:javascript
复制
left join Student_Subject on Student.id = Student_Subject.student_id

使用id列(从学生)和student_id列(从Student_Subject )连接student_id。

Student.id = Student_Subject.student_id

代码语言:javascript
复制
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'
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/41434283

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档