连接两个表并遍历数据 [英] Joining two tables and iterating through data

问题描述

我一直在使用stackoverflow来帮助我，但现在我很困惑-希望我能就此问题获得一些指导：

I constantly am using stackoverflow to help me but right now I am stumped - hopefully I can get some guidance on this issue:

我有两个表，其中一个包含NFL将另外一支包含NFL球队的球队分组。它们通过divide_id字段（Divisions表上的主键，Teams表上的外键）连接。

I have two tables, one containing NFL Divisions the other containing NFL Teams. They are connected via the division_id field (primary key on Divisions table, foreign key on the Teams table).

我想做的是在查询中联接表并按部门输出团队。我可以使用两个单独的查询/循环在PHP中完成此操作，但我想知道如何/是否可以通过将两个表连接到一个查询中来完成此操作。

What I would like to do is join the tables in a query and output the Teams by Division. I can accomplish this in PHP with two separate queries/loops but I want to know how/if I can accomplish this by joining the two tables in a single query instead.

这是我得到的：

$query = "SELECT Divisions.division_id, Divisions.conference, Divisions.name, 
    Teams.name FROM Divisions INNER JOIN Teams ON 
    Teams.division_id = Divisions.division_id ORDER BY Divisions.division_id";

if( $result = $db->query($query) ){
  while( $row = $result->fetch_object() ){
    // here is where I'm stuck
  }
}

我在这里得到的最终输出类似于：

I'm trying to get the final output to look similar to:

AFC North

克利夫兰布朗队

匹兹堡钢人队

巴尔的摩乌鸦队

辛辛那提猛虎队

AFC North
Cleveland Browns
Pittsburgh Steelers
Baltimore Ravens
Cincinnati Bengals

亚足联东部

布法罗条例草案

迈阿密海豚

等。

AFC East
Buffalo Bills
Miami Dolphins
etc..

在此先感谢所有可以帮助您的人我把这个出去！两年多来我还没有做任何编程，只是回到它上面。

Major thank you in advance to anyone who can help me out with this! I haven't done any programming in over 2 years and just getting back into it.

推荐答案

在行中循环并输出一个每当 division_id 更改时，将添加新的标头：

Loop through the rows and output a new header whenever the division_id changes:

$query = "SELECT Divisions.division_id, Divisions.conference, Divisions.name, 
    Teams.name AS teamName FROM Divisions INNER JOIN Teams ON 
    Teams.division_id = Divisions.division_id ORDER BY Divisions.division_id";

if ($result = $db->query($query)) {
    $division = "";
    while ($row = $result->fetch_object()) {
        if ($division != $row["division_id"])
        {
            $division = $row["division_id"];
            echo "<br /><h2>{$row["conference"]}</h2>";
        }

        echo "{$row["teamName"]}<br />";
    }
}

这篇关于连接两个表并遍历数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！