MySQL - Return all values if input is empty

Question

I am creating an advanced search feature for a website and it's almost done, I'm only having one major issue. I am matching the rooms like this:

AND Rooms=" .$_SESSION["room"] ."

and tried this as well:

AND (Rooms=" .$_SESSION["room"] ." OR Rooms IS NULL)

But the problem is if the user doesn't insert any value in the room input it won't show any room. And with the IS NULL code if I insert "8" in the rooms input if there is no matches it will display all values from the DB. I don't want to make the input as required.

I just need a solution with mysql for when the field is empty return all values without using this:

if ($_SESSION["room"]==NULL) {}
else{}

Full query:

`SELECT * FROM secret WHERE secretIDName='1' AND NatureTypeIDName LIKE '%" .$_SESSION["nature"] ."%' AND (NettArea>=" 
.$_SESSION["NettArea"] ." OR NettArea IS NULL) AND ConditionTypeIDName LIKE'%" .$_SESSION["lifestyle"] 
."%' AND ((SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',4),'|',-1)>=" 
.$_SESSION["BusinessTypeValuesMin"] 
." AND SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',4),'|',-1)<=" 
.$_SESSION["BusinessTypeValuesMax"] 
.") OR SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',4),'|',-1) = '') AND (SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',2),'|',-1)='" 
.$_SESSION["BusinessTypeValuesType"] 
."' OR SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',2),'|',-1)='') AND GarageArea>=" 
.$_SESSION["GarageArea"] 
." AND (LocationIDName LIKE '%" 
.$_SESSION["zone1"] 
."%' AND LocationIDName LIKE '%" 
.$_SESSION["zone2"] 
."%' AND LocationIDName LIKE '%" 
.$_SESSION["zone3"] 
."%') AND (Rooms=" 
.$_SESSION["room"] 
.") LIMIT " 
.($page-1)*$Page 
.", " .$Page ."";`

Apply check first if(!empty('$_SESSION["room"]')){AND Rooms=" .$_SESSION["room"] ;"} — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Aug 2, 2017 at 11:42
Don't add the Rooms condition if the $_SESSION["room"] is empty. — Tanmay Maity
– Tanmay Maity, Commented Aug 2, 2017 at 11:45
If You don't want to create query using ifs, You can do it like (Rooms=".$_SESSION["room"]." OR ".$_SESSION["room"]." = '') — Roman Hocke
– Roman Hocke, Commented Aug 2, 2017 at 11:47

Roman Hocke · Accepted Answer · 2017-08-02 11:53:11Z

2

You can create the condition like this (I assume, that the "rooms" is number representing number of rooms?):

AND (Rooms = ".(int)$_SESSION['room']." OR ".(int)$_SESSION['room']." = 0)

If $_SESSION['room'] is empty (user haven't specified number of rooms), You get

AND (Rooms = 0 OR 0 = 0)

... which is always TRUE, so the "rooms" condition doesn't apply at all. If user specified number of rooms, the query would look like:

AND (Rooms = 8 OR 8 = 0)

The 8 = 0 is always FALSE, so effectively, You have the condition You need: Rooms = 8.

answered Aug 2, 2017 at 11:53

Roman Hocke

4,2411 gold badge22 silver badges36 bronze badges

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

8 Comments

Diogo Alves Over a year ago

The data in the DB is set as null so it would try and look for 0=null which is false right?

Roman Hocke Over a year ago

Depends on how do You want to handle NULLs in database. If user specifies 8 rooms, do You want to include NULL records or not? You can alter the condition any way You wish, like AND ((Rooms = '.(int)$rooms.' OR Rooms IS NULL) OR '.(int)$rooms.' = 0) etc.

Diogo Alves Over a year ago

I only want to show the nulls if the input is empty, if any value is inserted i want to only show that value

symcbean Over a year ago

I, for one, am now completely confused. So $_SESSION['room'] can be an integer or null, The attribute secret.room can be an integer or null, and if either value is null you want to see all the rows in the table? And something here is supposed to be secret?

Diogo Alves Over a year ago

Now i am confused, it's as simple as that, you insert the number of rooms you want, you get the exact matches. If the house as 4 rooms and you inserted 4 you will only get 4. But if i don't want to refer to the rooms I want to display all nulls and not nulls, but only if the session variable is empty

|

Virb · Accepted Answer · 2017-08-02 12:47:48Z

1

In your query, checking NULL isn't the same as checking empty. I'd recommend the following:

AND (Rooms=" .$_SESSION["room"] ." OR Rooms IS NULL OR Rooms <>'')

Also, it's highly recommended filtering the $_SESSION variable before injecting that into MySQL, if it's a number, assign it to $room=(int)$_SESSION['room'] to force it to be an integer.

edited Aug 2, 2017 at 12:47

Virb

1,5781 gold badge16 silver badges25 bronze badges

answered Aug 2, 2017 at 11:43

Aaron Belchamber

1,7682 gold badges17 silver badges20 bronze badges

2 Comments

Diogo Alves Over a year ago

Thanks for the tip, the code didn't work, it displayed no data

symcbean Over a year ago

It should have thrown an error with a null $_SESSION['room'] - that you can't see the error is a problem with your config / processes

E. SALEH · Accepted Answer · 2017-08-02 11:55:13Z

0

There are several solutions, one of them is simply to store the SQL in a string variable, and then add the rooms conditions if the value is not null.

$sql = SELECT ...

if ($_SESSION["room"] !== NULL) { $sql = $sql . ' AND Rooms=".$_SESSION["room"] . " ' }

edited Aug 2, 2017 at 11:55

answered Aug 2, 2017 at 11:47

E. SALEH

11 bronze badge

2 Comments

Diogo Alves Over a year ago

That's exactly what I'm trying to avoid

E. SALEH Over a year ago

Then you can use the CASE WHEN clause ... ex: AND CASE WHEN $_SESSION["room"] <> NULL THEN Rooms=$_SESSION["room"] ELSE TRUE END .... you just need to adapt it a bit I guess

Lekens · Accepted Answer · 2017-08-02 12:46:00Z

Try the query like this: Note i concatenate all php varibles with {} instead of ". ."

SELECT * FROM secret WHERE secretIDName='1' AND NatureTypeIDName LIKE '%{$_SESSION["nature"]}%' AND (NettArea >={$_SESSION["NettArea"]} OR NettArea IS NULL) AND ConditionTypeIDName LIKE'%{$_SESSION["lifestyle"]}%' AND ((SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',4),'|',-1)>={$_SESSION["BusinessTypeValuesMin"]} AND SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',4),'|',-1)<={$_SESSION["BusinessTypeValuesMax"]}) OR SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',4),'|',-1) = '') AND (SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',2),'|',-1)='{$_SESSION["BusinessTypeValuesType"]}' OR SUBSTRING_INDEX(SUBSTRING_INDEX(BusinessTypeValues,'|',2),'|',-1)='') AND GarageArea>={$_SESSION["GarageArea"]} AND (LocationIDName LIKE '%{$_SESSION["zone1"]}%' AND LocationIDName LIKE '%{$_SESSION["zone2"]}%' AND LocationIDName LIKE '%{$_SESSION["zone3"]}%') AND (Rooms={$_SESSION["room"]}) LIMIT ($page-1)*$Page, $Page";

Collectives™ on Stack Overflow

MySQL - Return all values if input is empty

4 Answers 4

8 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related