calculate age in years simply

Scriptcase 8 Discussion
#1

hi guys,

i have form with single record, one field from db type (date) is the date of birth

created new sc field just to calculate (age now) i want to make new ajax event to calculate from date of birth (ajax: on change) and fill the field of {age now} in years

tried the to date difference thing macro didn’t work due to lots of details, also can’t use sql statements because date is not saved to database yet

anybody did this before?

Calculate age from date of birth?
#2

got it this way:

$today=date(‘Y-m-d’);
{differences} = sc_date_dif_2 ({birthdate}, “yyyy-mm-dd”, $today, “yyyy-mm-dd”, 2);
{age} = {differences[2]}.’ Years’;

#3

Yep.

Note that you can also use SQL whether the data is saved or not. SELECT works with provided values much faster than it does with db data. Sometimes, SQL makes a decent calculator.

SELECT YEAR({age_now}) - YEAR({dob}) - (DATE_FORMAT({age_now},
‘%m%d’) < DATE_FORMAT({dob}, ‘%m%d’)) AS Age;

That will compensate for leap year babies.

I would do it with PHP, myself. StackExchange will show you how. I’m not sure I’d count on SC macros for anything that isn’t 100% ScriptCase functionality. Date math isn’t ScriptCase functionality.

#4

[QUOTE=Giblet535;31348]Yep.

Note that you can also use SQL whether the data is saved or not. SELECT works with provided values much faster than it does with db data. Sometimes, SQL makes a decent calculator.

SELECT YEAR({age_now}) - YEAR({dob}) - (DATE_FORMAT({age_now},
‘%m%d’) < DATE_FORMAT({dob}, ‘%m%d’)) AS Age;

That will compensate for leap year babies.

I would do it with PHP, myself. StackExchange will show you how. I’m not sure I’d count on SC macros for anything that isn’t 100% ScriptCase functionality. Date math isn’t ScriptCase functionality.[/QUOTE]

you are right, but also to use that statement in events you will need macro i guess, something like sc_lookup and thats why i asked the question above, using php and regular sql stuff with SC sometimes is irritating and confusing unless you tried similar thing in sc before and worked…

do you have any simpler idea how to use regular mysql or php stuff without using sc macros? please share your thoughts with us if you have

thanks in advanced

#5

Create a MySQL function.


CREATE FUNCTION `GETAGE`(BDAY DATE) RETURNS tinyint(3) unsigned
    READS SQL DATA
RETURN (YEAR(CURDATE())-YEAR(BDAY)) - (RIGHT(CURDATE(),5) < RIGHT(BDAY,5))

All you have to do now is: SELECT GETAGE(the_date_you_want) AS age;

jsb

#6

Oh hii jsb,

Only reading your name from time to time around makes me feel RELIEF

Happy new year by the way and thanks indeed :slight_smile:

Maybe i am missing some fundamentals here, thought that using select or direct php/mysql stuff are to be used with lookup macro or other macros to work inside SC, didn’t know that can be used directly in events, however, will give it a shot

#7

Happy New Year as well.
Just to make it clear, the above function has to be created within your database on the MySQL server.
The advantage is you can use it in a sql statemant like any other MySQL function.

jsb

#8

thanks

hmmm okay, i was right, in fact I have big lack in fundamentals, sorry to waste your time :frowning:

i added that code to the sql box in my database (mysql) (mytable) through phpmyadmin and it gave me that is ok (no error) but don’t know how to retrieve or display the outcome value, tried to add this to ajax event: SELECT GETAGE({birthdate}) AS age; got errors, unexpected don’t know what

Tried to read about it here got even more lost

Moreover, what i still don’t understand, the form is yet newly opened, the record is not in DB YET, end user will add the birth date as fresh entry, and the age field should be calculated based on that entry, i mean how mysql will know the value of the field before inserting it in the database? mabye you guys mean is to calculate the already inserted records, or i have to stop and go sleep

#9

Ok, don’t worry.
Fire up phpmyadmin and select the database. DON’T click on any table.
Click on the ‘Structure’ tab and scroll down if can’t see the whole page.
Underneath the list of tables there should be a line: ‘+ Routines’ click on it and check if your function is there. If not, create it.
Now go to the SQL tab and issue the following statement: SELECT GETAGE(‘2000-01-01’) AS age
You should get 15. :slight_smile:

Now head over to SC.
Create an ajax event onChange for the date field.


sc_lookup(rs,"SELECT GETAGE('{birthdate}')");
{age_now} = {rs[0][0]};

That’s it
jsb

#10

sorry for being late in replying this

I followed everything what you said exactly… at the beginning, seemed more than I can handle :slight_smile: but it worked ok as you said, and now will definitely hit some walls to achieve many more steps ahead in this regard

truly appreciated brother, thanks a million