php - 试图在一个简单的php系统中显示具有一对多关系的mysql数据？

Question

我已将 FirstPositionID/SecondPositionID 与职位的 PositionID(PRIMARY KEY) 连接起来。每个positionID有一个职位名称。由于我有 2 个由位置 id 设置的球员位置，php 不应该打印我设置的每个 id 的位置名称吗？ 当我打印 PositionName 时，它​​会打印我在位置表中插入的所有位置。这是我的 table

CREATE TABLE `players` (
`PlayerID` int(10) UNSIGNED NOT NULL,
`PlayerName` varchar(255) NOT NULL,
`CountryID` varchar(255) NOT NULL,
`FirstPositionID` int(11) UNSIGNED NOT NULL,
`SecondPositionID` int(10) UNSIGNED NOT NULL,
`Overall` int(11) UNSIGNED NOT NULL,
`Defence` int(11) NOT NULL,
`Speed` int(11) NOT NULL,
`Rebound` int(11) NOT NULL,
`Stamina` int(11) NOT NULL,
`TeamID` int(11) NOT NULL,
`Shooting` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

CREATE TABLE `positions` (
`PositionID` int(10) UNSIGNED NOT NULL,
`PositionName` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;





//-----------php-----------------------

    $sql= "SELECT * from players,positions";
    $reg = mysqli_query($con,$sql);
    if(mysqli_num_rows($reg) > 0)
    {
        while($row = mysqli_fetch_assoc($reg))
        {
            echo "</br>Player:".$row['PlayerName']." Overall:".$row['Overall']." Position: ".$row['PositionName'];
        }

    }
    else 
    {
        echo "SQL error at: </br> ".$register."SYNTAX:</br>".mysqli_error($con)."</br>";
    }

结果:

Player:Agrabanis Overall:64 Position: Point Guard

Player:Athineou Overall:64 Position: Point Guard

Player:Agrabanis Overall:64 Position: Shooting Guard

Player:Athineou Overall:64 Position: Shooting Guard

Player:Agrabanis Overall:64 Position: Small Forward

Player:Athineou Overall:64 Position: Small Forward

Player:Agrabanis Overall:64 Position: Power Forward

Player:Athineou Overall:64 Position: Power Forward

Player:Agrabanis Overall:64 Position: Center

Player:Athineou Overall:64 Position: Center

由于我已设置第一球员位置 ids 5,4 和第二球员位置 ids 2,1 不应该只为第一球员打印 Center,Power Forward(4,5 位置 ids) 和第二球员 Shooting Guard,Point Guard(2 ,1 个位置 ID)

Answer 1

请使用左连接或内连接而不是交叉连接，并将您的SQL字符串更改为类似的内容

SELECT pl.PlayerName, pl.FirstPositionID, pl.Overall,
pl.SecondPositionID, po1.PositionName AS FirstPositionName,
po2.PositionName AS SecondPositionName FROM players pl
LEFT JOIN positions po1 ON(pl.FirstPositionID = po1.PositionID )
LEFT JOIN positions po2 ON (pl.SecondPositionID = po2.PositionID )
LIMIT 0,100

另外，将 while 循环更改为

while($row = mysqli_fetch_assoc($reg)){
  echo "</br>Player:".$row['PlayerName']." Overall:".$row['Overall']." Position1: ".$row['FirstPositionName']." Position2:".$row['SecondPositionName '];
}

我认为这会对你有所帮助......