duanke0555
2017-10-01 13:26
浏览 38
已采纳

使用多个可选搜索字段创建MYSQL查询

I'm create a user search where a user can search for other users based on username, language and/or location. At least one field is required so for example, you can search for just username, or username and location etc.

I'm having difficulty writing a MYSQL query which takes into consideration that some parameters can be blank. I've tried using a maximum of two paramaters at the moment:

PHP/MYSQL (PDO)

$data = json_decode(file_get_contents("php://input"));
$user = $data->user; //'null' if left blank 
$location = $data->location; //'null' if left blank


$sql = "SELECT user, spokenLanguages, profileImage 
        FROM users WHERE user LIKE :user 
        AND (town = LOWER(:location) OR country = LOWER(:location))";

This works perfectly if $user and $location are defined, but I only need the location WHERE clause included if $user is defined and $location isn't equal to null. Similarly if $location is defined and $user is null, the user clause shouldn't be considered. Is there any quick method of doing this that I'm unaware of? Or will it be a case of extending the query with a if/else statements?

Any help will be appreciated.

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douna2917 2017-10-01 13:30
    已采纳

    You might be better off constructing different queries based on what conditions are defined. This makes it easier to optimize them.

    If you want to put the logic in a single query, I think you want:

    WHERE (:user IS NULL OR user LIKE :user) AND
          (:location IS NULL OR LOWER(:location) IN (town, country))
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题