PHP MySQL Query ignores AND statement

Question

I have the following PHP query that executes a MySQL query without error but is ignoring the city portion of the query at the end? Is there a way I have to arrange the AND/OR statements?

$sql_query = "SELECT ListOfficeMLSID AS list_office_mlsid, MLSNumber AS mls_number, City AS city, ListPrice AS list_price FROM my_table WHERE ListOfficeMLSID =\"office6\" OR ListOfficeMLSID =\"office7\" OR ListOfficeMLSID =\"office2\" AND ListPrice >= 1500000 AND city = \"Bonita Springs\" OR city = \"Estero\"";

It's because AND statements have a priority than OR statements - `1 AND 2 OR 3` is true if 3 is true, or if 1 and 2 are both true. Use brackets to force things to be validated in the order you want - `1 AND (2 OR 3)` is true if 1 is true and if at least one of 2 and 3 is true. — andrewsi, Dec 19 '13 at 18:15

Leonardo · Accepted Answer · 2013-12-19T18:29:16.993

2

$sql_query = "SELECT ListOfficeMLSID AS list_office_mlsid, MLSNumber AS mls_number, City AS city, ListPrice AS list_price FROM my_table WHERE ListOfficeMLSID IN('office6','office7','office2') AND ListPrice >= 1500000 AND city IN('Bonita Springs', 'Estero')";

edited Dec 19 '13 at 18:29

answered Dec 19 '13 at 18:18

Leonardo

736
4
11

Is use of IN better performance-wise or more for keeping the query easier to maintain? – Rocco The Taco Dec 19 '13 at 18:36
1

I did it like that because it'll be easy to mantain, but according to this [post](http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance): it's also performance-wise – Leonardo Dec 19 '13 at 18:42

Sean Doe · Answer 2 · 2013-12-19T18:47:27.993

$sql_query = "SELECT ListOfficeMLSID AS list_office_mlsid, MLSNumber AS mls_number, City AS city, ListPrice AS list_price FROM my_table WHERE (ListOfficeMLSID =\"office6\" OR ListOfficeMLSID =\"office7\" OR ListOfficeMLSID =\"office2\") AND ListPrice >= 1500000 AND (city = \"Bonita Springs\" OR city = \"Estero\")";

As I understand you want to get record that has ListOfficeMLSID in (office2, office6 or office6) AND ListPrice >= 1500000 AND city in(Bonita Springs or Estero)?

Your query will return records that suits at least one of your conditions

PHP MySQL Query ignores AND statement

2 Answers2