very strange database query result

i'm getting a very strange result from my database query, i was hoping someone could spot something that i missed.

heres my table with some sample data:

feeling_id  country     date            feeling     digit
25          australia   2011-02-21      bad         1
26          a开发者_运维技巧ustralia   2011-02-21      bad         0
8            france      2011-02-21      better      1

im trying to count how many of each country are in the database and how many of these have a digit of 1. so the above data would give:

australia with digit of 1 = 1

australia rows in the database = 2

france with digit of 1 = 1

france rows in the database = 1

here's the code

<?php

include ('mysqli_connect.php'); // indclude mysql connection functions

$countries = array('united states','canada','united kingdom');
$r = array(); 

foreach($countries as $country){
        //e.g. $r_spain holds the results from the query below with spain as $country
        $r[$country] = mysqli_query($dbc,"SELECT * FROM feelings WHERE country = '$country' AND digit='1'");

        //loop through the results
        while($row = mysqli_fetch_array($r[$country], MYSQLI_ASSOC)){
                $rowCount = mysqli_num_rows($r[$country]);

        }       

        //e.g. $r_spain holds the results from the query below with spain as $country
        $r[$country] = mysqli_query($dbc,"SELECT * FROM feelings WHERE country = '$country'");

        //loop through the results
        while($row = mysqli_fetch_array($r[$country], MYSQLI_ASSOC)){
                $rowCount2 = mysqli_num_rows($r[$country]);

        }   

echo $country . '=' . $rowCount . '<br />';
echo $country . '=' . $rowCount2 . '<br />';

}
?>

i get the following result when there is only two canada rows in the database, neither of these have a digit of 1???

united states with digit of 1 =13

united states rows in the database =17

canada with digit of 1 =13

canada rows in the database =2

united kingdom with digit of 1 =4

united kingdom rows in the database =4

the canada with digit of 1 answer seems to copy the united states digit of 1 result.

can anybody see where i've gone wrong?

thanks a lot, alsweeet

why looping through the results when you could just do a count() on the sql??

SELECT COUNT(feeling_id) FROM feelings WHERE country = '$country' AND digit='1'

You need to set $rowCount and $rowCount2 to 0 at the beginning of your foreach. Because no rows are found for Canada, it never enters the while loops and therefore uses the values for $rowCount and $rowCount2 from the previous iteration.

foreach($countries as $country){
    $rowCount = 0;
    $rowCount2 = 0;

If there are no matching rows $rowCount and/or $rowCount don't get set at all. You need to set those to 0 at the top of the loop.

However, there is a much better way of doing this:

select country, digit, count(*) from your_tbl group by country, digit;

That will return results with 3 fields: country, digit, and the number of matching rows for that combo.