Counting group members

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
tobywimberley
Forum Newbie
Posts: 2
Joined: Wed Aug 10, 2011 10:31 am

Counting group members

Post by tobywimberley »

Good morning all,
I hope this is the correct place to post this question...

I am stumped on what is probably a simple issue to resolve for a non newb.
I have been asked to add a feature to a pre-existing site. The feature allows for registration, which I can handle fine, however, they want there to be registration periods and a limit of 14 per group. My intention is to add a tag that lists how many spots are left in each group. Ive added the fields "_count_allowance" and "_count_members" in mysql. The field _count_allowance is the max allowed (14), while _count_members is a sequential number from 1-14. I was going to subtract members form allowance to determine the spots available.
There is a page already that lists the groups and I was simply going to add a script to handle the math and echo the output but I cant figure out how to tie the variable $roomleft back to the group its associated with.
Is there such of a thing as a calculation field in Mysql like there is in other databases? If so, I could let mysql handle the calculation and echo it with everything else.

Ive attached a screenshot of the group list part of the page

Thanks for your help!
Toby


Here is the page code

Code: Select all

$sqlsunday="SELECT ID, name, demo, facilitator, pic, bio, day, time, place, email, phone, website, _count_allowance, _count_members FROM lifegroups WHERE day='Sunday' ORDER BY name;";
$resultsunday=mysql_query($sqlsunday) or die(mysql_error());
$numsun=mysql_num_rows($resultsunday);

$sqlwed="SELECT ID, name, demo, facilitator, pic, bio, day, time, place, email, phone, website, _count_allowance, _count_members FROM lifegroups WHERE day='Wednesday' ORDER BY name;";
$resultwed=mysql_query($sqlwed) or die(mysql_error());
$numwed=mysql_num_rows($resultwed);

$sqlfri="SELECT ID, name, demo, facilitator, pic, bio, day, time, place, email, phone, website, _count_allowance, _count_members FROM lifegroups WHERE day='Friday' ORDER BY name;";
$resultfri=mysql_query($sqlfri) or die(mysql_error());
$numfri=mysql_num_rows($resultfri);

$querycount = "SELECT COUNT(groupid) FROM members"; 
	 
$resultcount = mysql_query($querycount) or die(mysql_error());




?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>The Heights Fellowship - LIFE Groups</title>
<link href="../req/style.css" rel="stylesheet" type="text/css" />
</head>

<body><div id="content">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="920" height="85"><?php include ("../include/top.php"); ?></td>
  </tr>
  <tr>
    <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td valign="top" width="46"><div id="menu"><?php //hello
		include ("../include/menu.php"); ?></div></td>
        <td valign="top" width="116" class="bordertop borderbottom borderleft bgwhite">&nbsp;</td>
        <td valign="top" class="bordertop borderbottom borderright bgwhite bodymargin"><table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td class="borderbottom"><img src="images/thflife.png" alt="thfLIFE" /></td>
            <td rowspan="2" valign="middle" align="right"><img src="images/lifelogo.png" alt="LIFE Groups" /></td>
          </tr>
          <tr>
            <td height="150" valign="top" class="paddingtop"><b>LIFE Groups.  Are you game?</b><br />What are LIFE Groups?
At The Heights Fellowship, we value community. While we did not author it, we often say that a church has to grow larger and smaller at the same time. We achieve this through our LIFE Group Ministry.
<br /><br />
Our LIFE Groups are built around the 242 principle. This principle comes from the New Testament book of Acts:<Br /><br />

<i>They devoted themselves to the apostles' teaching and to the fellowship, to the breaking of bread and to prayer.</i>  Acts 2:42 (niv)
</td>
            </tr>
          <tr>
            <td height="75" colspan="2" valign="top" class="paddingtop">
            
The 242 Principle is Fellowship Focused, Study Centered, and Prayer and People Passioned.  Notice that they devoted themselves to: <br />
   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...the apostles' teaching - the Study of Scripture.<br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...the fellowship – the people united by a common cause.<br />
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...the breaking of bread – the sharing of everyday life together.<br />
     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;...prayer - That's going to the throne of God on behalf of ourselves and those around us.<br /><br />

THF LIFE Groups provide people the opportunity to intimately be a part of a close-knit group of Christ-followers. It is a safe-zone to share life experiences, to search for Biblical answers, and to pursue spiritual projects together. In a LIFE Group, a person will have a support group for times in life where they're hurting, lonely, or rejoicing.<br /><br />
            <b>What About My Children?</b><br />
THF wants the LIFE Group experience to be as "hassle free" as possible. So we make childcare available on Sunday nights. Childcare is by reservation and is free of charge (children ages birth-sixth grade) for parents attending our Sunday night LIFE groups. Reservations are due the Wednesday prior to the LIFE Group meeting. You can email Mika McDaniel for reservations and questions regarding LIFE Group Childcare or call the church (806-771-8415) and leave the information.</td>
          </tr>
         
		  <tr>
            <td height="75" colspan="2" valign="top" class="paddingtop"><span class="text18"><b>Sunday Groups</b></span>
              <table width="100%" border="0" cellspacing="0" cellpadding="0" class="bordertop">
   <?php $i=0;  
   
		  while ($numsun>$i) { ?>
   <tr>
    <td class="groupbox" width="370"><table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr></tr>
  <tr>
    <td class="text14"><b><?php if (strlen(mysql_result($resultsunday,$i,website))>0) { ?><a href="<?php echo mysql_result($resultsunday,$i,website); ?>" target="_blank"><?php } echo stripslashes(mysql_result($resultsunday,$i,name)); ?>&nbsp;<?php if (strlen(mysql_result($resultsunday,$i,website))>0) { ?><img src="images/site.png" border="0" /></a><?php }?></b></td>
  </tr>
  <tr>
  <td>
   Current members <?php echo stripslashes(mysql_result($resultsunday,$i,_count_members)); ?> Room Left <?php echo $roomleft;?>
  </td>
  </tr>
  <tr>
    <td class="paddingleft">Demo: <?php echo stripslashes(mysql_result($resultsunday,$i,demo)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Facilitators: <?php echo stripslashes(mysql_result($resultsunday,$i,facilitator)); ?></td>
  </tr>
  
  <tr>
    <td class="paddingleft aboutbox"><strong>About:</strong><img src="images/biopics/<?php echo stripslashes(mysql_result($resultsunday,$i,pic)); ?>.jpg" align="right" class="aboutpic" />  <?php echo stripslashes(mysql_result($resultsunday,$i,bio)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Time: <?php echo stripslashes(mysql_result($resultsunday,$i,time)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">E-Mail: <a href="mailto:<?php echo stripslashes(mysql_result($resultsunday,$i,email)); ?>"><?php echo stripslashes(mysql_result($resultsunday,$i,email)); ?></a></td>
  </tr><?php if (strlen(mysql_result($resultsunday,$i,phone))>0) { ?>
  <tr>
    <td class="paddingleft">Phone: <?php echo stripslashes(mysql_result($resultsunday,$i,phone)); ?></td>
  </tr><?php } ?>
</table></td><?php $i++; if ($numsun>$i) {?>
    <td class="groupbox paddingleft"><table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr></tr>
  <tr>
    <td class="text14"><b><?php if (strlen(mysql_result($resultsunday,$i,website))>0) { ?><a href="<?php echo mysql_result($resultsunday,$i,website); ?>"><?php } echo stripslashes(mysql_result($resultsunday,$i,name)); ?>&nbsp;<?php if (strlen(mysql_result($resultsunday,$i,website))>0) { ?><img src="images/site.png" border="0" /></a><?php }?></b></td>
  </tr>
  <tr>
  <td>
   Current members <?php echo stripslashes(mysql_result($resultsunday,$i,_count_members)); ?> Room Left <?php echo $roomleft;?>
  </td>
  </tr>
  <tr>
    <td class="paddingleft">Demo: <?php echo stripslashes(mysql_result($resultsunday,$i,demo)); ?></td>
  </tr>
   <tr>
    <td class="paddingleft">Facilitators: <?php echo stripslashes(mysql_result($resultsunday,$i,facilitator)); ?></td>
  </tr>
  
  <tr>
    <td class="paddingleft aboutbox"><strong>About:</strong><img src="images/biopics/<?php echo stripslashes(mysql_result($resultsunday,$i,pic)); ?>.jpg" align="right" class="aboutpic" /> <?php echo stripslashes(mysql_result($resultsunday,$i,bio)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Time: <?php echo stripslashes(mysql_result($resultsunday,$i,time)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">E-Mail: <a href="mailto:<?php echo stripslashes(mysql_result($resultsunday,$i,email)); ?>"><?php echo stripslashes(mysql_result($resultsunday,$i,email)); ?></a></td>
  </tr><?php if (strlen(mysql_result($resultsunday,$i,phone))>0) { ?>
  <tr>
    <td class="paddingleft">Phone: <?php echo stripslashes(mysql_result($resultsunday,$i,phone)); ?></td>
  </tr><?php } ?>
</table></td>
  </tr><?php } $i++; } ?>
</table>
</td>
          </tr>
          <tr>
            <td height="75" colspan="2" valign="top" class="paddingtop"><span class="text18"><b>Wednesday Groups</b></span>
              <table width="100%" border="0" cellspacing="0" cellpadding="0" class="bordertop">
   <?php $i=0;
		  while ($numwed>$i) { ?>
   <tr>
    <td class="groupbox" width="370"><table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr></tr>
  <tr>
    <td class="text14"><b><?php if (strlen(mysql_result($resultwed,$i,website))>0) { ?><a href="<?php echo mysql_result($resultwed,$i,website); ?>" target="_blank"><?php } echo stripslashes(mysql_result($resultwed,$i,name)); ?>&nbsp;<?php if (strlen(mysql_result($resultwed,$i,website))>0) { ?><img src="images/site.png" border="0" /></a><?php }?></b></td>
  </tr>
  <tr>
  <td>
   Current members <?php echo stripslashes(mysql_result($resultwed,$i,_count_members)); ?> Room Left <?php echo $roomleft;?>
  </td>
  </tr>
  <tr>
    <td class="paddingleft">Demo: <?php echo stripslashes(mysql_result($resultwed,$i,demo)); ?></td>
  </tr>
   <tr>
    <td class="paddingleft">Facilitators: <?php echo stripslashes(mysql_result($resultwed,$i,facilitator)); ?></td>
  </tr>
  
  <tr>
    <td class="paddingleft aboutbox"><strong>About:</strong> <img src="images/biopics/<?php echo stripslashes(mysql_result($resultfri,$i,pic)); ?>.jpg" align="right" class="aboutpic" /><?php echo stripslashes(mysql_result($resultwed,$i,bio)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Time: <?php echo stripslashes(mysql_result($resultwed,$i,time)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">E-Mail: <a href="mailto:<?php echo stripslashes(mysql_result($resultwed,$i,email)); ?>"><?php echo stripslashes(mysql_result($resultwed,$i,email)); ?></a></td>
  </tr><?php if (strlen(mysql_result($resultwed,$i,phone))>0) { ?>
  <tr>
    <td class="paddingleft">Phone: <?php echo stripslashes(mysql_result($resultwed,$i,phone)); ?></td>
  </tr><?php } ?>
</table></td><?php $i++; if ($numwed>$i) {?>
    <td class="groupbox paddingleft"><table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr></tr>
  <tr>
    <td class="text14"><b><?php if (strlen(mysql_result($resultwed,$i,website))>0) { ?><a href="<?php echo mysql_result($resultwed,$i,website); ?>"><?php } echo stripslashes(mysql_result($resultwed,$i,name)); ?>&nbsp;<?php if (strlen(mysql_result($resultwed,$i,website))>0) { ?><img src="images/site.png" border="0" /></a><?php }?></b></td>
  </tr>
  <tr>
  <td>
   Current members <?php echo stripslashes(mysql_result($resultwed,$i,_count_members)); ?> Room Left <?php echo $roomleft;?>
  </td>
  </tr>
  <tr>
    <td class="paddingleft">Demo: <?php echo stripslashes(mysql_result($resultwed,$i,demo)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Facilitators: <?php echo stripslashes(mysql_result($resultwed,$i,facilitator)); ?></td>
  </tr>
  
  <tr>
    <td class="paddingleft aboutbox"><strong>About:</strong> <img src="images/biopics/<?php echo stripslashes(mysql_result($resultfri,$i,pic)); ?>.jpg" align="right" class="aboutpic" /><?php echo stripslashes(mysql_result($resultwed,$i,bio)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Time: <?php echo stripslashes(mysql_result($resultwed,$i,time)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">E-Mail: <a href="mailto:<?php echo stripslashes(mysql_result($resultwed,$i,email)); ?>"><?php echo stripslashes(mysql_result($resultwed,$i,email)); ?></a></td>
  </tr><?php if (strlen(mysql_result($resultwed,$i,phone))>0) { ?>
  <tr>
    <td class="paddingleft">Phone: <?php echo stripslashes(mysql_result($resultwed,$i,phone)); ?></td>
  </tr><?php } ?>
</table></td>
  </tr><?php } $i++; } ?>
</table>
</td>
          </tr>
          <tr>
            <td height="75" colspan="2" valign="top" class="paddingtop"><span class="text18"><b>Friday Groups</b></span>
              <table width="100%" border="0" cellspacing="0" cellpadding="0" class="bordertop">
   <?php $i=0;
		  while ($numfri>$i) { ?>
   <tr>
    <td class="groupbox" width="370"><table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td class="text14"><b><?php if (strlen(mysql_result($resultfri,$i,website))>0) { ?><a href="<?php echo mysql_result($resultfri,$i,website); ?>" target="_blank"><?php } echo stripslashes(mysql_result($resultfri,$i,name)); ?>&nbsp;<?php if (strlen(mysql_result($resultfri,$i,website))>0) { ?><img src="images/site.png" border="0" /></a><?php }?></b></td>
  </tr>
  <tr>
  <td>
   Current members <?php echo stripslashes(mysql_result($resultfri,$i,_count_members)); ?> Room Left <?php echo $roomleft;?>
  </td>
  </tr>
  <tr>
    <td class="paddingleft">Demo: <?php echo stripslashes(mysql_result($resultfri,$i,demo)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Facilitators: <?php echo stripslashes(mysql_result($resultfri,$i,facilitator)); ?></td>
  </tr>
  
  <tr>
    <td class="paddingleft aboutbox"><strong>About:</strong> <img src="images/biopics/<?php echo stripslashes(mysql_result($resultsunday,$i,pic)); ?>.jpg" align="right" class="aboutpic" /><?php echo stripslashes(mysql_result($resultfri,$i,bio)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Time: <?php echo stripslashes(mysql_result($resultfri,$i,time)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">E-Mail: <a href="mailto:<?php echo stripslashes(mysql_result($resultfri,$i,email)); ?>"><?php echo stripslashes(mysql_result($resultfri,$i,email)); ?></a></td>
  </tr><?php if (strlen(mysql_result($resultfri,$i,phone))>0) { ?>
  <tr>
    <td class="paddingleft">Phone: <?php echo stripslashes(mysql_result($resultfri,$i,phone)); ?></td>
  </tr><?php } ?>
</table></td><?php $i++; if ($numfri>$i) {?>
    <td class="groupbox paddingleft"><table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr></tr>
  <tr>
    <td class="text14"><b><?php if (strlen(mysql_result($resultfri,$i,website))>0) { ?><a href="<?php echo mysql_result($resultfri,$i,website); ?>"><?php } echo stripslashes(mysql_result($resultfri,$i,name)); ?>&nbsp;<?php if (strlen(mysql_result($resultfri,$i,website))>0) { ?><img src="images/site.png" border="0" /></a><?php }?></b></td>
  </tr>
    <tr>
  <td>
   Current members <?php echo stripslashes(mysql_result($resultfri,$i,_count_members)); ?> Room Left <?php echo $roomleft;?>
  </td>
  </tr>
  <tr>
    <td class="paddingleft">Demo: <?php echo stripslashes(mysql_result($resultfri,$i,demo)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Facilitators: <?php echo stripslashes(mysql_result($resultfri,$i,facilitator)); ?></td>
  </tr>
  
  <tr>
    <td class="paddingleft aboutbox"><strong>About:</strong> <img src="images/biopics/<?php echo stripslashes(mysql_result($resultsunday,$i,pic)); ?>.jpg" align="right" class="aboutpic" /><?php echo stripslashes(mysql_result($resultfri,$i,bio)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">Time: <?php echo stripslashes(mysql_result($resultfri,$i,time)); ?></td>
  </tr>
  <tr>
    <td class="paddingleft">E-Mail: <a href="mailto:<?php echo stripslashes(mysql_result($resultfri,$i,email)); ?>"><?php echo stripslashes(mysql_result($resultfri,$i,email)); ?></a></td>
  </tr><?php if (strlen(mysql_result($resultfri,$i,phone))>0) { ?>
  <tr>
    <td class="paddingleft">Phone: <?php echo stripslashes(mysql_result($resultfri,$i,phone)); ?></td>
  </tr><?php } ?>
</table></td>
  </tr><?php } $i++; } ?>
</table>
</td>
          </tr>
        </table></td>
      </tr>
      
    </table></td>
  </tr>
  <tr>
    <td class="bodymargin" align="center"><?php include ("../include/bottom.php"); ?></td>
  </tr>
</table>
</div>
<?php include ("../req/google.php"); ?></body></html>
Attachments
page.png
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Counting group members

Post by califdon »

You (almost) never want to store computed values, like how many members are in a group! That should be calculated by a query each time you need to use it. Otherwise you are just asking for trouble when something unexpected happens and suddenly your stored data is wrong. A query that uses COUNT() and GROUP BY will easily produce the accurate numbers whenever you need them.
User avatar
tobywimberley
Forum Newbie
Posts: 2
Joined: Wed Aug 10, 2011 10:31 am

Re: Counting group members

Post by tobywimberley »

califdon wrote:You (almost) never want to store computed values, like how many members are in a group! That should be calculated by a query each time you need to use it. Otherwise you are just asking for trouble when something unexpected happens and suddenly your stored data is wrong. A query that uses COUNT() and GROUP BY will easily produce the accurate numbers whenever you need them.
Awesome! thanks for your reply!
That being said, based on the existing code I posted earlier how would I go about coding it so that it shows the total members in each group? there isn't a variable that I can use in a WHERE clause the way it is written now... Would I need to redo the entire page?

Thanks again for your help.
Toby
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Counting group members

Post by califdon »

I'm having trouble following your script code. I don't think it's an efficient way to do what you seem to be trying to achieve and it uses some PHP MySQL syntax that I never use and am therefore not very familiar with, namely, all the mysql_result() functions. If you check the PHP Manual http://php.net/manual/en/function.mysql-result.php, you will note the recommendation to use more efficient functions. This is further emphasized in the W3Schools tutorial on that function: http://www.w3schools.com/php/func_mysql_result.asp.

No doubt it is possible to make some changes in your existing script and make it work, but if it were me, I would rewrite it, using mysql_query() and mysql_fetch_assoc() or one of the similar fetch functions.

In any case, you don't need either of the 2 new fields you added, _count_allowance and _count_members. They should be removed from your table. The "allowance" is going to be 14 for all groups, I believe you said, so you don't need to store that, just declare a variable equal to that value in your script. Also if that value later changes to, say, 12, all you need to do is change one byte in your script! And you can use a query like the following to extract the current number of members in each group:

Code: Select all

$membercountquery = "SELECT groupID, COUNT(memberID) AS groupcount FROM memberstablename GROUP BY groupID";
(of course, substituting the names of your table and fields)

You may need to fetch the results of the above query into a PHP associative array for use in your output. The contents of the array would be something like this:

Code: Select all

(groupID) (count)
  207     12
  184      7
  233     10
which would create essentially a lookup table--you would use the groupID as the index, to show the count value (or, equally easy, 14 minus the count value).

I think it may be possible to do everything in one query, but I would have to know more about your table structures.
Post Reply