Hello,

I'm building a report for a client who wants to know how many visitors are reaching his site based on the domain name entered by the visitors.  For instance, some visitors may type in "foo.domain.com","domain.com", or "mobile.domain.com".  The report needs to be broken down by week and should display the last 10 weeks of data.

Here's some more information about the report:
- The report will have the weeks as column headers
- The report will have the domains as row labels
- The cells of the report will contain the number of visitors


I've already spent some time on the project and have built a summary table specifically for this report that looks like this:

domain_report
=============
id: int, primary key, auto-increment
week: date
domain: varchar(255)
visitors: int(11)

Some sample data looks like:

454    2009-05-04    www.fooindustries.com    21
455    2009-05-04    www.foonc.com    1871
456    2009-05-04    www.fooperfectmatch.com    4
457    2009-04-27    www.foocal.com    61

Not all domains will show up in every week.  So, for example, maybe www.foocal.com had one visitor in the week of 2009-04-27.  In the final report, there will still be a cell for www.foocal.com for 2009-04-27, but it will be 0.

Normally to generate the report I would build up a big associative array in PHP, but it would be messy since I would have to 1) query for unique weeks, 2) query for unique domains, 2) build up a big associative array like data[$domain][$week] = visits, and fill the associative array with data from a third query.

Is there some sort of self-outer-join that I can do to fetch the domains, weeks, and visitors that would translate well to a spreadsheet style report, considering that not all weeks have each domain listed?

Thanks!
- Bret