Re: [PHP-DB] Re: [users@httpd] PHP/MySQL and COUNT() problem

I notice your query says

SELECT x,y,COUNT(y),z FROM ... GROUP BY y

It was my understanding that when using aggregate function, you need to 
group by each field in your select-clause, so your query should be:

SELECT x,y,COUNT(y),z FROM ... GROUP BY x, y, z

Hope this helps,

Yves Sucaet
Iowa State University



----- Original Message ----- 
From: "Evert Lammerts" <[email protected]>
To: <[email protected]>; <[email protected]>
Sent: Saturday, April 26, 2008 6:31 AM
Subject: [PHP-DB] Re: [users@httpd] PHP/MySQL and COUNT() problem


> I've forwarded this msg to the php-db list ([email protected]). Try to 
> subscribe to it.
>
> The mysql extension for php is only an interface between php and mysql - 
> the parsing of queries is done within mysql itself. So if your query works 
> through CLI it will also work from within php. Try to var_dump every 
> result row:
>
> while ($row = mysql_fetch_assoc($result))
>    var_dump($row);
>
> Apart from this, why would you use the COUNT function and return the 
> amount of NOT NULL y's (which all of your y's are because of your WHERE 
> clause) for every row in the result set? You can simply count the amount 
> of loops when looping over the result, or you can use php's 
> mysql_num_rows($result) function.
>
> Paul Heath wrote:
>> This may be the wrong message board for this [apologies if it is], but if 
>> any of you are PHP/MySQL gurus. Have been trying to get a particular 
>> MySQL query to work the through PHP5 with out success.
>>
>> I'll simplify my problem:
>>
>> I have an MySQL query along the lines of ("SELECT x,y,COUNT(y),z FROM 
>> table WHERE y LIKE '%/some text% /AND --/someotherstuff-- /GROUP BY y");
>>
>> When i run this on the MySQL command line it works fine, when i run 
>> inside my PHP file, it returns everything ok except the COUNT (y) value 
>> [Note y is a string variable]....
>>
>> I then go on  to run
>> /$result = mysql_query($query) or die ('cant run query'.mysql_error());/
>> /while ($row = mysql_fetch_assoc($result)) {
>> } etc etc...
>>
>> /all my other queries in this file work perfectly.
>>
>> When i put some checking code in it definately is returning blank/null 
>> from MySQL for this count value
>>
>> Is there something special with PHP and COUNT() MySQL functions im not 
>> doing.....?? or does the problem lie on the MySQL side and how it treats 
>> PHP requests for COUNT()?
>>
>> I would appreciate any advice now cos now ive ran out of ideas!
>>
>> -- 
>> Rgds
>> Paul
>>
>
>
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>