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

[thara]

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.

[fastsol]

You're going to need to change the data type of the dob column to an actual date format or you won't be able to determine any kind of time difference from it's values.

[thara]

@fastsol, I am using 3 dropdown to select users DOB. then I format that 3 values to create DOB and its output is like this 'yyyy-mm-dd'. Thats why  I use VARCHAR datatype for DOB.

Edited June 6, 2015 by thara

[thara]

@fastsol, Sorry of my mistake, I can use date for DOB column. I changed it. So any idea to create this select query?

[Ch0cu3r]

First don't use varchar for storing dates, set your dob field to use DATE type. Then you can use MySQL's DATE aggregate functions. Example

SELECT ... FROM users
WHERE YEAR(NOW())-YEAR(dob) BETWEEN $minAge AND $maxAge

[fastsol]

Here's a post with lots of examples http://stackoverflow.com/questions/5773405/calculate-age-in-mysql-innodb

[Barand]

This method gets my vote

SELECT TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age;

[thara]

@Barand, how would be the select query if all form fields are optional?

[thara]

@Barand, This is how I tried it. But I cannot get any records,

    $q = "SELECT  u.user_id
                            , u.username
                            , u.email
                            , u.dob
                            , u.sex
                            , u.whoami
                            , u.about_you
                            , u.age_range
                            , h.centimeters
                            , u.city
                            , c.country_code
                            , i.image
                            , i.image_path
            FROM users u
                INNER JOIN height h ON h.id = u.height_id
                INNER JOIN countries c ON c.id = u.country_id
                LEFT JOIN image_info i ON i.user_id = u.user_id AND i.image_type = 'primary'
            WHERE
                TIMESTAMPDIFF(YEAR, dob, CURDATE()) >= '{$ageMin}' AND
                TIMESTAMPDIFF(YEAR, dob, CURDATE()) <= '{$ageMax}' AND
                u.sex = '{$sex}' AND
                u.country = '{$countryId}'
            ORDER BY u.date_registered DESC";

Can you tell what is the wrong with this?

Thank you.

[Barand]

You check if an input has a value and, if it has, include that condition in the WHERE clause. If it hasn't, leave it out.

[Barand]

Can you tell what is the wrong with this?

 

 

What values are you passing to the query?

[thara]

Now I fixed the error and its working.. But still no idea how to do this

 

You check if an input has a value and, if it has, include that condition in the WHERE clause. If it hasn't, leave it out.

 

 

My input check something like this :

    // check "I AM A" dropdown:
    if (!empty($_POST['iam'])) {
        $iam = $_POST['iam'];    
        $iam = filter_var($iam, FILTER_SANITIZE_STRING);
        //echo $iam;
    }    else {
        $iam = '';
    }
    
    // check "Seeking" dropdown:
    if (!empty($_POST['seeking'])) {
        $seeking = $_POST['seeking'];    
        $seeking = filter_var($seeking, FILTER_SANITIZE_STRING);
        $seeking = trim($seeking);
        if ($seeking == "Man") {
            $sex = "Male";
        } else {
            $sex = "Female";
        }
        //echo $seeking;
    }    else {
        $seeking = '';
    }
    

    // check "Age Min" dropdown:
    if (!empty($_POST['age_min'])) {
        $ageMin = (int) $_POST['age_min'];    
        //echo $ageMin;
    }    else {
        $ageMin = 18;
    }
    
    // check "Age Max" dropdown:
    if (!empty($_POST['age_max'])) {
        $ageMax = (int) $_POST['age_max'];    
        //echo $ageMax;
    }    else {
        $ageMax = 30;
    }    
    
    // check "Country" dropdown:
    if (!empty($_POST['country'])) {
        $countryId = (int) $_POST['country'];    
        //echo $countryId;
    }    else {
        $countryId = '';
    }

[Barand]

One thing to watch when using ENUM columns. If the value in the search is numeric it uses the numeric value of the enum. If it is a string it uses the descriptive value.

 

So

mysql> SELECT * FROM users WHERE sex = 'female';
+----+----------+--------+
| id | username | sex    |
+----+----------+--------+
|  2 | User 222 | female |
|  4 | User 444 | female |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users WHERE sex = 2;
+----+----------+--------+
| id | username | sex    |
+----+----------+--------+
|  2 | User 222 | female |
|  4 | User 444 | female |
+----+----------+--------+
2 rows in set (0.00 sec)

But if you put quotes round the '2' it treats it as a string value

mysql> SELECT * FROM users WHERE sex = '2';
Empty set (0.00 sec)

As for omitting the where criteria that are not required

$whereclause = '';
$where = array();
// check "Age Min" dropdown:
    if (!empty($_POST['age_min'])) {
        $ageMin = (int) $_POST['age_min'];    
        //echo $ageMin;
    }    else {
        $ageMin = 18;
    }
    
    // check "Age Max" dropdown:
    if (!empty($_POST['age_max'])) {
        $ageMax = (int) $_POST['age_max'];    
        //echo $ageMax;
    }    else {
        $ageMax = 30;
    }
    
    $where[] = "(TIMESTAMPDIFF(YEAR,dob,CURDATE()) BETWEEN $agemin AND agemax)";    
    
    // check "Country" dropdown:
    if (!empty($_POST['country'])) {
        $countryId = (int) $_POST['country'];
        $where = "(u.country = $countryid)"    
        //echo $countryId;
    }    else {
        $countryId = '';
    }
    
if (count($where > 0)) { //any search criteria entered
    $whereclause = "WHERE " . join(' AND ', $where);
}

$sql = "SELECT ....." . $whereclause;

[thara]

I am not clear about this

One thing to watch when using ENUM columns. If the value in the search is numeric it uses the numeric value of the enum. If it is a string it uses the descriptive value.

 

 

can you kindly explain it? and why didn't you include "sex" in where clause?

 

Thank you.

[Barand]

If a value is provided, add it to the WHERE conditions.

 

I took your sample input check code from #12 above, that's why there is no sex in there