用PHP连接两个MySQL表 [英] Join two MySQL tables with PHP

问题描述

例如，我创建了两个页面和两个MySQL表.

For example, I have created two pages and two MySQL tables.

Index.php& citys.php

城市

 ID     City       Country  Population
 --------------------------------------
 1      Amsterdam     NL     1500000
 2      Rotterdam     NL     900000
 3      Dusseldorf    DE     1800000

评论

ID   City        Name   Comment
---------------------------------
 1   Dusseldorf  Jack   Great city!
 2   Dusseldorf  John   Beautiful
 3   Rotterdam   Emy    Love it

此刻，我仅使用这样的表citys:

At the moment I only use the table citys like this:

index.php通过以下方式链接到citys.php:

index.php linking to citys.php with:

<a href='citys.php?cmd=menu&id=";echo $row['id'];echo "'>

citys.php使用以下代码显示来自MySQL的数据:

And citys.php use this code to show the data from 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.

但是我也想在第2页上显示注释.因此，必须对查询进行编辑以从表comments中获取正确的数据.

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.

我尝试从互联网编辑我自己编辑过的其他示例，例如:

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);
?>

但是什么都行不通.

我该如何解决?

VIPIN JAIN的答案查询有效，但是仍然存在一个问题:

The query from VIPIN JAIN's answer works, but there is one problem left:

查询:

$sql = "SELECT * FROM citys LEFT JOIN comments ON comments.city=citys.city WHERE citys.id=$id";

如果表"comments"具有三行，则此代码仅显示最后两行，而不显示第一行:

If the table 'comments' has three rows, this code shows only the last two but not the first:

<?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.

推荐答案

使用此查询

$sql = "SELECT * FROM citys LEFT JOIN comments ON comments.city=citys.city WHERE citys.id=$id";

使用leftjoin进行此类工作

Use leftjoin for this type of work

这篇关于用PHP连接两个MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！