SQL query to calculate age from birthdate

[andrewvanmarle]

I'm using Community builder, and want to use info from it's database to add the age to a form ( getting the query in the form is a step i'll worry about later...


I did some searching, and did find statements to calculate age from birthdate today, but what i'm looking for is to calculate the age from a fixed date ( in the future of a specific user ( the one logged in)

the DB is called: jos_comprofiler
the user ID is called: id
the birthdate is called: cb_geboortedatum
The date to calculate towards would be june 13th 2008

First of all, I don't know how to identify the user himself (how to point at the right id), and after that the birtdate should be called and subtracted fom the fixed date to determine age.

I got a piece of code from someone on another forum that does the calculation, now for the correct user ID to link to the birthdate....

Code: Select all

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS('2008-6-13') - TO_DAYS(cb_geboortedatum)), '%Y') + 0 AS Age 
FROM jos_comprofiler 
WHERE id = givenIdValue;

[ianmac]

Try:

$user = &JFactory::getUser();

$userid = $user->id;

[andrewvanmarle]

Code: Select all

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS('2008-6-13') - TO_DAYS(cb_geboortedatum)), '%Y') + 0 AS Age 
FROM jos_comprofiler 
WHERE id = $userid;

$user = &JFactory::getUser();

$userid = $user->id;

would this be correct then?

[andrewvanmarle]

Ok gotten a bit further:

Code: Select all

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS('2008-06-13') - TO_DAYS(cb_geboortedatum)), '%Y') + 0 AS Age FROM #___comprofiler 
WHERE id = ". $my->id; 

I'm using this script to enter some data in the fields of a form:

Code: Select all

<?php global $database,$my; $query = "SELECT cb_roepnaam FROM #__comprofiler WHERE id = ". $my->id;  
$database->setQuery( $query ); $fieldvalue = $database->loadResult(); return $fieldvalue;?> 

but if I just slap the above in the code below it I get no result. I have a feeling that $flieldvalue should be something different, but i'm not sure what.

Thanks!