Maxcim Maxcim - 4 months ago 9
PHP Question

Join two mysql tables with php

For example I have created 2 pages and 2 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"] ?>


Until here everything is showing up and working fine.

But what I want to accomplish is also 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);
?>


But nothing works.

Anyone who can help?

Edit!

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

Query:

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


If the table 'comments' has 3 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.

Answer

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

Comments