Query for MYSQL with IF to validate several table fields

Asked

Viewed 1,056 times

0

I am developing a Property Classifieds, so I need to filter some fields if the internet user wants, such as: Number of rooms, Number of parking spaces, Number of bathrooms, etc.

I need Query to have IF so I can validate.

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 variable $nquarties not empty it would filter through Rooms.

sql

  • I think it is better to make these conditions in the application, via PHP (in case) would be more pleasant.

  • @lvcs I have started this process in PHP but as I need several filters, it is getting very complex. Because validating the conditions directly in the query will be easier and I would only use query to validate everything.

2 answers

1


A 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)

  • How to check if a variable in SQL is empty and SE yes query?

  • It didn’t work out

  • 1

    You need to configure the default value (in the example I put "-1") to all variables. When you specify a different default value, the query will logically filter by variable.

  • I did, but to finish successfully I need that inside the AND (Rooms = '$nquartos' or '$nquartos' = -1 ORDER BY price ASC ). Got it? I need ORDER BY price ASC inside AND, there’s a way?

  • 1

    Not possible. What is your intention of ORDER BY within the WHERE clause?

  • Before ORDER BY, I have this trap AND ('$maiormenor' = '1' ORDER BY price ASC). Where the variable $maiormenor must have value 1 and then sort by price. Only this line makes a mistake, because I think I try to see if it has value 1 and then for trying to order.

  • 1

    Do you want the query to return the results ordered by price when the variable $maiormenor is equal to 1? If yes, it is another problem, another subject, another question. Post a new question, to facilitate those who have the same doubt.

  • Let’s go continue this discussion in chat.

Show 3 more comments

1

You could validate "If it has a value equal to the column or if it has an 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' = '');

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.