Jump to content

Nested query or inner join?


epseix1

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/277604-nested-query-or-inner-join/
Share on other sites

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";
}

?>
 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.