php - 试图限制在 mysql 查询/数组中返回的数据

Question

我有这两张表...

时间表(gameid，homeid，awayid，weekno，seasonno)

团队(团队成员、位置、昵称)

下面的这个 mysql 查询获取了所有 32 个团队的日程安排信息......

        $sql = "SELECT
        h.nickname AS home,
        a.nickname AS away,
        h.teamid AS homeid,
        a.teamid AS awayid,
        s.weekno
    FROM schedule s
        INNER JOIN teams h ON s.homeid = h.teamid
        LEFT JOIN teams a ON s.awayid = a.teamid
    WHERE s.seasonno =2014";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else { 
// Placeholder for data
    $data = array();
    while($row = mysqli_fetch_assoc($schedule)) {
        if ($row['away'] == "") {$row['away']="BYE";}
        $data[$row['homeid']][$row['weekno']] = $row['away'];
        $data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
    }
}

但是，我只想获取一个特定团队的信息，该信息存储在 $teamid 变量中。这应该很容易吧？我已经尝试了很多东西，包括下面的这个(我在其中添加了一个 AND 语句)，但是这个仍然输出太多......

$sql = "SELECT
    h.nickname AS home,
    a.nickname AS away,
    h.teamid AS homeid,
    a.teamid AS awayid,
    s.weekno
FROM schedule s
    INNER JOIN teams h ON s.homeid = h.teamid 
    LEFT JOIN teams a ON s.awayid = a.teamid 
WHERE s.seasonno =2014
AND h.teamid=$teamid OR a.teamid=$teamid";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else { 
// Placeholder for data
    $data = array();
    while($row = mysqli_fetch_assoc($schedule)) {
        if ($row['away'] == "") {$row['away']="BYE";}
        $data[$row['homeid']][$row['weekno']] = $row['away'];
        $data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
    }
}

下面是上面输出的数组。它更好，但我想要的是第二个数组 ([1])，在这个例子中，它有老鹰队的完整时间表。它没有给我太多其他东西，我想我可以忍受它而忽略其他东西，但我宁愿尽可能高效并且只得到我需要的东西......

Array
(
    [24] => Array
        (
            [1] => Eagles
        )

    [1] => Array
        (
            [1] => Jaguars
            [3] => Redskins
            [5] => Rams
            [9] => @ Texans
            [11] => @ Packers
            [13] => @ Cowboys
            [14] => Seahawks
            [17] => @ Giants
            [4] => @ 49ers
            [6] => Giants
            [10] => Panthers
            [15] => Cowboys
            [2] => @ Colts
            [7] => BYE
            [8] => @ Cardinals
            [12] => Titans
            [16] => @ Redskins
        )

    [16] => Array
        (
            [3] => Eagles
            [8] => Eagles
        )

    [23] => Array
        (
            [5] => Eagles
        )

    [10] => Array
        (
            [9] => Eagles
        )

    [4] => Array
        (
            [11] => Eagles
            [1] => Eagles
            [3] => @ Eagles
            [16] => Eagles
        )

    [2] => Array
        (
            [13] => Eagles
            [17] => Eagles
            [15] => @ Eagles
        )

    [9] => Array
        (
            [14] => Eagles
            [6] => Eagles
        )

    [3] => Array
        (
            [17] => Eagles
            [5] => Eagles
            [6] => @ Eagles
        )

    [29] => Array
        (
            [4] => Eagles
        )

    [31] => Array
        (
            [9] => Eagles
        )

    [7] => Array
        (
            [10] => Eagles
            [11] => Eagles
        )

    [6] => Array
        (
            [15] => Eagles
        )

    [27] => Array
        (
            [1] => @ Eagles
        )

    [28] => Array
        (
            [2] => Eagles
        )

    [14] => Array
        (
            [4] => Eagles
        )

    [15] => Array
        (
            [5] => @ Eagles
        )

    [] => Array
        (
            [7] => @ Eagles
        )

    [25] => Array
        (
            [9] => Eagles
        )

    [11] => Array
        (
            [10] => @ Eagles
        )

    [26] => Array
        (
            [12] => @ Eagles
        )

    [13] => Array
        (
            [14] => @ Eagles
        )

)

更新:我为每个 Abhik 添加了括号(谢谢!)，它有一点帮助，但仍然提供了太多，即......

Array
(
    [1] => Array
        (
            [1] => Jaguars
            [2] => @ Colts
            [3] => Redskins
            [4] => @ 49ers
            [5] => Rams
            [6] => Giants
            [7] => BYE
            [8] => @ Cardinals
            [9] => @ Texans
            [10] => Panthers
            [11] => @ Packers
            [12] => Titans
            [13] => @ Cowboys
            [14] => Seahawks
            [15] => Cowboys
            [16] => @ Redskins
            [17] => @ Giants
        )

    [27] => Array
        (
            [1] => @ Eagles
        )

    [28] => Array
        (
            [2] => Eagles
        )

    [4] => Array
        (
            [3] => @ Eagles
            [16] => Eagles
        )

    [14] => Array
        (
            [4] => Eagles
        )

    [15] => Array
        (
            [5] => @ Eagles
        )

    [3] => Array
        (
            [6] => @ Eagles
            [17] => Eagles
        )

    [] => Array
        (
            [7] => @ Eagles
        )

    [16] => Array
        (
            [8] => Eagles
        )

    [25] => Array
        (
            [9] => Eagles
        )

    [11] => Array
        (
            [10] => @ Eagles
        )

    [7] => Array
        (
            [11] => Eagles
        )

    [26] => Array
        (
            [12] => @ Eagles
        )

    [2] => Array
        (
            [13] => Eagles
            [15] => @ Eagles
        )

    [13] => Array
        (
            [14] => @ Eagles
        )

)

Answer 1

需要用括号分隔条件

WHERE s.seasonno =2014
AND h.teamid=$teamid OR a.teamid=$teamid

到

WHERE 
s.seasonno =2014
AND 
(h.teamid=$teamid OR a.teamid=$teamid)