Как вывести возраст из столбца даты в базе данных с помощью sql?

ProgramBox

Как вывести возраст из столбца даты в базе данных с помощью sql?

Post author:admin
Запись опубликована:3 мая, 2023
Post category:Вопросы по программированию

#mysql

Вопрос:

Я пытаюсь отобразить всех пользователей, которые находятся в определенном возрастном диапазоне (например, от 10 до 20), используя mysqli . Какой запрос будет наилучшим для определения возраста и как?

В настоящее время у меня проблема с запросом, который не отображает точный результат. Я хочу отображать пользователей в возрасте от того-то и того-то, но приведенный ниже запрос не выдает никакой ошибки, а вместо этого отображает даже возраст ниже 10 или выше 20. У меня есть 2 таблицы user и 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

$username Ниже приведен сеанс, сохраненный в этой переменной.

         $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>";
            }               
        }

Проблема в приведенном выше коде заключается в том, что если я выбираю возраст от 18 до 80, все равно отображается возраст ниже 18 или выше 80, поэтому я считаю, что это как-то связано с

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

или

       date_of_birth BETWEEN '$age_from' AND '$to_age'

Чего я ожидаю, так это того, что — предположим, я Роза, и поскольку мой пол интересов мужской, а выбранный возрастной диапазон от 18 до 25, я хочу, чтобы отображался только 'Bing' пользователь.

Ответ №1:

 date_of_birth BETWEEN '$age_from' AND '$to_age'

Давайте заменим поля некоторыми значениями.

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

Это бессмысленно, возможно, вы захотите использовать

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

В примере :

Схема (MySQL версии 5.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');

Запрос # 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');

Вывод

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

Запрос # 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');

Вывод

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

Просмотр в DB Fiddle

1. замена date_of_birth на age ухудшает ситуацию, поскольку она вообще ничего не отображает, в то время как date_of_birth отображает всех пользователей, а не только требуемый возрастной диапазон

2. Правда, я допустил небольшую ошибку

3. Спасибо, это работает, но вы бы порекомендовали сделать это по-моему или предпочли бы другие методы?

4. У вас не так уж много выбора. Я буду использовать этот способ. Но я настоятельно рекомендую вам использовать параметризованные запросы, чтобы избежать SQL-инъекций

Ответ №2:

С моей точки зрения, в вашем запросе есть недостаток. В

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

…вы сравниваете date_of_birth (дату) с age (целым числом). Это не имеет смысла, вы можете получить любой неожиданный результат.

Попробуйте изменить предложение WHERE, чтобы:

 ...WHERE ... AND (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$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 (TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN '$age_from' AND '$to_age')";