Resolved interesting results and wonder why

[durangod]

Hi i was doing some counts for a admin page and i discovered my count was wrong and im not sure why the results are different.

If i post this in the sql of phpMyAdmin

PHP Code:

//for total bio display
$query="SELECT COUNT( adv_userid ) 
FROM mytable WHERE adv_bio != '' AND bio_approved='1'";
$gbio=mysql_query($query,$link) or die(mysql_error());
$totgbio=mysql_num_rows($gbio);


Result is:  //copied and pasted directly from phpmyadmin display

SELECT COUNT( adv_userid ) 
FROM mytable
WHERE adv_bio !=''
AND bio_approved ='1'

 Profiling 
[Inline] [ Edit ] [ Explain SQL ] [ Create PHP Code ] [ Refresh ] 



+ Options
COUNT(adv_userid) 
2        

So the result is 2 which is correct! 


But if i post this in my php like this

PHP Code:


$query="SELECT COUNT( adv_userid ) 
FROM mytable WHERE adv_bio != '' AND bio_approved='1'";
$gbio=mysql_query($query,$link) or die(mysql_error());
$totgbio=mysql_num_rows($gbio);

and then display it like this 

Tot Bios = <?=$totgbio;?>

It gives me 1  Which is wrong!

But if i post it in the file like this instead of using count just select all.

PHP Code:

$query="SELECT * 
FROM mytable WHERE adv_bio != '' AND bio_approved='1'";
$gbio=mysql_query($query,$link) or die(mysql_error());
$totgbio=mysql_num_rows($gbio);

and then display it like this 

Tot Bios = <?=$totgbio;?>

It gives me 2  Which is correct!

I wonder why count does not work i the file but works in the sql. And its the same statement?

Strange.. any ideas?

[Fou-Lu]

Your use of mysql_num_rows is incorrect.
Since you don't have a group by on your query, you end up with a count of all records matching the where criteria. In other words, you have 1 result, and that result is the number that match. Using a select without a count returns 2 records, which is why the mysql_num_rows shows that many.
You need to fetch the count from the $gbio.