Multiple WHERE using AND OR in SQL statement

ts10

Hey, I can't quite figure out how to do a multiple WHERE using AND and also using OR in my SQL statement. Here's the example:

mysql_query("SELECT * FROM mel_spysupply.clients WHERE clients_subof IS NULL AND clients_tstamp > $fromDate AND clients_tstamp < $toDate AND clients_fn LIKE '%$_POST[search_criteria]%' OR clients_ln LIKE '%$_POST[search_criteria]%' OR clients_company LIKE '%$_POST[search_criteria]%' OR clients_address LIKE '%$_POST[search_criteria]%' OR clients_city LIKE '%$_POST[search_criteria]%' ORDER BY $sortBy ASC") or die(mysql_error());

Basically, I'm trying to figure out how I can get mySQL to understand that I have three groups of WHEREs

Group 1 always returns WHERE: "clients_subof IS NULL"

Group 2 always returns WHERE: "clients_tstamp > $fromDate AND clients_tstamp < $toDate"

Group 3 always returns ONE OF THESE: " clients_fn LIKE '%$POST[search_criteria]%' OR clients_ln LIKE '%$POST[search_criteria]%' OR clients_company LIKE '%$POST[search_criteria]%' OR clients_address LIKE '%$POST[search_criteria]%' OR clients_city LIKE '%$_POST[search_criteria]%'"

However, it gets confused with the way I have it above and it never searches the dates right when I have the OR statements in there. However, if I remove the OR statements, the date works perfectly.

Any ideas?

Thanks!!

chads2k2

Check this out and see if this works for you.

You might be able to use AND instead of &&.
Also you might be able to use OR instead of ||.

I just know those two work in MySQL for sure.

mysql_query("SELECT * FROM mel_spysupply.clients WHERE (clients_subof IS NULL) || (clients_tstamp > $fromDate && clients_tstamp < $toDate) && (clients_fn LIKE '%$_POST[search_criteria]%' || clients_ln LIKE '%$_POST[search_criteria]%' || clients_company LIKE '%$_POST[search_criteria]%' || clients_address LIKE '%$_POST[search_criteria]%' || clients_city LIKE '%$_POST[search_criteria]%') ORDER BY $sortBy ASC") or die(mysql_error());

Try that, and see if it works. If it does then just remember you can use ()'s all over the place and it acts like it should. Here is an example of that, it's like in math.

SELECT * FROM myTABLE WHERE ((field1 = 1) || (field2 = 2)) && (field3 = 3);

This would say something like:
Look in field1 and get every record that has a 1 in this field.
Now pull all records where field2 equals 2.
Take all those records that I just got and keep all the ones where field3 is equal to 3. Now return the records if any.

The same principle can be applied all way around. I haven't ran this SQL statement (because I don't have the database) but I am pretty sure it will work.

Take it easy,

Chad