Announcement

Collapse
No announcement yet.

calculate age in years simply

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • calculate age in years simply

    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?

  • #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';

    Comment


    • #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.

      Comment


      • #4
        Originally posted by Giblet535 View Post
        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.
        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

        Comment


        • #5
          Create a MySQL function.
          Code:
          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

          Comment


          • #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

            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

            Comment


            • #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

              Comment


              • #8
                thanks

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

                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
                Last edited by MikeDE; 01-19-2015, 05:34 PM.

                Comment


                • #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. :-)

                  Now head over to SC.
                  Create an ajax event onChange for the date field.
                  Code:
                  sc_lookup(rs,"SELECT GETAGE('{birthdate}')");
                  {age_now} = {rs[0][0]};

                  That's it
                  jsb

                  Comment


                  • #10
                    sorry for being late in replying this

                    I followed everything what you said exactly... at the beginning, seemed more than I can handle 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

                    Comment

                    Working...
                    X