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.