PHP & MySql array issue (extremely annoying!)

Question

As a brief explanation of what I'm trying to do:

I have a MySql database with various columns. One of these columns is the "birthday" column which contains the users date of birth.

What I'm trying to do is take all of the date of births in this column, turn them in to an age, and then work out the average/mean age.

So... I've got the code for both parts working, I just can't get them to work together!

I can feed the birthday function I have a date of birth, and it'll turn it in to an age.

I can get all of the dates of birth in to an array.

What I can't do is anything from here. I can't turn the array in to the ages and work out the mean, basically, and it's driving me nuts. I am a newbie to PHP, but I've done a fair bit of work so far on this.

Would REALLY appreciate help to get this working! I just don't know where to go from the below.

Here's my code:

// Age calculation

function CalculateAge($BirthDate)
{
    // Put the year, month and day in separate variables
    list($Day, $Month, $Year) = explode("/", $BirthDate);

    $YearDiff = date("Y") - $Year;

    // If the birthday hasn't arrived yet this year, the person is one year younger
    if(date("m") < $Month || (date("m") == $Month && date("d") < $DayDiff))
    {
            $YearDiff--;
    }
    return $YearDiff;
}

// How to use the function
// CalculateAge("24/06/1991");

//Birthdate array

$ages = mysql_query("SELECT birthday FROM user_records");

$agearray = array(); 

while($row=mysql_fetch_assoc($ages)) {array_push($agearray, $row);}

Thanks for any help in advance.

christophmccann · Accepted Answer · 2011-01-27 21:15:14Z

Remember that $row is an associative array rather than a string variable so you need to do $row['birthday'] or you will pass in an array object into the function. I have explained in code below.

Hope it helps

// Age calculation

function CalculateAge($BirthDate)
{
   // Put the year, month and day in separate variables
   list($Day, $Month, $Year) = explode("/", $BirthDate);

   $YearDiff = date("Y") - $Year;

    // If the birthday hasn't arrived yet this year, the person is one year younger
   if(date("m") < $Month || (date("m") == $Month && date("d") < $DayDiff))
   {
        $YearDiff--;
   }
   return $YearDiff;
}

// How to use the function
// CalculateAge("24/06/1991");

//Birthdate array

 $birthdays = mysql_query("SELECT birthday FROM user_records");

 $birthdaysArray = array(); 

 while($row=mysql_fetch_assoc($birthdays)) { $birthdaysArray[] = $row['birthday']; } 

//LOOP HERE TO FIND AGES

Jon Black · Accepted Answer · 2011-01-27 23:03:22Z

drop table if exists users;

create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null,
dob date not null
)
engine=innodb;

insert into users (username, dob) values
('f00','1960-01-01'),    ('bar','1970-01-01'),
('alpha','1980-01-01'),    ('beta','1990-01-01'),
('delta','2000-01-01'),    ('theta','2010-01-01'),
('zeta',curdate());

select
 if(sum(a.age) is null, 0, sum(a.age)) as sum_age,
 count(*) as count_ages,
 if(count(*) = 0, 0, sum(a.age) / count(*)) as avg_age
from
(
select 
 date_format(now(), '%Y') - date_format(dob, '%Y') - (date_format(now(), '00-%m-%d') < date_format(dob, '00-%m-%d')) as age
from users
) a

+---------+------------+---------+
| sum_age | count_ages | avg_age |
+---------+------------+---------+
|     156 |          7 | 22.2857 |
+---------+------------+---------+
1 row in set (0.00 sec)

Michael Berkowski · Accepted Answer · 2011-01-27 21:18:56Z

0

So next, you need to iterate over your array and convert to age using your function.

$calculated_ages = array();
foreach($agearray as $d)
{
  $calculated_ages[] = CalculateAge($d);
}

Then get the mean.

answered Jan 27, 2011 at 21:18

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

1 Comment

Kieran Over a year ago

Awesome guys, thanks, I got it all working! Extremely appreciated.

Hamish · Accepted Answer · 2011-01-27 22:04:29Z

0

Rather than pulling it into PHP you can do this directly from the MySQL query,

For example, the following query returns a the average age of users in days to the current date:

SELECT AVG(DATEDIFF(NOW(), birthday)) AS AverageAge FROM `user_records`;

answered Jan 27, 2011 at 22:04

Hamish

23.4k8 gold badges57 silver badges67 bronze badges

Collectives™ on Stack Overflow

PHP & MySql array issue (extremely annoying!)

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related