CODEWITHSUNDEEP
phpmysqlsql
user994535
user994535

Reputation: 710

Mysql error with OR AND

I have table with the following data in table clients

------------------
|id|client|status|
------------------
|1 |331   |  0   |
|2 |331   |  1   |
|3 |331   |  2   |
|4 |331   |  1   |
|5 |331   |  0   |
|6 |222   |  0   |
------------------

I like to retrieve all the fields from the table clients where client is 331 and having status 0 or 1. That means I will be getting id 1,2,4,5 as the result.

But when i used this sql 

$query = mysql_query("select * from clients where client=331 AND status=0 or status=1")

I am getting id 1,2,4,5,6 instead.

Whats the correct way to get the data?

Upvotes: 1

Views: 79

Answers (5)

zellio
zellio

Reputation: 32484

Parenthetic statements are your friend. SQL AND before OR as AND has higher precedence than OR, to overcome this you need to use these things ( )

You are looking for

SELECT * FROM clients WHERE client=331 AND (status=0 OR status=1)

Upvotes: 0

carpii
carpii

Reputation: 2001

Remember operator precedence!

Your criteria should be..
client=331 AND (status=0 or status=1)

or for completeness sake..

client=331 AND status IN (0,1)

Upvotes: 1

janoliver
janoliver

Reputation: 7824

$query = mysql_query("select * from clients where client=331 AND (status=0 or status=1)")

Your code reads "if (client is 331 and status is 0) or (status is 1)"

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453212

Use

select * from clients where client=331 AND (status=0 or status=1)

The order of precedence for logical operators is (Not, And, then Or). As And has higher precedence than Or your query is effectively

select * from clients where (client=331 AND status=0) or status=1

Which is not the desired semantics. You could also use

select * from clients where client=331 AND status in (0,1)

Upvotes: 6

rid
rid

Reputation: 63442

Use parenthesis. Replace:

select * from clients where client=331 AND status=0 or status=1

with:

select * from clients where client=331 AND (status=0 or status=1)
  • The first query looks for records which have either (client = 331 and status = 0), or status = 1.
  • The second query looks for records which have client = 331 and either (status = 0 or status = 1).

Upvotes: 2

Related Questions