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 7 of 7
  1. #1
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts

    count rows inside array

    Hi, I got the state array working, just don't know how to go about
    counting the states individually within the query as well.,

    PHP Code:
    $result mysql_query("SELECT state FROM test WHERE country=us");
    //$num = mysql_num_rows($result);//gives the total count only

        
    $states=array(); 
        while(
    $row mysql_fetch_assoc($result))  {
            
    $states[]=$row["state"]; 
        }

    echo 
    implode("*"$states);//works fine
    echo implode("*"$num);// need row count to match above indv states 
    Thanks
    Sonny
    Last edited by sonny; 12-03-2012 at 07:08 AM.

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    The simplest solution with the code you already have is to simply count the entries in the PHP within the while loop. That way you avoid a second call to the database to do the counting there.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by felgall View Post
    The simplest solution with the code you already have is to simply count the entries in the PHP within the while loop. That way you avoid a second call to the database to do the counting there.
    can you give an example how to properly do that within the loop?

    I need to display states with their individual counts and put into array
    like I did with states. I'm not sure how to do that, I'm bad working with
    arrays.

    Thanks
    Sonny

  • #4
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    Hi guys really need help, cannot get count query to work


    this works correctly for state names
    PHP Code:
    $result mysql_query("SELECT DISTINCT state FROM test WHERE country='us'");
    //$num = mysql_num_rows(result);//gives the total count only (WRONG)

        
    $states=array(); 
        while(
    $row mysql_fetch_assoc($result))  {
            
    $states[]=$row["state"]; 
        }

    echo 
    implode("*"$states);//works fine 

    Shouldn't this give the same result for individual state counts,
    as the other query above does for state names?
    PHP Code:
        $result2 mysql_query("SELECT COUNT(state) AS num FROM test WHERE country ='us'") or die(mysql_error());
        
    $num=array(); 
        while(
    $row mysql_fetch_assoc($result2))  
        {
            
    $num[]=$row["num"];
        }

    echo 
    implode('*',$num);
    example like *27*52*78 etc 
    Sonny
    Last edited by sonny; 12-03-2012 at 04:24 AM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Nope. First of all, when you do COUNT(state) then, unless some of the records in your text table have *NULL* state values, then you will get *exactly* the same answer as if you did
    Code:
    SELECT COUNT(*) AS num FROM test WHERE country = 'us'
    In other words the TOTAL COUNT OF ALL records that match on country = 'us'. ONE SINGLE NUMBER. Period.

    In short, COUNT(state) does not *AT ALL* mean what you think it means.

    I *think* that what you are after is this:
    Code:
    SELECT state, COUNT(*) AS num FROM test 
    WHERE COUNTRY='us'
    GROUP BY state
    ORDER BY state
    And that will give you records such as
    Code:
    Alabama   37
    Alaska     5
    .. etc. ...
    But of course now you won't want to use implode, more than likely.

    I'm not sure why you ever wanted to use implode in the first place. What use is it?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sonny (12-03-2012)

  • #6
    Regular Coder sonny's Avatar
    Join Date
    Apr 2008
    Location
    United States
    Posts
    567
    Thanks
    88
    Thanked 0 Times in 0 Posts
    That's exactly what I wound up doing, I was searching for hours and found this info
    http://www.w3resource.com/mysql/aggr...h-group-by.php

    It turned out to be really simple.

    I am making a pie chart and it needs data like this,
    $data = explode('*',$data);

    every time I try to mess that I get errors somewhere, but everything works now so I'm
    happy. that group by count is very powerful and simple.I also used AND state !='' for empty state values

    I get the date into array like
    $states[]=$row["states"];
    $num[]=$row["sum"];

    Thanks
    Sonny
    Last edited by sonny; 12-03-2012 at 07:13 AM.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    I also used AND state !='' for empty state values...
    That's good, but better would be
    Code:
       AND IFNULL(state,'') != ''
    That will handle state values that are either a blank string ('') or NULL in the DB. A safer test, over all.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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