Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,486
    Thanks
    279
    Thanked 32 Times in 31 Posts

    interesting results and wonder why

    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 COUNTadv_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?
    Last edited by durangod; 10-06-2012 at 11:34 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    durangod (10-06-2012)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •