Select the age from birthday in mysql ?

Hi,

following  is user table:

CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fname` varchar(255) NOT NULL,
`mname` varchar(255) NOT NULL,
`lname` varchar(255) NOT NULL,
`createdat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`birthday` date NOT NULL,
PRIMARY KEY (`id`)
);

insert one row into table:

INSERT INTO `user` (`id`, `fname`, `mname`, `lname`, `createdat`, `birthday`) VALUES
(1, ‘Anil’, ‘Kumar’, ‘Panigrahi’, ‘2009-11-19 11:04:27’, ‘1983-02-14’),

To retrieve age from the table:

SELECT
 YEAR( NOW(  )  )  - YEAR( birthday )
 -
 IF( MONTH( NOW(  )  )  <
 MONTH( birthday ) , 1, 0  )  -
 IF( MONTH( NOW(  )  )  = MONTH( birthday )
AND
 DAYOFMONTH( NOW(  )  )  <
 DAYOFMONTH( birthday ) , 1, 0  )
AS
age,
birthday, now(  )
FROM user;

Hope that it will be useful.

Anil Kumar Panigrahi

With more than 10 years experience in PHP and Founder of Anil Labs, a blog for PHP and related posts. contributed posts regarding CodeIgniter, CakePHP and Learn PHP online ... Contact : [email protected]

You may also like...

3 Responses

  1. Jetz says:

    Gud day sir. .

    Can you please expound your code?? Im a newbie in php MySQL that’s why its very complicated for understand that code.

    Tnx. My Regards.

    -Jetz

  2. Hello Jetz,

    In the sql query,

    there three mysql functions used, that year(),month(), dayofmonth().

    If you run query like one by one means select year ,month and ….,

    In that case hope you will understand the code.

  3. Gregor says:

    Nice one, i do the same but with php, because i dont want to “olver load” or something like that in the sql code lol, too many querys that i have to manage u.u

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.