Query for MYSQL with IF to validate multiple table fields

Question

Query for MYSQL with IF to validate multiple table fields

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I'm developing a Real Estate Classifieds, so I need to filter some fields if the user wants them, such as: Number of rooms, Number of parking spaces, Number of bathrooms, etc.

I need Query to have IF so I can validate if the variables are filled can filter the X or Y field or both.

Example:

SELECT type, property_type, city, region, neighborhood, price, rooms,
 bathrooms, parking, content, img1, urlimovel, logo, url 
FROM imoveis where type = '$oquedeseja' AND goal = '$idfinalidade' 
AND property_type = '$idtipo' AND region = '$iduf' AND city = '$idcidade'
AND rooms = '$nquartos'

AND rooms = '$ nquartos'

In the above example it would check if the variable $ nquartos is not empty it would filter by rooms .

sql

asked by anonymous 30.03.2016 / 21:12

2 answers

1

You could validate "If it has equal value to the column or if it has empty value":

SELECT type, property_type, city, region, neighborhood, price, rooms,
 bathrooms, parking, content, img1, urlimovel, logo, url 
FROM imoveis 
where (type = '$oquedeseja' or '$oquedeseja' = '') 
AND (goal = '$idfinalidade' or '$idfinalidade' = '')
AND (property_type = '$idtipo' or '$idtipo' = '') 
AND (region = '$iduf' or '$iduf' = '') 
AND (city = '$idcidade' or '$idcidade' = '')
AND (rooms = '$nquartos' or '$nquartos' = '');

30.03.2016 / 21:54

Php returns null JSON when accented in MySQL How to access classes in Unity 3D (C #)

score 1 · Accepted Answer

One possible solution is to consider a default value when the variable is not selected in the application. For example -1 .

SELECT type, property_type, city, region, neighborhood, price, rooms,
 bathrooms, parking, content, img1, urlimovel, logo, url 
FROM imoveis 
where (type = '$oquedeseja' or '$oquedeseja' = -1) 
AND (goal = '$idfinalidade' or '$idfinalidade' = -1)
AND (property_type = '$idtipo' or '$idtipo' = -1) 
AND (region = '$iduf' or '$iduf' = -1) 
AND (city = '$idcidade' or '$idcidade' = -1)
AND (rooms = '$nquartos' or '$nquartos' = -1)