[thelist] php array cleverness needed

[Dunstan Orchard]

Hi there,

This is a bit complicated, but here goes...

In my mysql db I have a list of country names, and a count to go with each 
name (showing the number of members in that country).

The code below takes this data (ordered by count desc) and does the following:

[1] Adds the top 5 country names by count to an array.
[2] Sorts the array by country name.
[3] Loops through the array, printing out the country names. 
[4] Orders the full list by the name of the country.
[5] Loops through the full list printing out the country names.

This works fine when the count column is well populated. But if only, say, two 
countries have an entry greater than zero in the count column, then the last 
tthree records printed in part [1] are misleading, since they don't belong in 
the "most commonly selected countries" list, they're only there because the 
loop goes round 5 times, and they were next in the list.

What I'd like to be able to do is:

[1] Add the top 5 country names by count to an array. The count must be > 0 to 
be added to the array, so if only the first 2 countries have a count > 0 then 
only 2 records would be added.
[2] Sort the array by country name.
[3] Loop through the array, printing out the country names. 
[4] Order the full list by the name of the country.
[5] Loop through the full list printing out the country names.

Can anyone see if it's possible to do all of that in one db call?

--------------------------------------------------------------
<optgroup label="5 most commonly selected countries">
<?
$query = "SELECT country_name FROM country ORDER BY country_count DESC";
$country_result = mysql_query($query);

while ($row = mysql_fetch_assoc($country_result))
   {
   // add the results to an array - this array will contain a full list of 
countires sorted DESC by the counter
   $countries[] = $row['country_name'];
   }

// add the first 5 (as sorted by the counter remember) to an array
$i = 0;
while ($i <= 4)
   {
   $common_countries[] = $countries[$i];
   $i++;
   }

// sort the array alphabetically and print it out
array_multisort($common_countries, SORT_ASC);
foreach ($common_countries as $common_country)
   {
?>
<option value="<?=$common_country?>"><?=$common_country?></option>
<?
   }
?>
</optgroup>

<optgroup label="Full country list">
<?
// sort the full country list array alphabetically and print it out
array_multisort($countries, SORT_ASC);
foreach ($countries as $country_drop)
   {
?>
<option value="<?=$country_drop?>"<?=$selected?>><?=$country_drop?></option>
<?
   }
?>
</optgroup>
--------------------------------------------------------------

You can see the code above in action at:
http://www.petservicesexpo.com/seminar_registration/

For your info, the count of the "5 most commonly selected countries" shown 
there is:

Afghanistan = 0
Albania = 0
Canada = 1
Finland = 0
United States = 1

So you can see my problem. What I'd like is only for Canada and United States 
to be shown.

Well, thankyou so very much to anyone who gives this a bash, I know it's quite 
a favour to ask, but I've been stuggling away at it for most of today and 
haven't had any luck.

Thanks - Dunstan

---------------------------
Dorset, England
http://www.1976design.com/
http://www.orchard.it/
http://www.maccaws.org/