Searching MySQL for this OR that

[rbudj]

I have a program where I am searching for certain criteria (of course). Here is an example

$search_query = "SELECT $sql_vars, p.id, p.country FROM members WHERE id=1 AND photo=3";

This is not the actual search, just an example.

What I need to do is to search the members table WHERE id = 1 AND photo=3 or photo=4 or photo=5 etc...

My problem is that when the search takes place it does not recognize the OR's, it only recognizes the AND photo=3.

I am probably getting the syntax wrong so can someone provide some insight please. Thanks

[batman]

You will not fisically write out "OR" you will have to use || for that.

$search_query = "SELECT $sql_vars, p.id, p.country FROM members WHERE id=1 AND photo=3 || photo=4 || photo=5";

Im not sure if thats the actuall syntax you will need, but it should be close.

[dave420]

It's usually a good idea to use brackets to seperate the different levels of logic, otherwise you might get unpredictable results...

select * from table where (id = 4) and (something = "else" || something="woo!")

Also, you can use the "in" statement...

select * from table where id=4 and something in ("else", "woo!")

🙂

[sadi_co]

Originally posted by rbudj

My problem is that when the search takes place it does not recognize the OR's, it only recognizes the AND photo=3

You can think of logical expressions (that represent conditions) with ANDs and ORs like arithmetic expressions with and +: (and /) are grouped and computed first, and only after that, + (and -) are computed.

So,
a AND b OR c OR d
is like
a * b + c + d
resulting
(a AND b) OR c OR d

You need to notice that logical expressions go one step further. As they can only yield two possible results, the internal calculations are stopped once that result remains the same no matter the resting calculations.

In expressions with only ANDs (remember that these expressions are computed first), the calculations stop after a false is found: in 'a AND b AND c', if a is false, b and c are not even looked at. Do you see why?

In expressions with only ORs (remember that these expressions are computed last), the calculations stop after a true is found: in 'a OR b OR c', if a is true, b and c are not even looked at. If you still don't see why, you should read a book on SQL or programming.

Originally posted by dave420
It's usually a good idea to use brackets to seperate the different levels of logic, otherwise you might get unpredictable results...

I would say it's ALWAYS a good idea to use parenthesis inside expressions.

And, even though it's not mandatory, I alway put parenthesis around the whole where clauses.