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 15 of 15
  1. #1
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts

    How to count mysql_num_rows

    Need some help. I can check if there is 0 matching for using mysql_num_rows but how do I get it to show the total number matching?

    PHP Code:
    $fquery mysql_query("SELECT currentaction FROM people WHERE currentaction='Shopping' AND currentaction='Purchasing'"); 
    PHP Code:
     if (mysql_num_rows($fquery) == '0')
             {
               
    $amount 0;
             }
             else {

             } 
    So basically I want it to show how many people are currently shopping and purchasing in total.
    Last edited by masterofollies; 03-15-2010 at 04:02 PM.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #2
    New Coder
    Join Date
    Mar 2009
    Posts
    90
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Quote Originally Posted by masterofollies View Post
    So basically I want it to show how many people are currently shopping and purchasing in total.
    Hi, try something like this:
    PHP Code:
    $fquery mysql_query("SELECT username,COUNT(*) AS CNT FROM people 
    WHERE currentaction='Shopping' AND currentaction='Purchasing' 
    GROUP BY username ORDER BY username ASC"
    ); 
    p.p.: You can use userid instead of username.
    Also I'm not sure about 'Shopping' AND 'Purchasing' or 'Shopping' OR 'Purchasing' -> depends on your database schema
    Regards
    Edit:Oh, maybe you are searching "with rollup"?
    PHP Code:
    $fquery mysql_query("SELECT username,COUNT(*) AS CNT FROM people 
    WHERE currentaction='Shopping' AND currentaction='Purchasing' 
    GROUP BY username WITH ROLLUP"
    ); 
    Last edited by koko5; 03-14-2010 at 06:35 PM.

  • #3
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    PHP Code:
    $amount mysql_num_rows($fquery);
    echo 
    $amount

  • #4
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Now it gives me an amount of 1. However there is at least 2 I saw "shopping".
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #5
    Regular Coder
    Join Date
    Jul 2009
    Posts
    136
    Thanks
    0
    Thanked 7 Times in 7 Posts
    But were those shopping also purchasing? That's what the AND means.

  • #6
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Nope, I am looking to count both. So if 3 are shopping and 2 are purchasing. I want it to display 5.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #7
    Regular Coder
    Join Date
    Jul 2009
    Posts
    136
    Thanks
    0
    Thanked 7 Times in 7 Posts
    You need an OR instead of the AND then.

  • #8
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Still getting a 1. Here is my code.

    PHP Code:
     $fquery mysql_query("SELECT currentaction,COUNT(*) AS CNT FROM people WHERE currentaction='Shopping' OR currentaction='Purchasing'");
             
    $amount mysql_num_rows($fquery);

    echo 
    "Number of people shopping: $amount"
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #9
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,864
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    PHP Code:
    $amount mysql_num_rows($fquery);

    echo 
    "Number of people shopping: $amount"
    That query would return only one row, containing a value as the number of rows matched. So, you need to fetch the value from the mysql result.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #10
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    I don't quite understand?
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #11
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by masterofollies View Post
    I don't quite understand?
    In your original query, num_rows would return the number of rows matched. In that second query using COUNT(), only one result is returned, and that results value contains the number of rows matched, so you need to fetch the value of that second query to find out what the count is.
    Last edited by MattF; 03-15-2010 at 01:06 AM.

  • #12
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    So like this?

    PHP Code:
     $fquery mysql_query("SELECT currentaction,COUNT(*) AS CNT FROM people WHERE currentaction='Shopping' OR currentaction='Purchasing'");
    $test mysql_fetch_array($fquery);         
    $amount mysql_num_rows($test);

    echo 
    "Number of people shopping: $amount"
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #13
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,864
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Nop!
    PHP Code:
    $test mysql_fetch_array($fquery);    
    echo 
    $test['CNT']; 
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    masterofollies (03-15-2010)

  • #14
    New Coder
    Join Date
    Mar 2009
    Posts
    90
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Quote Originally Posted by masterofollies View Post
    PHP Code:
     $fquery mysql_query("SELECT currentaction,COUNT(*) AS CNT FROM people WHERE currentaction='Shopping' OR currentaction='Purchasing'");
    $test mysql_fetch_array($fquery);         
    $amount mysql_num_rows($test);

    echo 
    "Number of people shopping: $amount"
    This query with no group by clause is not right. However, MySQL accepted it and applied group by internally, so result can be 1 or 2 ("Shopping" and "Purchasing" if any).
    As I wrote in my previous post, to find "Number of people shopping" group by must be performed on username/userid + with rollup or you can do simply:
    PHP Code:
    echo mysql_result(mysql_query("SELECT COUNT(DISTINCT userid) AS CNT FROM people 
    WHERE currentaction IN('Shopping','Purchasing')"
    ),0); 
    Regards

  • #15
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Got it, works. The echo $test['CNT'] one. Thanks
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?


  •  

    Posting Permissions

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