GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Post by simonmlewis »

Hi all

I need a genius out there somewhere to find me the solution for something that sounds so very easy.

I have a log of searched entries as an SQL database table. It has just two fields: id, department.

I was to produce a page that shows each individual department searched on (not duplicated - ie. GROUP BY and DISTINCT), with the number next to it showing how many entries of it there are.

ie. there are 25 entries of "commercial", so the result would render as:

Department hits
commercial 25

I have tried the following:

Code: Select all

$query = mysql_query ("SELECT DISTINCT department, COUNT(DISTINCT department) as num_off FROM departmentlog GROUP BY department") or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
echo "$row->department There are " .$row['num_of'] ."finished.";
}
But I get this as a result:
Warning: mysql_fetch_array(): 3 is not a valid MySQL result resource in other/deptlog.php on line 33
Can anyone please help.
Thank you.
Simon
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Post by Mark Baker »

Drop the distinct, it's meaningless in this context because you're doing a group by on department

Code: Select all

 
$query = mysql_query ("SELECT department, COUNT(*) as num_off FROM departmentlog GROUP BY department")
   or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
   echo $row['department'] . " There are " . $row['num_of'] . " finished.<br />";
}
 
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Post by simonmlewis »

Same error:
Warning: mysql_fetch_array(): 3 is not a valid MySQL result resource in /other/deptlog.php on line 33
Line 33 is this:

Code: Select all

while($row = mysql_fetch_array($result)){
Total code now shows:

Code: Select all

<?php 
$sqlconn=mysql_connect("localhost","U","P");
$rs=mysql_select_db("DB",$sqlconn);
$query = mysql_query ("SELECT department, COUNT(*) as num_off FROM departmentlog GROUP BY department") or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
echo "$row->department There are " .$row['num_of'] ."finished.<br/>";
}
 
    mysql_close($sqlconn);
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Post by Mark Baker »

Code: Select all

 
$result = mysql_query ("SELECT department, COUNT(*) as num_off FROM departmentlog GROUP BY department")
   or die (mysql_error());
 
while($row = mysql_fetch_array($result)){
   echo $row['department'] . " There are " . $row['num_of'] . " finished.<br />";
}
 
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GROUP BY, DISTINCT, COUNT..... WHICH IS IT???

Post by simonmlewis »

That's excellent, thank you very much.

Result was this:
Agricultural Property There are 16 finished.
Associates There are 30 finished.
Business Rescue & Insolvency There are 8 finished.
Commercial Property There are 19 finished.
Company and Commercial There are 13 finished.
Consultants There are 28 finished.
Debt Recovery There are 8 finished.
Well, part of it. I can play around the with text and layout now.

Brilliant!!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply