Getting Monthly totals

[dcp]

I'm a bit new to this, and I apologize in advance for my thickheadedness. I hope someone can help me with a suggestion.

 

I have two tables: programs and events. All events are categorized as a kind of program (and include a program id). I'm trying to get the monthly total for the number of participants in all events. All events have a month, year, and day field.

 

I start by getting the totals this way:

 

$query ="SELECT event_month, sum(number) as total_events FROM events WHERE year = '".$year."' AND program_id = '".$_GET['id']."' GROUP BY event_month ";

 

Then I get the result of the query and loop through the rows:

 

$result = mysql_query($query)

echo "<table>\n<tr>\n";
while ($row = mysql_fetch_array($result)) {
$month = $year."-".$row['month']."-01";
$month_name = date('M', strtotime($month));
	echo "<td><div>".$month."</div>";
	echo "<div>".$row['total_events']."</div></td>";
	}
echo "</tr></table>\n\n\n"; 

 

This gets me close to what I want, which is a table with two rows, the name of the month on top and the total attendance for that month below. But I'd really like every month displayed--not just those for which there is data. So my output would be a table with 12 cells, Jan through Dec, and below each the total for that month, if it exists.

 

I imagine I ought to be using a for loop, but don't know where to begin. Any help would be appreciated. 

 

dcp

[mwookie]

I am not sure this is the best way to do this but you can do a join query on a table with all the month names.

 

Month Table

monthNum (Primary)

monthName

 

Query would look like:

 

$query ="SELECT monthName, sum(number) as total_events FROM events right outer joind monthTable on (monthTable.monthNum = total_events.event_month WHERE year = '".$year."' AND program_id = '".$_GET['id']."' GROUP BY monthName";

 

The right outer join make it show all the rows in the month table.

 

Hope this helps...

 

 

________________

"You cannot escape the responsibility of tomorrow by evading it today." Abraham Lincoln

Company – Projects ($1 Iraq Stock Photos – Sell Your Stock Images)

[dcp]

Thanks mwookie,

 

I like that approach for a number of reasons. But I'm still not getting all months from the "months" table. Here's the actual query I'm using:

 

SELECT months.id, months.name, sum(number) as total_events FROM pbb_events RIGHT OUTER JOIN months on (months.id = pbb_events.event_month) WHERE pbb_events.event_year = '2007' AND pbb_events.program_id = '156' GROUP BY months.name

 

It seems to be totaling correctly, but I get rows only where events have been entered.

[fenway]

Seriously? Right outer join? Try re-writing the query with left join.

[dcp]

Course I wouldn't be posting here if I had the answers. Unfortunately, this one isn't it either.

[SnowControl]

SELECT monthtable.monthNum , monthtable.monthName , sum(events.number) AS total_events , events.month FROM monthtable

LEFT JOIN events ON monthtable.monthNum = events.month

WHERE events.yeah='2007'

 

This one will do it for you. Only works for one year though.

 

 

$query="SELECT monthtable.monthNum , monthtable.monthName , sum(events.number) AS total_events , events.month FROM monthtable
LEFT JOIN events ON monthtable.monthNum = events.month
WHERE events.yeah='2007' ";
$result=mysql_query($query);
$num=mysql_num_rows($result); // Will of course be 12 rows, 1 per month


$i=0;
while ($num > $i) {

$month=mysql_result($result,$i,"monthtable.monthName");
$attendants=mysql_result($result,$i,"total_events");

echo "<strong>$month</strong> - $attendants participants   <br />";

$i++;
}