<pedrocorreia.net ⁄>
corner
<mySearch ⁄> <mySearch ⁄>

corner
 
corner
<mySnippets order="rand" ⁄> <mySnippets order="rand" ⁄>

corner
 
corner
<myContacts ⁄> <myContacts ⁄>

<email ⁄>


pc@pedrocorreia.net

<windows live messenger ⁄>


pedrojacorreia@hotmail.com

<myCurriculum type="pdf" ⁄>


Download
corner
 
corner
<myBlog show="last" ⁄> <myBlog show="last" ⁄>

corner
 
corner
<myNews show="rand" ⁄> <myNews show="rand" ⁄>

corner
 
corner
<myNews type="cat" ⁄> <myNews type="cat" ⁄>

corner
 
corner
<myQuote order="random" ⁄> <myQuote order="random" ⁄>

corner
 
corner
<myPhoto order="random" ⁄> <myPhoto order="random" ⁄>

<pedrocorreia.net ⁄>
corner
 
corner
<myAdSense ⁄> <myAdSense ⁄>

corner
 
corner
<myVisitorsMap ⁄> <myVisitorsMap ⁄>

corner
 
 

<Calculate age in MySQL ⁄ >




clicks: 15306 15306 2008-06-13 2008-06-13 goto mySnippets mySnippets sql  Download  Bookmark This Bookmark This



I will try to translate some Portuguese snippets to English. The first one I picked was, how to calculate age in MySQL.

MySQL (as far as I know ^_^'') doesn't have a function that allow us to obtain a person age, although it's not really that hard to "make" one, a few subtractions and ... a simple comparison is enough.

In many cases it's necessary to know a person's age, we have a few options for doing that:
- we insert directly the age, which's not that really dynamic, because as time goes by, of course the field age will be outdated, and consequently our data, and date, will turn to be obsolete;
- we insert directly the age and will change manually that information ... well, no way, we have many other interesting things to do in life :);
- we can make a script (maybe a cron job) that will update that information, but we'll just overload the system unnecessary
- .......
- we can also make that calculation using a server-side scripting language, such as PHP, ASP, etc., it's also a good solution, but if we could retrieve that information directly from MySQL, maybe it would be better
- we can save the date of birth and every time that it's necessary we can make a query in which we can ask gently to MySQL to do that work for us;

It will be in this last option that I'll focus.


Let's suppose we have the following table structure

  1. -- Table "persons_data" DDL
  2.  
  3. CREATE TABLE `persons_data` (
  4. `id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT,
  5. `name` varchar(255) NOT NULL DEFAULT '',
  6. `date_birth` date DEFAULT NULL,
  7. PRIMARY KEY (`id`)
  8. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;



Let's fill it with some values:

  1. INSERT INTO persons_data
  2. (id, name, date_birth)
  3. VALUES
  4. (1, "Jet Li", "1963-4-26");
  5.  
  6. INSERT INTO persons_data
  7. (id, name, date_birth)
  8. VALUES
  9. (2, "Jackie Chan", "1954-4-7");
  10.  
  11. INSERT INTO persons_data
  12. (id, name, date_birth)
  13. VALUES
  14. (3, "Ji-hyun Jun", "1981-10-30");
  15.  
  16. INSERT INTO persons_data
  17. (id, name, date_birth)
  18. VALUES
  19. (4, "Tae-hyun Cha", "1976-3-25");
  20.  
  21. INSERT INTO persons_data
  22. (id, name, date_birth)
  23. VALUES
  24. (5, "Ye-jin Son", "1982-1-11");




We'll create a file named "settings.inc", this file will contain some settings that will be necessary to this demonstration, such as the database connection, table name, but I would just like to point out the most important part in this snippet (or this snippet wouldn't be called calculating age in mysql :)) ISQL_AUX_CALCULATE_AGE


Basically to get the age, we have to subtract the current year for the year of the birth date, however there's a small catch, of course our goal's to have the person's age always updated, for example, if that person birthday already occurred, that information's valid, but if not, the information's invalid, so we can't strictly rely only in subtracting years, we have to go a little deeper in the date analysis. Let's see these examples:
- let's suppose that this is our current date 2007-11-03
-> if the date of birth's 1990-02-11, 2007-1990=17, it's correct;
-> if the date of birth's 1990-11-04, 2007-1990=17, it's incorrect, because only tomorrow the person will reach his 17st anniversary.



Because of that, and as I said before, we have to go a little deeper in the date analysis, we have to analyze also the month & day.
When, in MySQL, we compare something the value returned will be 1 (true) or 0 (false), well, so we'll just use that small information to verify if the current date's inferior or not comparing to the person's birthday.


settings.inc
  1. <?php
  2.  
  3. /**
  4. * Establish the connection to the database
  5. *
  6. * @return DatabaseConnector
  7. */
  8. function connectDB(){
  9. $db = mysql_connect("my_server", "my_username", "my_pwd");
  10. mysql_select_db("my_database",$db) or die("<b>connection_error</b>");
  11. return $db;
  12. }
  13.  
  14. //this is the auxiliary sql statement that will calculate the age
  15. define("ISQL_AUX_CALCULATE_AGE","
  16. YEAR(CURRENT_DATE()) - YEAR(`date_birth`) -
  17. (
  18. month(CURRENT_DATE()) -
  19. month(`date_birth`)<=0 &&
  20. (
  21. day(CURRENT_DATE()) - day(`date_birth`)<=0
  22. )
  23. ) As `PersonAge`
  24. ");
  25.  
  26. //table that contains all the persons data
  27. define("TBL_DATA","`persons_data`");
  28.  
  29. ?>










Using this short example...

  1. <?php
  2. require_once("settings.inc");
  3.  
  4. $const=get_defined_constants();
  5.  
  6. $iSQL="
  7. Select
  8. `id`, `name`, `date_birth`, {$const['ISQL_AUX_CALCULATE_AGE']}
  9. From
  10. {$const['TBL_DATA']}
  11. ";
  12.  
  13. $db=connectDB(); //establish the database connection
  14. $sqlResource=mysql_query($iSQL) or die(mysql_error());
  15. while($myrow=mysql_fetch_object($sqlResource)){
  16. $str.="{$myrow->name} was born in {$myrow->date_birth} and has {$myrow->PersonAge} years.<br/>";
  17. }
  18.  
  19. //free resources that are no longer needed
  20. mysql_free_result($sqlResource);
  21. mysql_close($db);
  22. unset($myrow);
  23.  
  24. //send to output
  25. echo $str;
  26. ?>



… will generate the following output

Jet Li was born in 1963-04-26 and has 45 years old.
Jackie Chan was born in 1954-04-07 and has 54 years old.
Ji-hyun Jun was born in 1981-10-30 and has 26 years old.
Tae-hyun Cha was born in 1976-03-25 and has 32 years old.
Ye-jin Son was born in 1982-01-11 and has 26 years old.

Please take a note that this information (age calculations) is based in 2008-06-13 and in this snippet the dates will not be updated ^_^''


If you have any doubt or found any error, please drop me an email









clicks: 15306 15306 2008-06-13 2008-06-13 goto mySnippets mySnippets sql  Download  Bookmark This Bookmark This