用 PHP 连接两个 MySQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10172411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join two MySQL tables with PHP
提问by Maxcim
For example, I have created two pages and two MySQL tables.
例如，我创建了两个页面和两个 MySQL 表。
Index.php& citys.php
Index.php& citys.php
citys
城市
ID City Country Population
--------------------------------------
1 Amsterdam NL 1500000
2 Rotterdam NL 900000
3 Dusseldorf DE 1800000
comments
注释
ID City Name Comment
---------------------------------
1 Dusseldorf Hyman Great city!
2 Dusseldorf John Beautiful
3 Rotterdam Emy Love it
At the moment I only use the table cityslike this:
目前我只使用这样的表格citys：
index.phplinking to citys.phpwith:
index.php链接到citys.php：
<a href='citys.php?cmd=menu&id=";echo $row['id'];echo "'>
And citys.phpuse this code to show the data from MySQL:
并citys.php使用此代码显示来自 MySQL 的数据：
<?php
include "connect.php";
if(!isset($cmd))
{
if($_GET["cmd"]=="menu" || $_POST["cmd"]=="menu")
{
if (!isset($_POST["submit"]))
{
$id = $_GET["id"];
$sql = "SELECT * FROM citys WHERE id=$id";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
?>
<?php echo $row["City"] ?>
<br><br>
<?php echo $row["Country"] ?>
<br><br>
<?php echo $row["Population"] ?>
Until here everything is showing up and working fine.
直到这里一切都显示出来并且工作正常。
But I also want to show the comments on page 2. So the query has to be edited to also get the right data from the table comments.
但我也想显示第 2 页上的注释。因此必须编辑查询才能从表中获取正确的数据comments。
I tried different examples from internet that I have edited myself like:
我尝试了来自互联网的不同示例，这些示例是我自己编辑的：
<?php
include "connect.php";
if(!isset($cmd))
{
if($_GET["cmd"]=="menu" || $_POST["cmd"]=="menu")
{
if (!isset($_POST["submit"]))
{
$id = $_GET["id"];
$sql = "SELECT citys.*, comments.* FROM citys, comments WHERE citys.id=$id AND comments.city=citys.city";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
?>
But nothing works.
但没有任何作用。
How can I fix this?
我怎样才能解决这个问题？
The query from VIPIN JAIN's answer works, but there is one problem left:
VIPIN JAIN 的回答中的查询有效，但还剩下一个问题：
Query:
询问：
$sql = "SELECT * FROM citys LEFT JOIN comments ON comments.city=citys.city WHERE citys.id=$id";
If the table 'comments' has three rows, this code shows only the last two but not the first:
如果表 'comments' 有三行，则此代码仅显示最后两行而不显示第一行：
<?php
while($row = mysql_fetch_array($result)) {
echo "<br><br>";
echo $row['name'];
echo "<br>";
echo $row['comment'];
}
?>
And if I try this it only shows the first row.
如果我尝试这个，它只显示第一行。
<?php echo $row["name"] ?>
<br>
<?php echo $row["comment"] ?>
I don't know why the first record is left away in the loop.
我不知道为什么第一条记录留在循环中。
回答by Vipin Jain
Use this query
使用此查询
$sql = "SELECT * FROM citys LEFT JOIN comments ON comments.city=citys.city WHERE citys.id=$id";
Use leftjoin for this type of work
将 leftjoin 用于此类工作
回答by Ali Demirci
select * from citys
left join comments on comments.city = citys.city
where citys.id=$id
