CODEWITHSUNDEEP
mysql
Sang Tonsing
Sang Tonsing

Reputation: 147

How to derive age from date column in database using sql?

I'm trying to display all users that are in specific age range(eg. between 10 and 20) using mysqli. What query will be best to derive the age and how?

I currently have a problem on a query that does not display the accurate result. What I want is to display users between the age this and that but the query below does not give any error but instead display even the age below 10 or above 20. I have 2 tables user and user_likes. 

        user
        username(pk)   gender   date_of_birth      country
          John           M        2010-12-31        India
          Bing           M        2000-12-31        Italy
          Rose           F        2000-12-01         UK

         user_likes 
         username(fk)    gender     age_from      to_age
         John             F             18            30
         Bing             F             20            30
         Rose             M             18            25

The $username below is a session stored in that variable.

        $sql = "SELECT gender, age_from, to_age FROM user_likes WHERE username = '$username'";
        $result = $conn->query($sql);
        if ($result->num_rows > 0)
        {
            while ($row = $result->fetch_assoc())
            {
                $gender1 = $row['gender'];
                $age_from = $row['age_from'];
                $to_age = $row['to_age'];
            }
        }

        $sql1 = "SELECT username, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM user WHERE NOT username = '$username' AND gender = '$gender1'  AND (date_of_birth BETWEEN '$age_from' AND '$to_age')";
        $result = $conn->query($sql1);
        if ($result->num_rows > 0)
        { 

            while ($row = $result->fetch_assoc())
            {                   
                echo "<div>";
                    echo "<span><a href = 'accounts.php?usernames=".$username."'><img src = '".$row['photo']."' alt = 'profile photo'></span>";
                    echo "<div>".$row['username']."</a><br><span>From".$row['country']."</span></div>";
                echo "</div>";
            }               
        }

The problem in the above code is that if i select the age from 18 to 80 even the age below 18 or above 80 is still displayed so I believe it has something to do with

      TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age

or

      date_of_birth BETWEEN '$age_from' AND '$to_age'

What I expect is that -- Suppose I am Rose and since my interest gender is male and selected age range is from 18 to 25 I want only 'Bing' user to be displayed.

Upvotes: 1

Views: 211

Answers (2)

MarcM
MarcM

Reputation: 2251

From my point of view, your query has a flaw. In

...WHERE ... AND (date_of_birth BETWEEN '$age_from' AND '$to_age')

...you are comparing date_of_birth (date) with age (integer). It makes no sense, you can get any unexpected result.

Try modifying the WHERE clause so that:

...WHERE ... AND (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$to_age')

Final sentence:

$sql1 = "SELECT username, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS age FROM user WHERE NOT username = '$username' AND gender = '$gender1'  AND (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$to_age')";

Upvotes: 1

Cid
Cid

Reputation: 15257

date_of_birth BETWEEN '$age_from' AND '$to_age'

Let's replace the fields by some values.

'2010-12-31' BETWEEN '18' AND '20'

This is a non-sense, you may want to rather use 

...
AND TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$to_age'

In example :

Schema (MySQL v5.7)

CREATE TABLE users (
  `username` VARCHAR(4),
  `gender` VARCHAR(1),
  `date_of_birth` VARCHAR(10),
  `country` VARCHAR(5)
);

INSERT INTO users
  (`username`, `gender`, `date_of_birth`, `country`)
VALUES
  ('John', 'M', '2010-12-31', 'India'),
  ('Bing', 'M', '2000-12-31', 'Italy'),
  ('Rose', 'F', '2000-12-01', 'UK');

Query #1

SELECT username,
       date_of_birth,
       TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS "age"
FROM users
WHERE (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '18' AND '20');

Output

| username | date_of_birth | age |
| -------- | ------------- | --- |
| Bing     | 2000-12-31    | 18  |
| Rose     | 2000-12-01    | 18  |

Query #2

SELECT username,
       date_of_birth,
       TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) AS "age"
FROM users
WHERE (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '01' AND '10');

Output

| username | date_of_birth | age |
| -------- | ------------- | --- |
| John     | 2010-12-31    | 8   |

View on DB Fiddle

Upvotes: 1

Related Questions