Jump to content



Photo

[PHP/SQL] Calculate Age


  • Please log in to reply
16 replies to this topic

#1 SinghKing

SinghKing

    Neowinian

  • Joined: 28-May 08
  • Location: West Midlands (UK)

Posted 12 June 2011 - 15:42

I have a mySQL database and a PHP frontend. The date of birth is stored within the database and im looking for some code that calculates the age preferably as a PHP


#2 AnthonySterling

AnthonySterling

    Offering bad advice since 23-December 04.

  • Joined: 23-December 04
  • Location: North-East, UK

Posted 12 June 2011 - 15:48

Where dob is the column which holds the date of birth. :)
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age


#3 Anooxy

Anooxy

    iPhantom

  • Joined: 23-June 08
  • Location: Albania
  • OS: Windows 7
  • Phone: Nokia 500

Posted 12 June 2011 - 15:51

Never done this but could be something along:

age = current_year - birth_year;

if(current_date < birth_date && current_month < birth_month){
    age = age - 1;
}

Assuming you store months with their numerical value which is the better way IMHO.

#4 OP SinghKing

SinghKing

    Neowinian

  • Joined: 28-May 08
  • Location: West Midlands (UK)

Posted 12 June 2011 - 15:56

The PHP website is reading straight off the table called members. I havent created any views or anything. Im also using phpMyAdmin to create all the database.
Where would I type in that statement?

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

#5 AnthonySterling

AnthonySterling

    Offering bad advice since 23-December 04.

  • Joined: 23-December 04
  • Location: North-East, UK

Posted 12 June 2011 - 15:59

You would execute it where required; it's no different to any of the other queries you're executing.
SELECT
    id
  , DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
  , name
FROM
  member


#6 Mike

Mike

    Neowinian Senior

  • Joined: 11-August 02

Posted 12 June 2011 - 15:59

Are you not encrypting those DOBs?

#7 OP SinghKing

SinghKing

    Neowinian

  • Joined: 28-May 08
  • Location: West Midlands (UK)

Posted 12 June 2011 - 16:06

You would execute it where required; it's no different to any of the other queries you're executing.

SELECT
    id
  , DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age
  , name
FROM
  member


There are no queries. which means im going somethign wrong here :s

Ok... Let me explain this situation a little better.

The database has information on members and their date of birth is stored. I have created a form using PHPRunner which creates the form from the members table. Since age cannot be stored into the database. The software has the ability to input functions or PHP code snippets.
So i was thinking, rather can creating queries, i would use php code instead to calculate the age. The php website can add and view members from the database.

hope this helps

#8 BGM

BGM

    Wibble Wobble™

  • Joined: 30-March 03
  • Location: Farnborough, UK

Posted 12 June 2011 - 16:36

you can just use DATEDIFF, and then round the end figure down after dividing the number of days in a year

i.e. my birthday is 11 oct 1984

select datediff('11-oct-1984',now())/365

that query results in 26, which is correct.. i am not a MySQL or PHP Dev so please test this first, it works in MSSQL anyway :p

There are no queries. which means im going somethign wrong here :s

well, how are you going to get the data out of the db? magic? :blink:

you will need to query the db in order to get the DOB, so, why not in your query convert it to the age rather than get php to do it for you on the page?

if you want to get the DOB from the db, and then convert it on the page via PHP.. try this function: http://www.bradino.c.../calculate-age/ ;)

#9 BGM

BGM

    Wibble Wobble™

  • Joined: 30-March 03
  • Location: Farnborough, UK

Posted 12 June 2011 - 16:42

duplicate :)

#10 Anooxy

Anooxy

    iPhantom

  • Joined: 23-June 08
  • Location: Albania
  • OS: Windows 7
  • Phone: Nokia 500

Posted 12 June 2011 - 16:42

I searched and found this: http://php.net/manua...tetime.diff.php

EDIT: Guy above me was faster XD.

#11 AnthonySterling

AnthonySterling

    Offering bad advice since 23-December 04.

  • Joined: 23-December 04
  • Location: North-East, UK

Posted 12 June 2011 - 16:49

I see.

function getAge($birthday){
  list($day, $month, $year) = explode('/', $birthday);
  $age        = date('Y') - $year;
  $month_diff = date('m') - $month;
  $day_diff   = date('d') - $day;
  if($day_diff < 0 || $month_diff < 0){
    $age--;
  }
  return $age;
}

I was going to suggest DateTime:diff but it is 5.3 dependent.*

*date_diff is an alias for DateTime::diff

#12 Anooxy

Anooxy

    iPhantom

  • Joined: 23-June 08
  • Location: Albania
  • OS: Windows 7
  • Phone: Nokia 500

Posted 12 June 2011 - 16:50

@Anthony:

Shouldn't that be:

if($day_diff < 0 && $month_diff < 0)


#13 OP SinghKing

SinghKing

    Neowinian

  • Joined: 28-May 08
  • Location: West Midlands (UK)

Posted 13 June 2011 - 15:55

Thanks guys, got it working. I used the following query to calculate the code

SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(aDoB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(aDoB, '00-%m-%d')) AS age FROM members

And then I saved it as a View. I then created the PHP table using the view and now the age automatically update.

TYVM

#14 BGM

BGM

    Wibble Wobble™

  • Joined: 30-March 03
  • Location: Farnborough, UK

Posted 13 June 2011 - 16:18

Thanks guys, got it working. I used the following query to calculate the code

SELECT *, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(aDoB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(aDoB, '00-%m-%d')) AS age FROM members

And then I saved it as a View. I then created the PHP table using the view and now the age automatically update.

TYVM

looks a bit inefficient, not sure though, would have to test it out

glad it works :)

#15 +scumdogmillionaire

scumdogmillionaire

    Neowinian Senior

  • Tech Issues Solved: 1
  • Joined: 23-October 01
  • Location: Denver, CO
  • OS: Windows 8.1 Pro/Windows 8.1 RT
  • Phone: Nokia Lumia 920

Posted 13 June 2011 - 17:00

you can just use DATEDIFF, and then round the end figure down after dividing the number of days in a year

i.e. my birthday is 11 oct 1984

select datediff('11-oct-1984',now())/365

that query results in 26, which is correct.. i am not a MySQL or PHP Dev so please test this first, it works in MSSQL anyway :p


Leap years will throw that off so it is inaccurate.



Click here to login or here to register to remove this ad, it's free!