2

For example, 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"] ?>

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.

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:

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:

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
  • To clarify this question a little could you post your table definitions. I am not sure hwo the tables relate. You have a ID in both tables which I assume is the primary key. But from what I can see you are putting you city names into the comments table as the foreign key to cities? If you can show me the table table definition I would be able to help out. – Namphibian Apr 16 '12 at 10:32
  • Why, I just wrote a really lengthy question and answer that covers this exact [sort of question](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) which I hope will help to clarify how joins work on tables and how to get information from multiple tables in your database! – Fluffeh Sep 18 '12 at 14:09
  • Table `citys` and file `citys.php` are misspelled. It should be [`cities`](https://en.wiktionary.org/wiki/city#Noun) and [`cities.php`](https://en.wiktionary.org/wiki/city#Noun), respectively. – Peter Mortensen Jul 06 '19 at 23:51

2 Answers2

8

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

Vipin Jain
  • 1,382
  • 1
  • 10
  • 19
  • The query works but there is one more problem: If the table "comments" has 3 rows this code shows only the last two but not the first:
    "; echo $row['name']; echo "
    "; echo $row['comment']; } ?> And if i try
    it only shows the first row.     –  Apr 16 '12 at 11:38
  • maybe you should use 2 queries for the above as joins will make the program take more than the required memory. So for better option go for multiple queries. i.e. for both city table and comment table. – Vipin Jain Apr 16 '12 at 11:50
  • If i use this

    "; echo $row['naam']; echo "
    "; echo $row['comment']; } ?> it shows all comments in the right order. I don't know if it's the correct way but it works for me! Thx for the help.     –  Apr 16 '12 at 11:58
  • I have one more question regarding the query. It seems like the field "city" isn't loaded after the query Example: gives the country, gives nothing. –  Apr 16 '12 at 15:03
  • just use var_dump($row); To find out what values are fetched by the query. and post the results here so that i can analyze what is happening – Vipin Jain Apr 17 '12 at 05:13
  • I found the problem myself with var_dump($row);. Thanks for all the help! :D –  Apr 17 '12 at 08:01
  • hmmm, still a problem. If there is a comment about a city everything works good. But if the Comments table has no record about a city this happens, note the NULL after Amsterdam. Array 0 => string '1' (length=1) 'id' => null 1 => string 'Amsterdam' (length=9) 'city' => null 2 => string 'NL' (length=2) 'country' => string 'NL' (length=2) 3 => string '1500000' (length=7) 'population' => string '1500000' (length=7) 4 => null 5 => null 6 => null 'name' => null 7 => null 'comment' => null And then gives nothing –  Apr 17 '12 at 10:07
  • This Happens in case of inner join but how could this happens in Left Join. Actually left join does gets the record if there is no record matching in the 2nd table please Visit: http://www.w3schools.com/sql/sql_join.asp – Vipin Jain Apr 17 '12 at 11:54
  • one more thing var_dump the query also and check in the phpmyadmin console – Vipin Jain Apr 17 '12 at 11:55
4
select * from citys 
left join comments on comments.city = citys.city 
where citys.id=$id
Ali Demirci
  • 326
  • 2
  • 11
  • You may condider using `LEFT OUTER JOIN`. This helps you not get error if it couldn't find a match row. –  Apr 16 '12 at 10:33