title: Join two MySQL tables with PHP
body: <p>For example, I have created two pages and two MySQL tables.</p>
  
  <p><code>Index.php</code> &amp; <code>citys.php</code></p>
  
  <p><strong>citys</strong></p>
  
  <pre><code> ID     City       Country  Population
   --------------------------------------
   1      Amsterdam     NL     1500000
   2      Rotterdam     NL     900000
   3      Dusseldorf    DE     1800000
  </code></pre>
  
  <p><strong>comments</strong></p>
  
  <pre><code>ID   City        Name   Comment
  ---------------------------------
   1   Dusseldorf  Jack   Great city!
   2   Dusseldorf  John   Beautiful
   3   Rotterdam   Emy    Love it
  </code></pre>
  
  <p>At the moment I only use the table <code>citys</code> like this:</p>
  
  <p><code>index.php</code> linking to <code>citys.php</code> with:</p>
  
  <pre><code>&lt;a href='citys.php?cmd=menu&amp;id=";echo $row['id'];echo "'&gt;
  </code></pre>
  
  <p>And <code>citys.php</code> use this code to show the data from MySQL:</p>
  
  <pre><code>&lt;?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);
  ?&gt;
  
  &lt;?php echo $row["City"] ?&gt;
  &lt;br&gt;&lt;br&gt;
  
  &lt;?php echo $row["Country"] ?&gt;
  &lt;br&gt;&lt;br&gt;
  &lt;?php echo $row["Population"] ?&gt;
  </code></pre>
  
  <p>Until here everything is showing up and working fine.</p>
  
  <p>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 <code>comments</code>.</p>
  
  <p>I tried different examples from internet that I have edited myself like:</p>
  
  <pre><code>&lt;?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);
  ?&gt;
  </code></pre>
  
  <p>But nothing works.</p>
  
  <p>How can I fix this?</p>
  
  <hr/>
  
  <p>The query from VIPIN JAIN's answer works, but there is one problem left:</p>
  
  <p>Query:</p>
  
  <pre><code>$sql = "SELECT * FROM citys LEFT JOIN comments ON comments.city=citys.city WHERE citys.id=$id";
  </code></pre>
  
  <p>If the table 'comments' has three rows, this code shows only the last two but not the first:</p>
  
  <pre><code>&lt;?php
      while($row = mysql_fetch_array($result)) {
          echo "&lt;br&gt;&lt;br&gt;";
          echo $row['name'];
          echo "&lt;br&gt;";
          echo $row['comment'];
      }
  ?&gt;
  </code></pre>
  
  <p>And if I try this it only shows the first row.</p>
  
  <pre><code>&lt;?php echo $row["name"] ?&gt;
  &lt;br&gt;
  &lt;?php echo $row["comment"] ?&gt;
  </code></pre>
  
  <p>I don't know why the first record is left away in the loop.</p>