Count problem

[Bryan]

I've been trying to work out a problem but I'm having no luck. I have
field in my table for area and I need to count the number of matches
and assign a variable for each one.. this is what I have but it doesn't
work.. any ideas?

$testcount = mysql_query("se lect area, count(area) as newcount from rea
group by area order by area");
while ($returncount = mysql_fetch_arr ay($testcount)) {
if ($returncount[area] == 'RCC') {
$rccn = $returncount[newcount];
}
if ($returncount[area] == 'RCL') {
$rccl = $returncount[newcount];
}
if ($returncount[area] == 'RCH') {
$rcch = $returncount[newcount];
}
if ($returncount[area] == 'RCW') {
$rccw = $returncount[newcount];
}
if ($returncount[area] == 'RNK') {
$rnkn = $returncount[newcount];
}
if ($returncount[area] == 'RNK') {
$rnen = $returncount[newcount];
}
}

[Andy Hassall]

Re: Count problem

On 20 Jun 2005 11:39:15 -0700, "Bryan" <bryanilton@gma il.com> wrote:
[color=blue]
>I've been trying to work out a problem but I'm having no luck. I have
>field in my table for area and I need to count the number of matches
>and assign a variable for each one.. this is what I have but it doesn't
>work.. any ideas?[/color]

*sigh* In what way does it not work?

What did you expect it to do?
What does it do?
How do the two differ?

Saying "it doesn't work" without explaining what it does do is not helpful and
could result in getting replies that are even more irritable than this one ;-)
[color=blue]
>$testcount = mysql_query("se lect area, count(area) as newcount from rea
>group by area order by area");[/color]

You haven't checked whether this query worked or not.

Check the return value. If it is false, then the query has failed. The error
is available from mysql_error().

It will also show a warning message, unless you have PHP configured to not
show or log warnings.

You must develop code with errors enabled (see the display_errors and
error_reporting options), and preferably warnings set to maximum, else you are
coding blindfolded.

One thing that jumps out is whether the table really called 'rea'? Or did you
mean 'area'?

With error checking and errors displayed, PHP would have told you this, and
you'd be certain about what's wrong - instead you're posting to thousands of
people asking them to guess based on insufficient information.
[color=blue]
>while ($returncount = mysql_fetch_arr ay($testcount)) {
>if ($returncount[area] == 'RCC') {[/color]

Associative array keys should be quoted else it'll raise a warning. It
wouldn't stop the code working, though (unless you had a constant named
'area').
[color=blue]
>$rccn = $returncount[newcount];
>}
>if ($returncount[area] == 'RCL') {
>$rccl = $returncount[newcount];
>}
>if ($returncount[area] == 'RCH') {
>$rcch = $returncount[newcount];
>}
>if ($returncount[area] == 'RCW') {
>$rccw = $returncount[newcount];
>}
>if ($returncount[area] == 'RNK') {
>$rnkn = $returncount[newcount];
>}
>if ($returncount[area] == 'RNK') {
>$rnen = $returncount[newcount];
>}
>}[/color]

OK, so how do you know it didn't work?

You need to post an example of the input data and the output data to prove it
didn't work.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[CJ Llewellyn]

Re: Count problem

On Mon, 20 Jun 2005 11:39:15 -0700, Bryan wrote:
[color=blue]
> I've been trying to work out a problem but I'm having no luck. I have
> field in my table for area and I need to count the number of matches
> and assign a variable for each one.. this is what I have but it doesn't
> work.. any ideas?
>
> $testcount = mysql_query("se lect area, count(area) as newcount from rea
> group by area order by area");
> while ($returncount = mysql_fetch_arr ay($testcount)) {
> if ($returncount[area] == 'RCC') {
> $rccn = $returncount[newcount];
> }
> if ($returncount[area] == 'RCL') {
> $rccl = $returncount[newcount];
> }
> if ($returncount[area] == 'RCH') {
> $rcch = $returncount[newcount];
> }
> if ($returncount[area] == 'RCW') {
> $rccw = $returncount[newcount];
> }
> if ($returncount[area] == 'RNK') {
> $rnkn = $returncount[newcount];
> }
> if ($returncount[area] == 'RNK') {
> $rnen = $returncount[newcount];
> }
> }[/color]

Like Andy said, try some basic error checking first

// always initialise variables before you use them
$aAreas = array();

$sql = "select area, count(area) as newcount from rea group by area order
by area";
$results = mysql_query($sq l , $conn); if(! $results || mysql_error($co nn)
|| mysql_num_rows( $results)<1) {
echo "Unable to get results [$sql] : " . mysql_error($co nn);
}
else
{
// why use 10 variables when 1 will do?
while($row = mysql_fetch_arr ay($results)) {
$aAreas[$row[area]] = $row[newcount];
}
// $aAreas will now hold your results with the extra
print_r($aAreas ) . "<br>\n";
}

[Wayne Delia]

Re: Count problem

Bryan wrote:[color=blue]
> I've been trying to work out a problem but I'm having no luck. I have
> field in my table for area and I need to count the number of matches
> and assign a variable for each one.. this is what I have but it doesn't
> work.. any ideas?
>
> $testcount = mysql_query("se lect area, count(area) as newcount from rea
> group by area order by area");[/color]

MySql queries involving count(columname ) require a GROUP BY clause, for
starters.

WMD

[Andy Hassall]

Re: Count problem

On Mon, 20 Jun 2005 21:06:21 GMT, Wayne Delia <wmd@deliafamil y.net> wrote:
[color=blue]
>Bryan wrote:[color=green]
>> I've been trying to work out a problem but I'm having no luck. I have
>> field in my table for area and I need to count the number of matches
>> and assign a variable for each one.. this is what I have but it doesn't
>> work.. any ideas?
>>
>> $testcount = mysql_query("se lect area, count(area) as newcount from rea
>> group by area order by area");[/color][/color]
^^^^^^^^^^^^^[color=blue]
>
>MySql queries involving count(columname ) require a GROUP BY clause, for
>starters.[/color]

Like the one he's already got? :-)

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Wayne Delia]

Re: Count problem

Andy Hassall wrote:[color=blue]
> On Mon, 20 Jun 2005 21:06:21 GMT, Wayne Delia <wmd@deliafamil y.net> wrote:
>
>[color=green]
>>Bryan wrote:
>>[color=darkred]
>>>I've been trying to work out a problem but I'm having no luck. I have
>>>field in my table for area and I need to count the number of matches
>>>and assign a variable for each one.. this is what I have but it doesn't
>>>work.. any ideas?
>>>
>>>$testcount = mysql_query("se lect area, count(area) as newcount from rea
>>>group by area order by area");[/color][/color]
>
> ^^^^^^^^^^^^^
>[color=green]
>>MySql queries involving count(columname ) require a GROUP BY clause, for
>>starters.[/color]
>
>
> Like the one he's already got? :-)[/color]

Oh, man - I can't believe that. That's about the third similar mistake
I've made today. I should probably go back to bed right now.

WMD

[Bryan]

Re: Count problem

Thanks for replying -- Please excuse my ignorance. I come from the
world of Excel and I'm having a little difficulty grasping the full
concept of PHP/MySQL.

The first variable (RCC) works fine, it returns "2" which it should.
The second variable (RCL) also returns "2" but should return "26".

The rest of the variables return nothing although most of them have
should have values (and some of the areas haven't yet been entered into
the table). I will try some other troubleshooting suggestions from CJ.