[SOLVED] need count of rows that have certain value in venue_id

[bradkenyon]

I have two tables, one that contains volunteers, and one that contains venues. Volunteers are assigned one venue each.

 

The id of the venues table (venues.id) is placed within the volunteers table in the venue_id column (volunteers.venue_id).

 

I know I could get a count of of how many matching values are in the volunteers.venue_id column by

SELECT venue_id, COUNT(*) FROM volunteers GROUP BY venue_id;

 

Why I want to do this: so the user can go in and see how many volunteers are assigned to each venue.

 

table: volunteers

columns: id, name, venue_id

 

table: venues

columns: id, venue_name

 

volunteers.venue_id = venues.id

 

I know this would be a join statement of some sort so it will get a count of each venue, then match up volunteers.venue_id to venues.id and print out the venues.venue_name along with the count.

 

Any help would be appreciated - Thank you.

[xtopolis]

SELECT ven.venue_name as 'Venue', COUNT(vol.id) as 'Number Of Volunteers' FROM venues ven
JOIN volunteers vol ON (ven.id=vol.venue_id)
GROUP BY ven.venue_name
ORDER BY ven.venue_name ASC

 

[pre]

Venue                  Number Of Volunteers

Charitable Event                  4

Recycle A Lot                    1

Save The World                    3

[/pre]

 

Obviously if you have volunteers attending more than one event, you will need a lookup table.  That can easily be modified into the statement using the same method :3

[bradkenyon]

Another question, how would I go about printing out these values to the web page.

 

I imagine I would have to loop thru and print each venue name, then pull the count for that venue and print that along side the venue name.

 

Thanks for the help, anymore would be greatly appreciate, thanks again!

[xtopolis]

if you have the statement above:

 

<?php
$sql = "my sql statement post above"
$result = mysql_query($sql);

while(list($name,$vols) = mysql_fetch_array($result)){
  echo "<p>For $name, $vols volunteer(s) will be assisting.</p>";
}
?>

[bradkenyon]

I tried this and nothing is printing, could anyone see what may be wrong with this.

 

<?php
$sql = "SELECT venues.venue_name as 'Venue', COUNT(volunteers_2009.id) as 'Number Of Volunteers' FROM venues ven JOIN volunteers_2009 vol ON (venues.id=volunteers_2009.venue_id) GROUP BY venues.venue_name ORDER BY venues.venue_name ASC";

$result = mysql_query($sql);

while(list($name,$vols) = mysql_fetch_array($result)) {
print '<p>'.$name.': '.$vols.'</p>';
} ?>

 

[bradkenyon]

Didn't know you give it nicknames, such as vol and ven, thank you!

 

It is fixed now.

[xtopolis]

They always teach you to program lazily.  Do it right, and do it efficiently.

 

Glad it works, topic solved this bad boy (lower left corner, button)