Help Needed Using COUNT(*) Function ..... Please

gneville

Hi All,

I am ripping my hair out over why this doesnt work.......and im sure its something really simple.

Basically I have a Database Of Data That I want To Print to a PHP Page, which will list in a Descending order of how many are in the same in a certain Field......

e.g

FRUIT | COUNT(*)

APPLES = 10
BANANAS = 9
PEACHES = 6

ORANGES = 2

Now I know what the command SQL command is :

"SELECT FRUIT , COUNT(*) FROM mytable GROUP BY FRUIT ORDER BY COUNT( * ) DESC LIMIT 0, 30"

As I have tested this within phpMyAdmin and it works......

However I fail to print this to PHP.

I believe I need a Loop here in order to get all the rows and print them all

I have used

"
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {

}
"

SO my Code Looks Like

$query = "'SELECT FRUIT , COUNT(*) FROM mytable GROUP BY FRUIT ORDER BY COUNT( * ) DESC LIMIT 0, 30 '";

$result = mysql_query($query);

while ($row = mysql_fetch_row($result)) {

<?php echo ($row['FRUIT'])?>
<?php echo ($row['COUNT'])?>

<?php

}

mysql_free_result($result);
mysql_close();

However This Fails,

I have tried using mysql_fetch_row and mysql_fetch_array and mysql_fetch_assoc but all show

"
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource"

Someone PLease Help Me

Installer

"COUNT()" is a really weird name to give to a column, and it makes it confusing trying to figure out just what you want to do (and just a bad idea in general). And the query line must have gotten mangled when you pasted it into your post. As it shows, it wouldn't have succeeded (bad query syntax). And most of those backticks are unnecessary, not to mention the single quotes inside the double quotes. And why break out of and back into php for every line? And if you want results for COUNT(*), use "echo $row['COUNT()'];", not "echo $row['COUNT'];". And all that shouting...

But taking a wild guess at what you're trying to do, here's some code that works:

$query = "SELECT FRUIT, COUNT(*) AS ct FROM mytable GROUP BY FRUIT ORDER BY `COUNT(*)` DESC LIMIT 0, 30"; 
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) { 
    echo $row['FRUIT'] . ' ';
    echo $row['ct'] . '<br />';
}

samudasu

Well first off you should read the section for mysql in the php manual.
The line "Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource" means that the argument "$result" that you supplied is invalid. This could be for a number of reasons. Start off by adding some debugging code to see where the failure occurs. Use a simple query until you get it working.

mysql_connect('host', 'user', 'pass') or die("can't connect to host");
mysql_select_db('mydatabase') or die("can't connect to database");
$query = "select * from mytable";
$result = mysql_query($query) or die("query failed");

while($row = mysql_fetch_array($result)) {
    print $row['FRUIT'] . "<br />";
}

Installer

"$row['Apple']" will produce an error. As far as I can tell , he has no column by that name.

samudasu

Yeah, i noticed that after i posted. It's already been changed

Weedpacket

"SELECT FRUIT, COUNT(*) AS ct FROM mytable GROUP BY FRUIT ORDER BY `COUNT(*)` DESC LIMIT 0, 30"

And if you're going to use an alias, you might as well use it elsewhere as well and save even more possibility of confusion

"SELECT FRUIT, COUNT(*) AS ct FROM mytable GROUP BY FRUIT ORDER BY ct DESC LIMIT 0, 30"

Installer

Weedpacket wrote:
"SELECT FRUIT, COUNT(*) AS ct FROM mytable GROUP BY FRUIT ORDER BY `COUNT(*)` DESC LIMIT 0, 30"
And if you're going to use an alias, you might as well use it elsewhere as well and save even more possibility of confusion
"SELECT FRUIT, COUNT(*) AS ct FROM mytable GROUP BY FRUIT ORDER BY ct DESC LIMIT 0, 30"

But those are two completely different queries. In the first the order is by the column named COUNT(*), while in the second the order is by the return from the function COUNT(*) aliased as "ct".

Result from first query:
BANANAS, 1
PEACHES, 1
ORANGES, 1
APPLES, 1

Result from second query:
APPLES, 1
BANANAS, 1
PEACHES, 1
ORANGES, 1

Although who knows if either one comes close to what he wants to achieve. For my part, I was just guessing.