这是我的代码,使用案例选择年龄范围:

    //the age advance search
    switch (Request::post('member_age')):
        case '18-25':
            $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -25 YEAR) And Date_Add(NOW(), INTERVAL -18 YEAR)";
            break;
        case '26-35':
            $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -35 YEAR) And Date_Add(NOW(), INTERVAL -26 YEAR)";
            break;
        case '36-45':
            $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -45 YEAR) And Date_Add(NOW(), INTERVAL -36 YEAR)";
            break;
        case '46-55':
            $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -55 YEAR) And Date_Add(NOW(), INTERVAL -46 YEAR)";
            break;
        case '56-65':
            $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -65 YEAR) And Date_Add(NOW(), INTERVAL -56 YEAR)";
            break;
        case '65+':
            $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -9999 YEAR) And Date_Add(NOW(), INTERVAL -66 YEAR)";
            break;
        default:
            $age = "";
    endswitch;


SQL:

SELECT     *
FROM       users
LEFT JOIN  friends
ON         users.user_id = friends.friends_friend
LEFT JOIN  friend_request
ON         users.user_id = friend_request.friend_request_to
OR         users.user_id = friend_request.friend_request_from
RIGHT JOIN user_personal_information
ON         user_personal_information.user_personal_information_user = users.user_id
WHERE      Concat_ws(' ', user_personal_information.user_firstname, user_personal_information.user_surname) LIKE ?
AND        user_enabled = 1
AND        user_id != ? ". $age ." ". $country ." ". $travel ." ". $occupation ."
GROUP BY   user_id


现在,例如,如果用户的生日是1970年,它将不会返回任何值,直到他们将DOB年更改为1969年(11月30日之前)或1971年。这是怎么做的,我该如何解决呢? ?我使用日期作为列类型

最佳答案

您的年龄段代码在每个年龄段之间遗漏了一年。解决方法:

//the age advance search
switch (Request::post('member_age')):
    case '18-25':
        $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -25 YEAR) And Date_Add(NOW(), INTERVAL -18 YEAR)";
        break;
    case '26-35':
        $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -35 YEAR) And Date_Add(NOW(), INTERVAL -25 YEAR)";
        break;
    case '36-45':
        $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -45 YEAR) And Date_Add(NOW(), INTERVAL -35 YEAR)";
        break;
    case '46-55':
        $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -55 YEAR) And Date_Add(NOW(), INTERVAL -45 YEAR)";
        break;
    case '56-65':
        $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -65 YEAR) And Date_Add(NOW(), INTERVAL -55 YEAR)";
        break;
    case '65+':
        $age = "AND user_birthdate BETWEEN Date_Add(NOW(), INTERVAL -9999 YEAR) And Date_Add(NOW(), INTERVAL -65 YEAR)";
        break;
    default:
        $age = "";
endswitch;

关于mysql - 我在date_add之间使用基于用户年龄的高级搜索,但是根据月份和年份不起作用,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/33999549/

10-13 06:54