How to create a select query calculating 'age' by user's date of birth?

Question

I want to make a select query to search seeking person between min and max ages. This is how search values comes from my search form.

Array
(
    [iam] => Man
    [seeking] => Woman
    [age_min] => 18
    [age_max] => 19
    [country_id] => 25
)

I have stored these values in two mysql tables. One is user and other one is Countries. My problem is there is not a column in user table to store user's age. Age is calculating according to the users Date of Birth and user table have a column to store users DOB.

My users table something like this:

CREATE TABLE IF NOT EXISTS users (
    user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    country_id SMALLINT UNSIGNED NOT NULL,
    username VARCHAR(20) NOT NULL, 
    email varchar(40) NOT NULL,
    first_name VARCHAR(30) DEFAULT NULL,
    sex ENUM('Male', 'Female') DEFAULT 'Male',
    dob VARCHAR(10) NOT NULL, 
    address VARCHAR(40) DEFAULT NULL,
    city VARCHAR(25) NOT NULL,
    last_login TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE (email), 
    UNIQUE (username)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

In country table it has country_id and country_name.

So. Can I know is there a way to do this using MySql SELECT query. Hope somebody may help me out. Thank you.

Why are you storing the data of birth as a character field? Fix your table to use the proper type (`date`). — Gordon Linoff, Jun 06 '15 at 13:36
@GordonLinoff, I am using 3 dropdown to select users DOB. then I format that 3 values to create DOB and its output is like this '1983-03-03' — MCC, Jun 06 '15 at 13:39
@Uchiha, I can calculate age with users DOB. But my problem is I have no idea how to create this kind of select query. — MCC, Jun 06 '15 at 13:43
@MCC. Don't confuse the user interface (issues of data display) with storage and retrieval — Strawberry, Jun 06 '15 at 13:48
@GordonLinoff, Sorry of my mistake, I can use `date` for DOB column. I changed it. So any idea to create this select query? — MCC, Jun 06 '15 at 13:51

score 0 · Answer 1 · answered Jun 06 '15 at 13:56

TIMESTAMPDIFF will get you the difference of two dates, so basically it will get you age if you pass dob and current date.

NOTE: Make sure to sanitize your inputs.

$Query = "SELECT * FROM users 
WHERE 
    TIMESTAMPDIFF(YEAR, dob, CURDATE()) >= '{$_POST['age_min']}' AND 
    TIMESTAMPDIFF(YEAR, dob, CURDATE()) <= '{$_POST['age_max']}' AND 
    sex = '{$_POST['seeking']}' AND 
    country_id = '{$_POST['country_id']}'";

How to create a select query calculating 'age' by user's date of birth?

1 Answers1