Join two MySQL tables with PHP

Question

I have created two pages and two MySQL tables.

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  Jack   Great city!
 2   Dusseldorf  John   Beautiful
 3   Rotterdam   Emy    Love it

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

index.php linking to citys.php with:

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

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"] ?>

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

Answer 1

Use leftjoin for this type of work. I hope this helps you.