php - 如何将Mysql中的3个表连接起来形成一个网格？

Question

我一直在尝试所有可能的技巧来完成我正在寻找的东西。我不是菜鸟，但我坚持这一点。我正在尝试从 3 个表中创建数据的“网格”。我希望它看起来像这样:

  Assignments | Assgn 1 | Assgn 2 | Assgn 3 | Assgn 4 | Final Grade
----------------------------------------------------------------------
   Username 1 |  Grade  |  Grade  |  Grade  |  Grade  | Total  
----------------------------------------------------------------------
   Username 2 |  Grade  |  Grade  |  Grade  |  Grade  | Total  
----------------------------------------------------------------------
   Username 3 |  Grade  |  Grade  |  Grade  |  Grade  | Total  

成员的第一个表列:

Memberid, Username

分配表:

 id, title

成绩保存在 subassign 中:

id,assign,student,grade

assign是assignments表中assignment的id号student 是成员表中的 Memberid。

这几天我一直在研究这个问题。

给出一些结果的是这个查询

 $result = mysql_query("SELECT assignments.title,subassign.grade, members.Username FROM assignments, subassign, members WHERE members.Memberid = subassign.student and assignments.id = subassign.assign ",$connect);

它正确地链接了信息，但我无法让它显示我想要的记录。它只是在 php 中创建一个新行。有人可以帮忙吗？我正要把我的电脑扔出窗外。

Answer 1

您可以像这样使用连接:

SELECT
    members.title AS 'Assignments',
    assignment1.grade AS 'Assign1',
    assignment2.grade AS 'Assign2',
    assignment3.grade AS 'Assign3',
    assignment3.grade AS 'Assign4'
FROM members
INNER JOIN subassign AS assignment1 ON assignment1.person = members.MemberId AND assign = 1
INNER JOIN subassign AS assignment2 ON assignment2.person = members.MemberId AND assign = 2
INNER JOIN subassign AS assignment3 ON assignment3.person = members.MemberId AND assign = 3
INNER JOIN subassign AS assignment4 ON assignment4.person = members.MemberId AND assign = 4

请注意，如果您想根据表中的 assignments 返回动态列(我假设您这样做)，最好的办法是动态生成与上述类似的查询。请参阅下面的一些非常粗略的示例代码，这些代码将生成您想要的动态查询(这完全未经测试):

$results = mysql_query("SELECT id, title FROM assignments");
$query = "SELECT
    members.title AS 'Assignments',";

$joins = "";

while($row = mysql_fetch_assoc($results))
{
    $name = 'assignment' . $row['id'];
    $query .= "\r\n" . $name . ".grade AS '" . $row['title'] . "',";
    $joins .= "\r\nLEFT JOIN subassign AS " . $name . " ON " . $name . ".person = members.MemberId AND " . $name . ".assign = " . $row['id'];
}

$query = substr($query, 0, -1) . " FROM members" . $joins;
$result = mysql_query($query);