MYSQL

Designing Solutions Through Programming. This course is also called introduction to programming.
Forum rules
Please be careful about sharing personal information. Never use any identifiable information.
Post Reply
User avatar
julia
New User
Posts: 3
Joined: 23 Aug 2018, 11:50

MYSQL

Post by julia » 02 Apr 2019, 09:58

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 ;"
1 x

Tags:

User avatar
Mr. MacKenty
Site Admin
Posts: 119
Joined: 28 Apr 2018, 17:06
Answers: 3

Re: MYSQL

Post by Mr. MacKenty » 02 Apr 2019, 17:57

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'];
}
0 x

Post Reply