[RESOLVED] MySQL doesn't give me number that PHP gives me...

[bodzan]

Hi all,

I appologize for the title but I can't say it in one sentence what's bothering me...

Here's the deal:
I have in a table stats of which one field is marked either 1 or 0. I want my script to show how many times that number is 1...
At first I did it with PHP checking the value like this:

$c = 0;
$query = mysql_query("SELECT field_name FROM TABLE_NAME");
while($result = mysql_fetch_assoc($query)){
    if($result['field_name'] == 1){
        $c ++;
    }
}
echo $c;

Later on I wanted to do the same thing with MySQL by doing this:

$query = mysql_query("SELECT field_name FROM TABLE_NAME WHERE field_name = 1");
echo mysql_num_rows($query);

The result was the number much different that the $c previously mentioned (162 over 167, or 40 over 44, or - my personal favorite - 55 over 68)...

Why is this difference happening and which one is the correct number?

I need to emphasize that the problem is much more complicated and I just simplified it here for the sake of argument...

I never had this kind of problem and I'm baffled with it... Shouldn't the two numbers be the same?

Any thoughts are more than welcome...

[nemonoman]

Don't simplify; the problem probably resides in the actual complex data.

[bodzan]

In table STATS I have stats data with my field called field_name and the foreign key named number...

In the second table (MEMBERS) I have members data with a key that is used in table A (number)...

Now, some members belong to someone - meaning they are referred to a site by some other member from the same table...

Now, I need a number that tells me how many users under a member have that field_name set to 1...

It's like this: member NAME has 20 members under him and 10 of them have that field set to 1... So I need is number 10...

So the thing I have to do is for every member in my member database check for other members that are registered under him and then for each to check if the field_name is set to 1...

The code for the latest attempt is:

$tot=0;
$sqlgd = my_query("SELECT number,memberref FROM MEMBERS WHERE number != '' AND memberref != ''");//check only users that have a referrer and have a number registered in out members table
while($rgd = mysql_fetch_assoc($sqlgd)){//all member details
	$sqlgs = my_query("SELECT stats_date,field_name FROM STATS WHERE number = '$rgd[number]' AND field_name = 1");
	if(mysql_num_rows($sqlgs)>0){//there are stats for given member
            $tot++;
	}
}

The previous atempt is like this:

$tot=0;
$sqlgd = mysql_query("SELECT number,memberref FROM MEMBERS WHERE number != '' AND memberref != ''");//check only users that have a referrer and have a number registered in out members table
while($rgd = mysql_fetch_assoc($sqlgd)){//all member details
	$sqlgs = mysql_query("SELECT stats_date,field_name FROM STATS WHERE number = '$rgd[number]'");

if(mysql_num_rows($sqlgs)>0){//there are stats for given member
        while($rgs = mysql_fetch_assoc($sqlgs)){
              if($rgs['field_name']==1){
                    $tot++;
              }
        }
}
}

Clearly the $tot is the number in question and I don't happen to know why they are different...

[mrhappiness]

in the first code fragment $tot gets incremented by 1 if

mysql_num_rows($sqlgs)

is bigger than 0

in the second code fragment $tot gets incremented by 1 for up to

mysql_num_rows($sqlgs)

times (due to the while loop inside)

got it?

1st:

 if(mysql_num_rows($sqlgs)>0){//there are stats for given member
            //this code gets executed once, no matter how many rows
            //have been returned
            $tot++;
    } 

2nd

if(mysql_num_rows($sqlgs)>0){//there are stats for given member
            while($rgs = mysql_fetch_assoc($sqlgs)){
           //this code gets executed mysql_num_rows($sqlgs) times
                  if($rgs['field_name']==1){
                        //this code gets executed as often as $rgs['field_name'] = 1
                        //this is something between 0 and mysql_num_rows($sqlgs)
                        $tot++;
                  }
            }
    }

[bodzan]

I just checked the database and there realy are more rows than I anticipated... I figured the number is set to 1 only one time but it appears to be more than once...

Thanx mrhappiness!

Geee what a dork I am sometimes... :-)