MySQL, PHP Inner join and show results in different tables html

Question

I have a small problem. I have two tables in a database:

[place]                        [client]

id     country                 id     name     id_place
--------------                 ------------------------
1      Canada                  1      Mike     1
2      USA                     2      Susan    1
3      China                   3      Juan     3
                               4      Nelly    2
                               5      Kevin    3

Using a SQL query:

SELECT 
    place.country, cliente.name 
FROM 
    place, client 
INNER JOIN 
    client ON place.id = client.id_place

and I show the results using:

while ($list = mysql_fetch_array($query))
{
     echo $list['contry']." | ".$list['name'];
}

And the results are:

Canada | Mike
Canada | Susan
USA    | Nelly
China  | Juan
China  | Kevin

So far so good. My problem is I want to show the results as follows:

<h1> Canada: <h1>
<h3> -Mike <h3>
<h3> -Susan <h3>

<h1> USA: <h1>
<h3> -Nelly <h3>

<h1> China: <h1>
<h3> -Juan <h3>
<h3> -Kevin <h3>

How I can do this? Help me please... Thanks!

marc_s · Accepted Answer · 2015-08-07 11:43:36Z

1

This should work:

$h1 = NULL;

while ( $list = mysql_fetch_array($query) ) {
    if($h1 <> $list['contry']) {
        echo "<h1>" . $list['contry'] . ":</h1>\n";

        $h1 = $list['country'];
    }

    echo "<h3>- " . $list['name'] . "</h3>\n";
}

$h1 is initialized as NULL. When the loop begins, it checks whether $h1 is equal to the $list['country']. If it is not, it will output the header for the country and set them equivalent so that it is not repeated.

You should also stop using mysql_ functions as they are being deprecated and use mysqli_ or PDO instead.

edited Aug 7, 2015 at 11:43

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 29, 2012 at 22:01

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL, PHP Inner join and show results in different tables html

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related