Creating a MYSQL query with multiple optional search fields

Question

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.

How to create a WHERE clause for PDO dynamically

Your Common Sense
– Your Common Sense

2017-10-02 08:23:07 +00:00
Commented Oct 2, 2017 at 8:23 — Your Common Sense
– Your Common Sense, Commented Oct 2, 2017 at 8:23

Gordon Linoff · Accepted Answer · 2017-10-01 13:30:38Z

3

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))

answered Oct 1, 2017 at 13:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Wickham Over a year ago

So you mean, a query for every possible combination of what could be defined?

Collectives™ on Stack Overflow

Creating a MYSQL query with multiple optional search fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related