Help! Trouble with while loop & nested queries for photo gallery

First loop works, but the rest generate errors

[HBird]

I have a PHP/MySQL-based photo website and I'm trying to program an album page. I need to (1) display a sample photo for each available country and (2) display the number of photos in that country. Like this:

[6789.jpg]
England
734 photos

[3493.jpg]
France
560 photos

It seems simple, but apparently involves a while loop with nested queries, and I can't get it to work properly!

I have set the sample photo for each country by adding the keyword 'countrypic' to the keywords field of the desired photo. So first I run a query for all photos with the keyword 'countrypic', which lists the countries and their sample photo IDs. Then I run a while loop to display each image thumbnail ($id.jpg) with the country name underneath. This works fine.

But I run into problems when I try to query the database again in order to count the photos. The first country (England) displays great, including the proper number of photos. But when the loop runs a second time, it loses the country variable. So the first thumbnail query is "... WHERE country='England' ..." but the second thumbnail query is "... WHERE country='' ...".

Here's what I have so far:
_________________________________

include("connect.php");
$query = "SELECT id, country FROM Photos WHERE keywords LIKE '%countrypic%' ORDER BY country"; // get list of countries and thumbnail IDs
$result=mysql_query($query);
$num=mysql_numrows($result);
$country = mysql_result($result ,$i ,"country");
$id = mysql_result($result, $i, "id");
mysql_close();
if ($num=="0") echo "<p>Sorry, there are no results to display for this album.</p>";
else {
echo "<p>There are currently <strong>$num</strong> countries represented in our database.</p><br>";
$i=0;
while ($i < $num) {
$country = mysql_result($result ,$i ,"country");
$id = mysql_result($result, $i, "id");
echo "<div class=\"thumbnailalbum\"><a href=\"album?country=$country\"><img src=\"images/thumbs/$id.jpg\" border=\"0\"></a><div class=\"albumtitle\">$country</div>";
include("connect.php");
$query2 = "SELECT id FROM Photos WHERE country='$country'"; // count number of photos in each country
echo $query2;
$result=mysql_query($query2);
$count=mysql_numrows($result);
mysql_close();
echo "<div class=\"caption\">$count Total Photos</div>"; // display number of photos
echo "</div>"; // end thumbnail div
$i++;
}
} // end if results
___________________________________

Note: I wanted to use SELECT COUNT(*) for the second query, but then the first photo count (England) didn't even work. So I've used 'SELECT id' for the time being instead.

Does anyone see the problem in here? I appreciate any help I can get!

[CyBerAliEn]

I believe this is your issue...

You have 2 queries...
(1) gets list of photos by country
(2) count number of photos for country

The results of your first query are being stored to in the variable $results. You then basically loop through 'results'. But inside this loop, you are re-assigning the variables 'results' to the second/sub query. So when PHP returns to the main loop, it is working on a different result set.

Try changing your second query to:

$result[b]2[/b]=mysql_query($query2);
$count[b]2[/b]=mysql_numrows($result[b]2[/b]); 

This should resolve your problem by allowing the 2nd query to run as expected. You have to be careful about using variable names when you are within loops... because if you overwrite values used by/in the loop, it can cause unexpected results.

[HBird]

Yes! That's it! Thank you so much. I thought to use $query2, but not $result2! Stupid. It's always so helpful to have a second pair of eyes look at the problem. Thanks again.