PHP上的内部联接 - Inner join on PHP

Inner join on PHP

本文关键字：内部 PHP | 更新日期: 2024-04-06

我有两张表（比赛、团队）。在比赛表中，我有id，team1，team2（其中team1和team2是球队的id）。在团队表中，我有id和name。。如何在php中打印比赛，但显示球队的名称而不是他们的ID？

我的代码（仅打印teamid与teamid）：

<?php
    require_once 'conf/conf.php';
    require_once 'functions.php';
    $query = mysql_query("SELECT time1 as t1id, time2 as t2id
                        FROM matches
                        INNER JOIN teams
                        ON
                        teams.id = matches.time1
                        ");
    //echo mysql_num_rows($query);
    while ($row = mysql_fetch_assoc($query)) 
    {
        $t1 = $row["t1id"];
        $t2 = $row["t2id"];     
        echo ($t1 . " vs. " . $t2 . "<br>");
    }
?>

您可以这样做（假设teams表有一个name列）：

<?php
    require_once 'conf/conf.php';
    require_once 'functions.php';
    $query = mysql_query("SELECT time1 as t1id,
                                 time2 as t2id,
                                 t1.name AS t1name,
                                 t2.name AS t2name
                        FROM matches
                        INNER JOIN teams AS t1
                        ON t1.id = matches.time1
                        INNER JOIN teams AS t2
                        ON t2.id = matches.time2");
    //echo mysql_num_rows($query);
    while ($row = mysql_fetch_assoc($query)) 
    {
        $t1 = $row["t1name"];
        $t2 = $row["t2name"];     
        echo ($t1 . " vs. " . $t2 . "<br>");
    }
?>

尝试这个

require_once 'conf/conf.php';
require_once 'functions.php';
$query = mysql_query("SELECT teams1.id as t1id,teams1.name as name1,  teams2.id as t2id,  teams2.name as name2
                    FROM matches
                    INNER JOIN teams as teams1 ON teams1.id = matches.time1
                    INNER JOIN teams as teams2 ON teams2.id = matches.time2
                    ");
//echo mysql_num_rows($query);
while ($row = mysql_fetch_assoc($query)) 
{
    $t1 = $row["name1"];
    $t2 = $row["name2"];     
    echo ($t1 . " vs. " . $t2 . "<br>");
}

正如我在对您的问题的评论中所说，加入表格两次：

SELECT t1.name, t2.name
FROM matches.m
INNER JOIN teams AS t1 ON (t1.id = m.team1)
INNER JOIN teams AS t2 ON (t2.id = m.team2)