epseix1 Posted May 3, 2013 Share Posted May 3, 2013 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 More sharing options...
gizmola Posted May 3, 2013 Share Posted May 3, 2013 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. Link to comment https://forums.phpfreaks.com/topic/277604-nested-query-or-inner-join/#findComment-1428040 Share on other sites More sharing options...
epseix1 Posted May 3, 2013 Author Share Posted May 3, 2013 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. Link to comment https://forums.phpfreaks.com/topic/277604-nested-query-or-inner-join/#findComment-1428050 Share on other sites More sharing options...
Psycho Posted May 3, 2013 Share Posted May 3, 2013 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"; } ?> Link to comment https://forums.phpfreaks.com/topic/277604-nested-query-or-inner-join/#findComment-1428058 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.