How to use OR & AND in WHERE clause in mysql

Question

I'm new to Mysql & have a table where at presently i'm fetching data as

SELECT * FROM tablename WHERE name='$user' AND date='$selecteddate'

Now i want to add 1 more column named status_id where i want to select from to values i.e 1 or 2

I tried this query

 SELECT * FROM tablename WHERE (name='Ankit') AND (date='2015-04-23') AND (status_id='1' OR status_id='2')

but didn't work out.

Please help.

Seems to be query is ok. What you expect and what you getting? — Mahadeva Prasad, Apr 23 '15 at 06:43
I tried this in mysql & it gave error as "SELECT * FROM karmadetails WHERE (name='Ankit') AND (date='2015-04-23') AND (status_id='2' OR status_id='1') " MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT * FROM tablename WHERE (name='Ankit') AND (date='2015-04-23') AND (st' at line 1 — AK_56, Apr 23 '15 at 06:43
If your first query working? what is the content of `$selecteddate` in your first query? — Jens, Apr 23 '15 at 06:43
how do you execute this query with php? the problem might be there.... — Luthando Ntsekwa, Apr 23 '15 at 06:45
I see a single and double quote at the beginning of your query, which doesn't look right — Luthando Ntsekwa, Apr 23 '15 at 06:46
Data type is int. My initial query is working fine. $selecteddate is the date selected by the user. — AK_56, Apr 23 '15 at 06:47
I think you can change the column name 'date' to some other name http://stackoverflow.com/questions/12775671/is-date-a-valid-mysql-column-name — Mahadeva Prasad, Apr 23 '15 at 06:48
So if type of status_id is int, you don't need the '. `status_id = 1` — Raphaël Althaus, Apr 23 '15 at 06:48

Raphaël Althaus · Accepted Answer · 2015-04-23T06:49:19.677

Well, you just need the elements concerned by the or clause to be between parenthesis.

They are not "needed", by the way (you could use precedence order of AND / OR), but I would say this is the best way for readability.

And if status_id data type is int, you don't need the ' ' around 1, and 2.

SELECT * 
 FROM tablename 
WHERE name='Ankit' 
AND date='2015-04-23' 
AND (status_id=1 OR status_id=2)

By the way, in this case, you may use an IN clause

AND status_id in (1, 2)

score 1 · Answer 2 · answered Apr 23 '15 at 06:44

1

Something like this

"SELECT * FROM tablename 
 WHERE name='Ankit' 
 AND date='2015-04-23' 
 AND (status_id='1' OR status_id='2')";

answered Apr 23 '15 at 06:44

Tom

score 0 · Answer 3 · edited Apr 23 '15 at 07:21

0

Try with using IN

AND status_id IN (1, 2)

edited Apr 23 '15 at 07:21

Mariusz Jamro

answered Apr 23 '15 at 06:48

Rex Rex

3 Answers3