What is wrong with my SQL Statement?

Question

I'm making a site where the user can select a rental property that has two fields they can select from, Furnished and Pets. The options in both select boxes is Yes and No.

<select name="furnished">
  <option value="">
  <option value="yes">Yes</option>
  <option value="no">No</option>
</select>

<select name="pets">
  <option value="">
  <option value="yes">Yes</option>
  <option value="no">No</option>
</select>

I am writing a SQL statement based on what the user picks in those fields.

$sql = 'SELECT *
       FROM properties
       WHERE num_bedrooms >= ' . $_GET['num_bedrooms'] . 
       ' AND num_bathrooms >= ' . $_GET['num_bathrooms'];

if($_GET['furnished'] == 'yes') { //if the furnished is set to yes
  $sql .= ' AND furnished = "yes" OR furnished = "partially"';
} else if($_GET['furnished'] == 'no') { //if the furnished is set to no
  $sql .= ' AND furnished = "no" OR furnished = "description"';
} 

if($_GET['pets'] == 'yes') { //if the pets is set to yes
  $sql .= ' AND pets = "yes" OR pets = "cats" OR pets = "dogs"';
} else if($_GET['pets'] == 'no') { //if the pets is set to no
  $sql .= ' AND pets = "no" OR pets = "description"';
}

If the user chooses yes for furnished, I want it to display all properties that are furnished (yes) OR that are partially furnished(partially). If the user chooses No, I want it to display all properties that are not furnished(no) OR that have a special description(description).

If the user chooses yes for pets, I want it to display all properties that allow all pets (yes) OR that allow only cats(cats) OR that allow only dogs(dogs). If the user chooses No, I want it to display all properties that don't allow pets(no) OR that have a special description(description).

As an example, this is the outputted SQL statement if the user chooses Yes on Furnished and Yes on Pets:

SELECT * FROM properties 
WHERE num_bedrooms >= 1 
AND num_bathrooms >= 1 
AND furnished = "yes" OR furnished = "partially" 
AND pets = "yes" OR pets = "cats" OR pets = "dogs"

The problem is that it will currently return results that meet EITHER of the furnished or pets requirements in the where clause, whereas I need it to return results that meet BOTH of the furnished and pets requirements in the where clause.

So it will currently return a result that says has the Furnished set to yes, and the pets set to no. How can I have it return all results with furnished set to yes AND pets set to yes?

What is wrong with my SQL statement?

You're NOT escaping input variables like you should. Have a look at [mysql_real_escape_string()](http://php.net/manual/en/function.mysql-real-escape-string.php) or [PDO::quote()](http://php.net/manual/en/pdo.quote.php) or the escaping/quoting method used in your database librariy… — rodneyrehm, Oct 29 '11 at 07:55

score 4 · Accepted Answer · answered Oct 29 '11 at 07:41

4

AND comes before OR in operator precedence, so your query reads like

SELECT * FROM properties 
WHERE 
(num_bedrooms >= 1 AND num_bathrooms >= 1 AND furnished = "yes")
OR (furnished = "partially" AND pets = "yes")
OR pets = "cats" 
OR pets = "dogs"

which is not quite what you expected. Try

SELECT * FROM properties 
WHERE num_bedrooms >= 1 
AND num_bathrooms >= 1 
AND (furnished = "yes" OR furnished = "partially")
AND (pets = "yes" OR pets = "cats" OR pets = "dogs")

or even better

SELECT * FROM properties 
WHERE num_bedrooms >= 1 
AND num_bathrooms >= 1 
AND furnished IN ("yes", "partially")
AND pets IN ("yes", "cats", "dogs")

You shouldn't do SELECT * try naming the columns you need!

answered Oct 29 '11 at 07:41

rodneyrehm

13,442
1
40
56

Thank you very much for your help! Why is it important I do not do SELECT *? I have many columns that I need to return for each property so I don't see the point in writing them all out. – zeckdude Oct 29 '11 at 07:56
unless you really need all your columns, * would lead to an unnecessary communication overhead between php and mysql (memory and cpu wasted for nothing). (amongst other reasons…) – rodneyrehm Oct 29 '11 at 07:58

score 3 · Answer 2 · answered Oct 29 '11 at 07:39

3

Put parens around the various OR'd things.

$sql .= ' AND (furnished = "yes" OR furnished = "partially")';

$sql .= ' AND (pets = "yes" OR pets = "cats" OR pets = "dogs")';

et cetera.

answered Oct 29 '11 at 07:39

Amber

507,862
82
626
550

What is wrong with my SQL Statement?

2 Answers2