我已从位置将FirstPositionID/SecondPositionID与PositionID（PRIMARY KEY）连接。每个positionID都有一个位置名称。既然我有两个由位置id设置的玩家位置，php不应该打印我设置的每个id的位置名称吗？当我打印PositionName时，它会打印我在位置表中插入的所有位置。这是我的桌子
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
由于我已经设置了第一个玩家位置id 5,4和第二个玩家位置ID 2,1，所以不应该只打印第一个玩家和第二个人的Center，Power Forward（4,5位置id）Shooting Guard，Point Guard（2,1位置id）
请使用left join或inner join而不是交叉联接，并将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($row = mysqli_fetch_assoc($reg)){
echo "</br>Player:".$row['PlayerName']." Overall:".$row['Overall']." Position1: ".$row['FirstPositionName']." Position2:".$row['SecondPositionName '];
}
我认为这将帮助你。。。