Nested query or inner join?

[epseix1]

So I have a massive events table and I want to display them by first grouping the results by country, followed by area etc. etc.

 

Until now I have used queries within queries using the result of the previous query loop in the where of the second query... (Still with me?)

 

$query1 = "SELECT * FROM calendar GROUP BY country ORDER BY country";
$result1 = mysql_query( $query ) or die( mysql_error() );
while ( $row = mysql_fetch_array( $result ) ) {
$country = $row["country"];
echo $country;

$query2 = 'SELECT * FROM calendar WHERE country="' . $country . '" GROUP BY area ORDER BY area';
$result2 = mysql_query( $query2 ) or die( mysql_error() );
while ( $row = mysql_fetch_array( $result2 ) ) {
echo $row["area"];
}
}

I'm thinking there must be an easier more efficient way of doing this via nested queries or inner join so that everything is in just one query etc.

 

Can somebody advise?

[gizmola]

Yes, just group by country, area.

 

SELECT * FROM calendar
GROUP BY country, area
ORDER BY country, area

You'll get one result set, with one row per country/area combination.

[epseix1]

Well yes, but then I get a repeated echo of the country for every row of area...

 

I'm after a result like:

 

England

-Essex

-Gtr. Manchester

-Sussex

 

... As opposed to...

 

England-Essex

England-Gtr. Manchester

England-Sussex

 

... Etc.

Edited May 3, 2013 by epseix1

[Psycho]

Never run queries in loops.

 

 

Well yes, but then I get a repeated echo of the country for every row of area...

 

You handle that in the output logic in PHP. Example:

 

<?php

$query = "SELECT country, area
          FROM calendar
          GROUP BY country, area
          ORDER BY country, area";
$result = mysql_query($query);

$currentCountry = false;
while($row = mysql_fetch_assoc($result))
{
    //Check of this is a different country than the last record
    if($currentCountry != $row['country'])
    {
        $currentCountry = $row['country'];
        echo "{$currentCountry}<br>\n";
    }

    echo " - {$row['area']}<br>\n";
}

?>
 

Edited May 3, 2013 by Psycho