Average age (figuring from birthdate)

The Little Guy · January 31, 2008

HMMM... mine comes out exact... maybe you have invalid birth dates? did you validate that the given birth date is valid, and that the person is not 1000 years old?

My Test: http://tzfiles.com/bday.php

Grant Holmes · January 31, 2008

If there is an issue, it was in the import. I have pull down selections for the m/d/y to avoid stupid entries, but who knows with the import. I'll look into that.

Question then: If I search for "NULL" records (to start this)... I have ROWS of NULL in all fields. I should delete those to clean things up first, right?

Is there then an easy way to look for entries in Cbirthdate, say beyond 60 years old that I can pick through?

Styles2304 · January 31, 2008

<?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)) {
////////////Old Code:  $Bbirthdate = $row['Cbirthdate']; With your changed should read:
$Cbirthdate = $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)) {
//////////Old Code: $Bbirthdate = $row['Cbirthdate']; With changes should read:
$Cbirthdate = $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)) {
///////// Old Code: $Bbirthdate = $row['Cbirthdate']; With changes should read:
  $Cbirthdate = $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>

If you wouldn't mind . . . would you give my code a shot again and at least give me the satisfaction of knowing if it works? lol hope this helps either way!

The Little Guy · January 31, 2008

I noticed, that if there were invalid dates (birth dates) that it would still add them to the total...

so say there are 4 entries in the database

and one of the entries is invalid: 1966-45-23

since 45 isn't a actual month, it will still say it found 4 entries.

so then it is taking the four entries and dividing them by the sum of the 3 valid entries, giving an invalid average.

does that make sense?

So... I would check to make sure all the entries are valid

The Little Guy · January 31, 2008

Try and run this:

http://tzfiles.com/validbd.php

Just run the php part.

Grant Holmes · January 31, 2008

LG: Thanks for the cool script. I'll test that.

Styles: Tried your new code. Still getting a blank page

Grant Holmes · January 31, 2008

Sure wish they allowed a longer period of time for edits....

I tried the "valid rows" script. I get a bunch of returns, but they all say, "Valid" and there is no row # return. Here's what I entered:

<?php
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
    $sql = mysql_query("SELECT * from birthdays");
    while($row = mysql_fetch_array($sql)){
        list($y,$m,$d) = explode("-",$row['birth']);
        if($m > 12){
            echo '<span style="color:red">Invalid Row: '.$row['birth'].'</span><br>';
        }elseif($d > 31){
            echo '<span style="color:red">Invalid Row: '.$row['birth'].'</span><br>';
        }else{
            echo '<span style="color:green">Valid Row: '.$row['birth'].'</span><br>';
        }
    }
?>

I'm not sure where your "birth" works from my fields. Is that the problem?

The Little Guy · January 31, 2008

You will need to change all instances of "birth" to "Cbirthdate"

Sorry I didn't do that.

Grant Holmes · January 31, 2008

LG: NP. I just wasn't sure. Well the good news is that I have no "Invalid" records per your test. I do have a bunch of blanks.

How can I modify that code to return the record number with the rows? I don't see a counter.

And/or... modify it to JUST return the rows with NO value in that field?

Barand · January 31, 2008

Here's an alternative

Run this SQL script

DELIMITER $$

DROP FUNCTION IF EXISTS `yourDBname`.`fnAge`$$
CREATE FUNCTION `yourDBname`.`fnAge`(dob DATE) RETURNS int
BEGIN
      DECLARE a int;
      SET a = YEAR(CURDATE()) - YEAR(dob);
      SET a = CASE DATE_FORMAT(CURDATE(),'%m%d') < DATE_FORMAT(dob,'%m%d')
        WHEN true THEN a-1 ELSE a END ;
      RETURN a;
END$$

DELIMITER ;

Then run this query with your column and table name

SELECT MIN(fnAge(adate)) as minage,
  MAX(fnAge(adate)) as maxage,
  AVG(fnAge(adate)) as avgage
  FROM `dates`

Grant Holmes · January 31, 2008

Barand,

Good to hear from you again. Can you tell me what the MySQL code does?

Barand · January 31, 2008

It creates a MySQL user function "fnAge(dob)" which returns the age from the dob passed to it. So if you

SELECT fnAge ('1949-01-22')

it returns "59"; (so it works for dates outside the UNIX epoch ie 1970 - 2037)

As the function converts dates to ages, the single query returns the min age, max age and average age for you.

Grant Holmes · January 31, 2008

So the SQL thing stays "within" PHPmyAdmin for use anytime, or am I missing it?

Then your query code... is this also from within PHPmyAdmin? or out at the browser level on a page?

Barand · January 31, 2008

Once the function is defined in the database you can call it it any query on that database; from PHPmyAdmin, from the command line, from a php script of your own

Grant Holmes · January 31, 2008

Thanks for clarifying! Just for kicks then.... back to the original question that we've now rabbit-trailed off at least 54 times....

How can I put this in my page code to get me the

Lowest age:

Average age:

Oldest age:

I cant put "SELECT fnAge ('1949-01-22')" in my query can I? That would always return the same thing. I can connect to the table. What would be next then?

Barand · February 1, 2008

SELECT MIN(fnAge(adate)) as minage,
  MAX(fnAge(adate)) as maxage,
  AVG(fnAge(adate)) as avgage
  FROM `dates`

EDIT: (assuming you defined the fnAge function)

<?php
$sql = "SELECT MIN(fnAge(CBirthdate)) as minage,
        MAX(fnAge(CBirthdate)) as maxage,
        AVG(fnAge(CBirthdate)) as avgage
        FROM `birhdays` ";
$res = mysql_query($sql);
list ($min, $max, $avg) = mysql_fetch_row($res);
echo "Min age : $min<br>Max age : $max<br>Average : $avg";        
?>

Grant Holmes · February 1, 2008

Hoky-doky. I tried creating the MySQL function. I went to the "SQL" tab in PHPmyAdmin.

(The Yahoo server I'm working on (no choice) is: phpMyAdmin 2.6.3)

Once in the SQL tab, I typed in your code with changes: (My db name is "contacts", table is "birthdays" and the field I need to work on is "Cbirthdate" -for the record)

DELIMITER $$

DROP FUNCTION IF EXISTS `contacts`.`fnAge`$$
CREATE FUNCTION `contacts`.`fnAge`(dob DATE) RETURNS int
BEGIN
      DECLARE a int;
      SET a = YEAR(CURDATE()) - YEAR(dob);
      SET a = CASE DATE_FORMAT(CURDATE(),'%m%d') < DATE_FORMAT(dob,'%m%d')
        WHEN true THEN a-1 ELSE a END ;
      RETURN a;
END$$

DELIMITER ;

However PHPmyAdmin says:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$

DROP FUNCTION IF EXISTS `contacts`.`fnAge`$$

CR

??

Barand · February 1, 2008

I don't have PHPmyAdmin - is there an option for excuting a script?

Grant Holmes · February 1, 2008

Not that I can see.

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

Join the conversation

Important Information