displaying query results with titles

[fife]

Hi Guys

 

Ok i have a query which when a user types into a search box a city name the search box auto fills with other city names like the one they have typed 

 

The query for these city names is as follows

 

 $query_rs_city = "SELECT city.*, countries.countryID, countries.country FROM city INNER JOIN countries ON countries.countryID = city.cityCountryUK ORDER BY city.cityNameUK ASC";$rs_city = mysql_query($query_rs_city, $dbconnect) or die(mysql_error());    //and the record set do{    echo $row['cityname'];}while($row = mysql_fetch_assoc($rs_city));  

 

As you can see the query uses a join so i can get the country name too (excuse the awful field names from the previous designer).   Now when the results are pulled back they come in the order below on the results page

 

 

liverpool, uk

london, uk

berlin, germany

paris, france

 

I would like them back in the following order on the results page

 

UK

         liverpool

         london

Germany

         Berlin

Fance

         Paris

 

I have tried changing the query to....

 

 

 $query_rs_city = "SELECT city.*, countries.countryID, countries.country FROM city INNER JOIN countries ON countries.countryID = city.cityCountryUK GROUP BY city.cityCountryUK";$rs_city = mysql_query($query_rs_city, $dbconnect) or die(mysql_error());   //and the record set$country = false;do{  if($country != $row['country']){echo "<strong>$row['country']</strong>";} else {$country = $row['country'];} echo "    $row['cityname']";      }while($row = mysql_fetch_assoc($rs_city)); 

 

but that seems to fail.  Can anyone point me in the right direction please?  Im a little stuck as to what to try next.

[xenophobia]

Errmm there are many ways to do it.

 

1st:

Two queries. First query is to store all country's ID and loop it. Then from the country ids, you yield the cities name based on the country's ID.

 

2nd:

Loop the result set you have, store them into a multidimension array. Eg:
[

  "uk" : [

    "liverpool",

    "london"

  ],

  "germany" : [

    "..."

  ]

]

Tips: you can use the "isset" function to check whether the array key is exists. From the array list, then you can print them in the desired order.

[DaveyK]

$rs_city = mysql_query($query_rs_city, $dbconnect) or die(mysql_error());
$data = array();

while ($row = mysql_fetch_assoc($rs_city))
{
   $data[$row['country']][] = $row;
}

print_r($data);

 

Try that