Biten off more than a noob can chew with GROUP BY & COUNT?

[chanzilla]

I'm a noob who's bitten off more than I can chew. I got some ASP/SQL background getting my feet wet with PHP/MySQL for what I thought was a quick n dirty project.  Help! I'm using MySQL 4, btw.

 

I'm trying to create a tally based on two tables, to output to a HTML list. For simplicity,I am dumbing down my problem to the below example.

 

Table A

Fields

AID,  name, timestamp

Values

001, 'bob', 12:01

002, 'cat', 12:02

003, 'cat', 12:03

004, 'eli', 12:06

005, 'cat', 12:12

006, 'eli', 12:23

 

Table B

Fields

BID, name, info, image, category

Values

010, 'cat','abc','img01.jpg','blue meanie'

011, 'bob','lmn','img02.jpg','yellow sub'

012, 'eli','xyz','img03.jpg','blue meanie'

 

I want to take the results of Table A from a particular category, and add corresponding details from Table B, Group them By name, then ORDER BY Count of records per group. Loosely, I think it should SQL like this:

 

SELECT (A.AID, A.name, A.timestamp, B,BID, B.name, B.info, B.image, B.category)

WHERE (A.name = B.name AND B.category = 'blue meanines')

GROUP BY (A.name)

ORDER BY COUNT(A.AID)

 

Without the GROUP BY & ORDER BY, I would expect the following results:

 

002,'cat',12:02,010,'cat','abc','img01.jpg','blue meanie'

003,'cat',12:03,010,'cat','abc','img01.jpg','blue meanie'

004,'eli',12:06,012,'eli','xyz','img03.jpg','blue meanie'

005,'cat',12:12,010,'cat','abc','img01.jpg','blue meanie'

006,'eli',12:23,012,'eli','xyz','img03.jpg','blue meanie'

 

But I'd like to be able to parse the following results to PHP/HTML:

 

'cat','abc','img01.jpg',3 (# of cats returned)

'eli','xyz','img03.jpg',2 (# of elis returned)

 

Which would loop through PHP/HTML through a bullet list:

 

 

• Blue Meanie Results!
  
  • There are 3 cats [img01.jpg]
    
  • There are 2 elis [img03.jpg]
    

 

 

It seemed so easy on paper when we were discussing the feature. The db tables already exist for seperate reasons and I thought it would be neat to generate a Top 10 list with the associated info. But MySQL doesn't like it when I try to use GROUP BY and COUNT in the same query. It sucks, but this is my intro to MySQL (I've done some basic MS SQL queries but never used COUNT or GROUP BY before). I tried manipulated the raw results with arrays, but that got top-heavy and messy many lines of code later, and I'm sure in SQL, I'm not properly respecting the syntax.  Can anyone please help me control my blue meanies?

[Barand]

$sql = "SELECT B.name, B.info, B.image, COUNT(*) as cats
          FROM tableA A JOIN tableB B ON  A.name = B.name
          WHERE B.category = 'blue meanie'
          GROUP BY B.name
          ORDER BY cats";
$res = mysql_query($sql);
echo "<table>\n";
while (list($name, $info, $image, $cats) = mysql_fetch_row($res))
{
    echo "<tr>
           <td>$name</td>
           <td>$info</td>
           <td>$image</td>
           <td>$cats</td>
           </tr>\n";
}
echo "</table>\n";