Average age (figuring from birthdate)

Grant Holmes · January 31, 2008

I've searched for help on this and found three different posts, but can't figure out my challenge from them.

I have been collecting birth dates in my table stored as "YYYY-MM-DD" I'm sucessfully displaying the birthdate as 12-12-2007 in the display of records using:

$Bdate = date("m-d-Y",strtotime("$Bbirthdate"));

I have some 13,000 records.

As I loop through the results to display records, I'd like to accumulate the total age of all the people in the records at a given time and then display the low, high and average age from that addition of ages.

Here's the total code of the page where I pull and can display birthdays if this helps you.

<?php include_once("security/SECsecurity.php"); ?>
<?php $DOCROOT = $_SERVER['DOCUMENT_ROOT'] ; ?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Birthday Club Members</TITLE>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
   <META NAME="GOOGLEBOT" CONTENT="NOARCHIVE">
    <META NAME="ROBOTS" CONTENT="NONE">
    <LINK REL="stylesheet" TYPE="text/css" HREF="text.css">
<script src="JS/sorttable.js"; ?></script>
<LINK REL="stylesheet" TYPE="text/css" HREF="JS/calendar-win2k-1.css">
<script type="text/javascript" src="JS/calendar.js"></script>
<script type="text/javascript" src="JS/calendar-en.js"></script>
<script type="text/javascript" src="JS/calendar-setup.js"></script>
</HEAD>
<?php
include("dbinfo.inc.php");
$tDay = date("m-d");
$fromDay = $_REQUEST["f_date"];
$toDay = $_REQUEST["t_date"];
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
if (!empty($_REQUEST["t_date"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where Event="Birthday" AND DATE_FORMAT(Cbirthdate,"%m-%d") >= "'.$fromDay.'" AND DATE_FORMAT(Cbirthdate,"%m-%d") <= "'.$toDay.'"';
} else if (!empty($_REQUEST["showall"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where Event="Birthday"';
} else if (!empty($_REQUEST["id"])) {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where Event="Birthday" AND id='.$_REQUEST["id"];  
} else {
  $query='SELECT *,DATE_FORMAT(Cbirthdate,"%m-%d") AS rbirthdate FROM birthdays where Event="Birthday" AND DATE_FORMAT(Cbirthdate,"%m-%d") = "'.$tDay.'"';
}

  

$result=mysql_query($query);

$num=mysql_numrows($result); 

mysql_close();

echo "<A name=\"top\"></A><b><center><H1>Birthday Club Members<FONT color=\"#ff0000\"><BR><I>By Referer</I></FONT></H1></b>";
?>
<!--  -->

<?php
if (!empty($_REQUEST["id"])) {
   echo "<center><FONT color=\"#ff0000\"><B>Record Updated</B></FONT></center>";
}
if (!empty($_REQUEST["del"])) {
   echo "<center><FONT color=\"#ff0000\"><B>Record Deleted</B></FONT></center>";
}
?>
<center><div style='width:100%; background-color:silver; text-align:right'>
  <?php SECShowAdminLink(); ?>
   
  <?php SECShowLogoutLink(); ?>  <A href="data.php">Main Data Page</A>  
</div></center>
<center><div style='width:100%; background-color:#ffe4c4; text-align:center'><BR>Today's birthdays have been pre-selected<BR><A href="birthdays2.php">View by Referred</A> | <A href="birthdays_active.php">Active Records Only</A> | <A href="birthdays_inactive.php">InActive Records Only</A><BR><BR></div></center>
<form name="form1" method="post">
<table border="0" cellspacing="2" cellpadding="2" width="800">
      <TR>
  	   <TD valign="top">
            <table cellspacing="0" cellpadding="0" style="border-collapse: collapse"><tr>
             <td><input type="text" name="f_date" id="f_date" readonly="1" /></td>
             <td><img src="JS/CalendarImage.gif" id="f_trigger" style="cursor: pointer; border: 1px solid red;" title="Date selector"
                  onmouseover="this.style.background='red';" onmouseout="this.style.background=''" /></td>
            </table>
            
            <script type="text/javascript">
                Calendar.setup({
                    inputField     :    "f_date",     // id of the input field
                    ifFormat       :    "%m-%d",      // format of the input field
                    button         :    "f_trigger",  // trigger for the calendar (button ID)
                    align          :    "Tl",           // alignment (defaults to "Bl")
                    singleClick    :    true
                });
            </script>
	   </TD>
	   	  	   <TD valign="top">
            <table cellspacing="0" cellpadding="0" style="border-collapse: collapse"><tr>
             <td><input type="text" name="t_date" id="t_date" readonly="1" /></td>
             <td><img src="JS/CalendarImage.gif" id="t_trigger" style="cursor: pointer; border: 1px solid red;" title="Date selector"
                  onmouseover="this.style.background='red';" onmouseout="this.style.background=''" /></td>
            </table>
            </form>
            
            <script type="text/javascript">
                Calendar.setup({
                    inputField     :    "t_date",     // id of the input field
                    ifFormat       :    "%m-%d",      // format of the input field
                    button         :    "t_trigger",  // trigger for the calendar (button ID)
                    align          :    "Tl",           // alignment (defaults to "Bl")
                    singleClick    :    true
                });
            </script>
	   </TD>	   
            <TD valign="top"><input type="submit" name="Submit" value="Submit"></TD>
            <TD valign="top"> <A href="birthdays.php?showall=1">Select ALL Birthdays</A></TD> 
	   <TD valign="top"> <A href="birthdays.php">Select Today</A></TD>		   
      </TR>	  
</TABLE>
<CENTER><P><?php echo "Showing: $fromDay to $toDay"; ?> </P></CENTER>
<center><div style='width:100%; background-color:#eee; text-align:right'>
  <?php SECShowAdminLink(); ?>
   
  <?php SECShowLogoutLink(); ?>
</div></center>
<table border="1" cellspacing="2" cellpadding="2" width="800" class="sortable">
<tr> 
<th width="10">DB #</th>
<th width="250">Name/Address</th>
<th width="100">Country</th>
<th width="200">E-mail</th>
<th>D.O.B.</th>
<TH valign="top" sortdir="desc">Submitted</TH>	
<th>Active</th>
</tr>

<?php
$i=0;
while ($i < $num) {
$Active=mysql_result($result,$i,"Active");
$firstname=mysql_result($result,$i,"Contact_Info_FirstName");
$lastname=mysql_result($result,$i,"Contact_Info_LastName");
$street=mysql_result($result,$i,"Contact_Info_StreetAddress");
$street2=mysql_result($result,$i,"Contact_Info_Address2");
$city=mysql_result($result,$i,"Contact_Info_City");
$state=mysql_result($result,$i,"Contact_Info_State");
$zip=mysql_result($result,$i,"Contact_Info_ZipCode");
$country=mysql_result($result,$i,"Contact_Info_Country");
$email=mysql_result($result,$i,"Contact_Info_Email");
$Cbirthdate=mysql_result($result,$i,"Cbirthdate");
$Bfirstname=mysql_result($result,$i,"Birthday_Info_FirstName");
$Blastname=mysql_result($result,$i,"Birthday_Info_LastName");
$Bstreet=mysql_result($result,$i,"Birthday_Info_StreetAddress");
$Bstreet2=mysql_result($result,$i,"Birthday_Info_Address2");
$Bcity=mysql_result($result,$i,"Birthday_Info_City");
$Bstate=mysql_result($result,$i,"Birthday_Info_State");
$Bzip=mysql_result($result,$i,"Birthday_Info_ZipCode");
$Bcountry=mysql_result($result,$i,"Birthday_Info_Country");
$Bemail=mysql_result($result,$i,"Birthday_Info_Email");
$Bbirthdate=mysql_result($result,$i,"Bbirthdate"); 
$id=mysql_result($result,$i,"id"); 
$Bdate = date("m-d-Y",strtotime("$Bbirthdate"));
$Cdate = date("m-d-Y",strtotime("$Cbirthdate"));
$Date=mysql_result($result,$i,"DateEntered");
?>
<?php
//Alternate Row Color- uses config color in SECsecurity.php
if($i % 2) 
	{ 
	echo "<TR bgcolor='$DarkRowColor'>";
	} 
else 
	{ 
	echo "<TR bgcolor='$LightRowColor'>";
	}
?>			
<td valign="top"><?php echo "$id"; ?></td>
<td valign="top"><STRONG><? echo "$firstname $lastname"; ?></STRONG><BR><? echo "$street  $street2<BR>$city, $state $zip<BR><B>  <SMALL>------------ Referred... -----------</SMALL></B><BR>-"; ?>
<? echo "$Bfirstname $Blastname"; ?><BR><? echo "$Bstreet  $Bstreet2<BR>$Bcity, $Bstate $Bzip"; ?></td>

<td valign="top"><CENTER><? echo "$country"; ?></CENTER><BR><BR><HR width="85%"><CENTER><? echo "$Bcountry"; ?></CENTER></td>
<td valign="top"><CENTER><a href="mailto:<? echo "$email"; ?>"><? echo "$email"; ?></a></CENTER><BR><BR><HR width="85%"><CENTER><a href="mailto:<? echo "$Bemail"; ?>"><? echo "$Bemail"; ?></a></CENTER></td>
<td valign="top"><? echo "$Cdate"; ?><BR><BR><BR><HR width="85%"><? echo "$Bdate"; ?></td>
<TD valign="top"><?php echo "$Date"; ?></TD>	
<td valign="top"><CENTER><?php    $checked = $Active ? "checked" : "";
echo "<input type=\"checkbox\" name=\"foo\" $checked>";
?>
 <BR><BR><a href='birthdays_edit.php?id=<? echo "$id"; ?>'>edit</a><BR><A href="#top"><IMG src="images/uparrow.gif" border="0" hspace="2" vspace="4" /></A><A href="#bottom"><IMG src="images/downarrow.gif" border="0" hspace="4" vspace="4" /></A></CENTER></TD>
</TD>
</tr>
<?php
++$i;
} 
echo "</table><P><A href=\"#top\">Back to top</A></P></B><BR>";
?>
//I'd like the display here if possible...
<BR><BR><BR>
           <?php include "include/footer.php"; ?>	
	  <a name="bottom"></a>
</BODY>
</html>

At the end of the page, I'd like to display Lowest age: XX Oldest age: XX Average Age: XX

If somebody wants extra credit, Number of members under 25:XX 25-35: XX 35-45: X 45+: XX

Stooney · January 31, 2008

Just a brief thought, what if you used ORDER BY and retrieve the birth dates from youngest to oldest, so the first element of the array (assuming you use mysql_fetch_array) would be the youngest, and the last element would be the oldest. Just a thought

Grant Holmes · January 31, 2008

Chris,

I know little PHP, and that thought, while it sounds good... I have no knowledge to implement.

Grant Holmes · January 31, 2008

Another point here... The results I'm seeking don't HAVE to be in this page. I'd be happy to have a page called DBAge.php or whatever that would ONLY display the desired results.

Styles2304 · January 31, 2008

Dunno about average . . . but here's low and high:

Low:

$query = "SELECT Bbirthdate FROM databasename ORDER BY Bbirthdate LIMIT 1";

$result = mysql_query($query,$link)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Bbirthdate'];
}

High:

$query = "SELECT Bbirthdate FROM databasename ORDER BY Bbirthdate DESC LIMIT 1";

$result = mysql_query($query,$link)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Bbirthdate'];
}

$link is what I store my database login info in.

aebstract · January 31, 2008

Pull all ages from your database in a while loop, and during each one just keep adding them, at the end you will divide your number by how many rows you returned. That'll give you the average.

Styles2304 · January 31, 2008

Well that's what I thought but won't it just give you a random number? If not . . . here's what he's talking about:

$BNumber = 0;
$BAverage = 0;

$query = "SELECT Bbirthdate FROM databasename ORDER BY Bbirthdate";

$result = mysql_query($query,$link)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Bbirthdate'];

  $BNumber++;
  $BTotal = $BTotal + $Bbirthdate;
}
  $BAverage = $BTotal / $BNumber;

aebstract · January 31, 2008

It wouldn't give you a random number.

5 + 5 + 5 = 15 / 3 = 5

Make sure you set BTotal to 0 at the top also.

Styles2304 · January 31, 2008

Ah yeah . . . and no, I know how average works . . . it just seems like it would give you a number instead of a date . . . that make sense? I dunno though.

Grant Holmes · January 31, 2008

Hey gang, thanks for the ideas. I do want just the age, not date. So I do want to know something like:

Lowest age: 27

Oldest age: 56

Average: 36.4

I'll look at these this morning and get back.

Styles2304 · January 31, 2008

ah ok well then that's MUCH easier. A little more code but it at least make sense to me.

<?php
//Todays Date
$TDate = date("Y-m-d");

//Other Variables (in order) Age, Lowest Age, Highest Age, Average Age, Total of Ages, and Number of Birthdays.
$Age = 0;
$LAge = 0;
$HAge = 0;
$AAge = 0;
$ATotal = 0;
$BNumber = 0;

//Finds the Lowest Age
$query = "SELECT Bbirthdate FROM databasename ORDER BY Bbirthdate LIMIT 1";

$result = mysql_query($query,$link)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Bbirthdate'];
}
$LAge = $TDate - $Bbirthdate;

echo 'The Lowest Age is: ' . $LAge . '<br>';

//Finds The Highest Age
$query = "SELECT Bbirthdate FROM databasename ORDER BY Bbirthdate DESC LIMIT 1";

$result = mysql_query($query,$link)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Bbirthdate'];
}

$HAge = $TDate - $Bbirthdate;

echo 'The Highest Age is: ' . $HAge . '<br>';


//Finds the Average Age
$query = "SELECT Bbirthdate FROM databasename ORDER BY Bbirthdate";

$result = mysql_query($query,$link)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Bbirthdate'];

  $Age = $TDate - $Bbirthdate;

  $ATotal = $ATotal + $Age;
  $BNumber++;
}
  $AAge = $ATotal / $BNumber;

echo 'And The Average Age is: ' . $AAge;

?>

I'm 100% positive that there are some syntax errors in there lol but just post them here and I'll help you with them.

Grant Holmes · January 31, 2008

Styles... WOW, thanks! I was just about to post comments to the other posts. This is cool!!

If I can get it working!!

I made some edits... I'm already opening the DB, so removed your $link. I also need information from the "Cbirthdate" field, not "Bbirthdate", but you obviously didn't know that! And "birthdays" is the name of my table.

So I did these edits. When I try the page, NOTHING shows on the page. Zero, zip, nada, blank;

<?php include_once("security/SECsecurity.php"); ?>
<?php $DOCROOT = $_SERVER['DOCUMENT_ROOT'] ; ?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>DB info</TITLE>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
   <META NAME="GOOGLEBOT" CONTENT="NOARCHIVE">
    <META NAME="ROBOTS" CONTENT="NONE">
    <LINK REL="stylesheet" TYPE="text/css" HREF="text.css">
</HEAD>
<BODY>
<a name="top"></a>
<center>
<?php echo "<A name=\"top\"></A><b><center><H1>\"<I>Listener Info</I>\":</H1>"; ?>
<div style='width:100%; background-color:silver; text-align:right'>
  <?php SECShowAdminLink(); ?>
   
  <?php SECShowLogoutLink(); ?>  <A href="data.php">Main Data Page</A>  
</div>
<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

////// (edited) Code from Styles2304 starts here

$query = "SELECT Cbirthdate FROM birthdays ORDER BY Cbirthdate LIMIT 1";
<?php
//Todays Date
$TDate = date("Y-m-d");

//Other Variables (in order) Age, Lowest Age, Highest Age, Average Age, Total of Ages, and Number of Birthdays.
$Age = 0;
$LAge = 0;
$HAge = 0;
$AAge = 0;
$ATotal = 0;
$BNumber = 0;

//Finds the Lowest Age
$query = "SELECT Cbirthdate FROM birthdays ORDER BY Cbirthdate LIMIT 1";

$result = mysql_query($query)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Cbirthdate'];
}
$LAge = $TDate - $Cbirthdate;

echo 'The Lowest Age is: ' . $LAge . '<br>';

//Finds The Highest Age
$query = "SELECT Cbirthdate FROM birthdays ORDER BY Cbirthdate DESC LIMIT 1";

$result = mysql_query($query)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Cbirthdate'];
}

$HAge = $TDate - $Cbirthdate;

echo 'The Highest Age is: ' . $HAge . '<br>';


//Finds the Average Age
$query = "SELECT Cbirthdate FROM birthdays ORDER BY Cbirthdate";

$result = mysql_query($query)
            or die(mysql_error());
            
while ($row = mysql_fetch_array($result)) {
  $Bbirthdate = $row['Cbirthdate'];

  $Age = $TDate - $Cbirthdate;

  $ATotal = $ATotal + $Age;
  $BNumber++;
}
  $AAge = $ATotal / $BNumber;

echo 'And The Average Age is: ' . $AAge;

?>
<BR><BR><BR><P><A href=\"#top\">Back to top</A></P></B><BR>
<BR><BR><BR>
           <?php include "include/footer.php"; ?>	
	  <a name="bottom"></a>
</BODY>
</html>

obsidian · January 31, 2008

Just a thought, have you considered letting MySQL do your work for you instead of having to do the while thing in PHP. This will get you the age of your people:

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(Cbirthdate)), '%Y') + 0 AS age FROM birthdays;

That is simply a list of ages, but you may be able to just call AVG on the function and be done with it:

SELECT AVG(DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(Cbirthdate)), '%Y') + 0) AS avg_age FROM birthdays;

The Little Guy · January 31, 2008

First do this (calculate age):

http://phpsnips.com/snippet.php?id=7

Next you can output mean(average), median(middle value), mode(most common), Range(High/low):

http://phpsnips.com/snippet.php?id=45

Grant Holmes · January 31, 2008

Obsidian, I've considered lots of things. However, KNOWLEDGE of php is NOT a strong suit to say the least, so my considerations are limited to subjects outside PHP.

Once I see code, I can sometimes figure it out and have successfully edited other stuff, but I am such a noob. So while I'm sure your solution has merit, I don't know how to implement it.

Styles solution will give me everything I need, IF I can get it to work.

Thanks to all. I can't tell you how great it is to have a resource like this.

obsidian · January 31, 2008

First do this (calculate age):

http://phpsnips.com/snippet.php?id=7

Next you can output mean(average), median(middle value), mode(most common), Range(High/low):

http://phpsnips.com/snippet.php?id=45

Your snippet in your age calculator won't be accurate. As you have it, if the day difference is greater than 0, you are assuming their birthday has not passed. The problem is that you are using an OR with that, so if the date is December 30, and their birthday is January 25, the day comparison will flag true, and you will decrement the year, even though the birthday has passed some 11 months prior.

obsidian · January 31, 2008

Obsidian, I've considered lots of things. However, KNOWLEDGE of php is NOT a strong suit to say the least, so my considerations are limited to subjects outside PHP.

Once I see code, I can sometimes figure it out and have successfully edited other stuff, but I am such a noob. So while I'm sure your solution has merit, I don't know how to implement it.

Completely understandable. All I posted was actual MySQL queries. Basically, the first one, if executed, would return a record set with a single column called "age" that would contain the ages of everyone in the database. Then, all you would have to do is loop over the results and average them:

<?php
$sql = mysql_query("SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(Cbirthdate)), '%Y') + 0 AS age FROM birthdays");
$rowCount = mysql_num_rows($sql);
$total = 0;
for ($i = 0; $i < $rowCount; $i++)
{
  $total += mysql_result($sql, $i, 'age');
}

echo "Average age is: " . number_format(($total / $rowCount), 2);
?>

The second query is just an attempt to see if MySQL would allow you to average the other function call:

<?php
$sql = mysql_query("SELECT AVG(DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(Cbirthdate)), '%Y') + 0) AS avg_age FROM birthdays;");
echo "Average age is: " . mysql_result($sql, 0, 'avg_age');
?>

I'm interested to know especially if the second method works

Grant Holmes · January 31, 2008

Obsidian, I created a different page with your code. Here are the results of both versions on the same page:

Average age is: 33.15

Average age is: 51.2100

So, somewhere there is a problem!! I also don't know how to create the loop.

Is there no way to 'fix' STYLEs code above? Just curious.

The Little Guy · January 31, 2008

SELECT name, birth, CURDATE(), AVG((YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5))) AS age FROM people;

MYSQL age calculator: http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

obsidian · January 31, 2008

Is there no way to 'fix' STYLEs code above? Just curious.

Well, I'm not sure exactly what he's doing, but it doesn't look like that will correctly calculate birthdays (sorry I don't have time to read everything in depth today). Here's something that might help, though.

<?php
function getAge($birthday){
if (($cleaned = strtotime($birthday)) === FALSE)
{
  return false; // Not a readable format
}

list($year, $month, $day) = explode('-', date('Y-m-d', $cleaned));
$age = date('Y') - $year;

if (mktime(0,0,0,$month,$day,date('Y')) < mktime())
{
  // Birthday hasn't passed, so subtract one year from age
  $age--;
}

return $age;
}

$sql = mysql_query("SELECT Cbirthday FROM birthdays");
$num = mysql_num_rows($sql);
$total = 0;
for ($i = 0; $i < $num; $i++)
{
  if (($age = getAge(mysql_result($sql, $i, 'Cbirthday'))) !== FALSE)
  {
    $total += $age;
  }
  else
  {
    $num--; // Couldn't get age, so remove from average
  }
}

echo "Average age is: " . number_format(($total / $num), 2);
?>

Hope that helps.

Grant Holmes · January 31, 2008

Obsidian, Here's what that got me as a result:

Average age is: 0.00

The Little Guy · January 31, 2008

I finally got a awesome QUERY!!!

Tested on my table, and this works PERFECT!

<?php
include 'db.php';
$query = "(SELECT COUNT(birth) as tPeople, SUM((YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5))) as totalAGES FROM age)";
$sql = mysql_query($query);

$row = mysql_fetch_array($sql);

echo 'average age: '.($row['totalAGES']/$row['tPeople']); 
?>

Grant Holmes · January 31, 2008

LG, to make sure I test this correctly, would you mind modifying with my fields, etc?

DB: birthdays

Field: Cbirthdate

The Little Guy · January 31, 2008

<?php
include 'db.php';
$query = "(SELECT COUNT(Cbirthdate) as tPeople, SUM((YEAR(CURDATE())-YEAR(Cbirthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Cbirthdate,5))) as totalAGES FROM birthdays)";
$sql = mysql_query($query);

$row = mysql_fetch_array($sql);

echo 'average age: '.($row['totalAGES']/$row['tPeople']); 
?>

I assume by DB, you mean Table, other wise change the "FROM birthdays" to "FROM myTable".

AND the FORMAT of Cbirthdate MUST equal a "YYYY-MM-DD" format to work otherwise more revamping must be done.

Grant Holmes · January 31, 2008

LG: your assumption on db vs. table was correct. my bad. sorry.

The birthdate is stored as YYYY-MM-DD

The result of your query was:

average age: 637.63463368221 hmmmm... me thinks this may not be correct?

==

I just thought of something else. There may be blank records due to an import we did. While we should likely check for blank records, that would drive the number lower, wouldn't it?

Sign In

Average age (figuring from birthdate)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information