Very strange database query result

Question

I get a very strange result from my database query, I was hoping that someone might notice what I missed.

heres my table with some data examples:

feeling_id  country     date            feeling     digit
25          australia   2011-02-21      bad         1
26          australia   2011-02-21      bad         0
8            france      2011-02-21      better      1

im trying to calculate how much of each country is in the database, and how many of them have the number 1. Thus, the above data will give:

Australia with the number 1 = 1

australia rows in database = 2

france with the number 1 = 1

france rows in database = 1

here is 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 are only two canada lines in the database, none of them have the number 1 ???

with the number 1 = 13

combined state rows in database = 17

Canada with the number 1 = 13

canada rows in database = 2

united kingdom with the number 1 = 4

united kingdom rows in database = 4

---

the answer should say: "Canada with the number 1 = 0"

Canada with a digit from 1 answer seems to copy the combined state digit with 1 result.

Can anyone see where I made a mistake?

Thanks alsweeet

Answer 1

You need to install $rowCountand $rowCount2to 0 at the beginning of your foreach. Since no row was found for Canada, it never goes into while loops and therefore uses the values ​​for $rowCountand $rowCount2from the previous iteration.

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

Answer 2

why loop results when you can just do count () on sql ??

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

Answer 3

If there are no matching rows, $ rowCount and / or $ rowCount are not set at all. You need to set them to 0 at the top of the loop.

However, there is a much better way to do this:

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

This will return results with three fields: country, number and the number of matching lines for this combo.