MYSQL

[julia]

I want to add a WHERE condition if the variable is not set to "none". I am trying to combine two case statements in one SELECT WHERE statement in MYSQL. It tells me that there is an error in my SQL but I couldn't find any answer on the internet. This is my code:

"SELECT * FROM `restaurants` WHERE
CASE
WHEN '$type' != 'none' THEN `type` = '$type' END
AND
CASE
WHEN '$cuisine' != 'none' THEN AND `cuisine` = '$cuisine' END
AND
CASE
WHEN '$location' != 'none' THEN AND `location` = '$location' END
AND
CASE
WHEN '$price' != 'none' THEN AND `price` = '$price' END ;"

[Mr. MacKenty]

wow.

So if the user DOESN'T choose an option, you want the choice to be none. Is that right?

I think you can test, using php, if the value is empty. If the value IS empty, then you can assign a wildcard character, which would select ANY restaurant of that type. For example, Let's say I ONLY choose a cuisine (greek) but I leave everything else blank. Your program should return every single greek restaurant in Warsaw, right?

Here's some UNTESTED PSEUDOCODE that might be helpful to get your thinking started:

Code: Select all

<?php 

if empty($_POST['location']) {
     // if this is true, it means there is no value for location.
     // so we set the value to the wildcard character, which means select everything
     $location = "*";
} else {
     // aha. If we are here, it means THERE IS a value for location, so let's set it:
     $location = $_POST['location'];
}