[SOLVED] Noob suck and sort from two tables

[mreish]

I have a little project going that's more of a programming exercise than anything.  It keeps track of outbound links from a site and I'm trying build a statistics page.

 

I have two tables. One stores information about web sites and one records the agent, IP, etc.  They look like this:

 

-- sites table --
siteID siteName
  1      test1.com
  2      test2.com
  3      test3.com

-- hits table --
siteID hitAgent
  3      firefox
  3      safari
  2      firefox
  3      safari
  3      firefox

 

I want the report to look like this:

test3.com 4

test2.com 1

test1.com 0

 

I've been looking through the mySQL documentation (gasp!) but I got to admit there's a lot of concepts that I'm missing which makes the going kind of rough.

 

I'm using PHP 4.4.1  and MySQL version  4.1.21-standard.

 

Any advice or a good tutorial?

[fenway]

Try this:

 

SELECT s.siteName, COUNT(*) 
FROM sites AS s
LEFT JOIN hits AS h ON ( h.siteID = s.siteID )
GROUP BY h.siteID

[mreish]

You're my hero!  It works like a charm!  I need to sit down with my mySQL book and figure what it's doing but it's doing it. Thanks for the super quick reply too.

 

Here's the project if anyone cares:

http://www.evcircuit.com

[fenway]

Three things that "it's doing": joining the tables based upon the FK relationship (siteID), aggregating all matching rows based on siteID for the count, and using left join instead of inner join so that sites with no hits show up with count=0, and are not excluded because of no matching hit rows.