Resolved count rows inside array

[sonny]

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

[felgall]

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.

[sonny]

Originally Posted by felgall

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

[sonny]

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

[Old Pedant]

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

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:

SELECT state, COUNT(*) AS num FROM test 
WHERE COUNTRY='us'
GROUP BY state
ORDER BY state

And that will give you records such as

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?

[sonny]

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

[Old Pedant]

I also used AND state !='' for empty state values...

That's good, but better would be

   AND IFNULL(state,'') != ''

That will handle state values that are either a blank string ('') or NULL in the DB. A safer test, over all.