Jump to content

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


thara

Recommended Posts

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.

Link to comment
Share on other sites

@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 by thara
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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 = '';
    }
Link to comment
Share on other sites

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;
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.