How to Use AND and OR in MySQL

Question

i want to create a search in my web. in search form i have Category and Input query for search. for example in category im choice jmovies and in input query im type keyword for ashi.

my code

SELECT * 
FROM master_post 
WHERE category = 'jmovies' 
    AND master_post_name LIKE '%ashi%' 
    OR altname LIKE '%ashi%'

so category = jmovies and keyword = ashi.

for example i have table like this

category     master_post_name    alt_name
===========  ==================  ===============
JMovies      ashi_include_1      not_include
JMovies      ashi_include_2      not_include
JMovies      ashi_include_3      not_include
JMovies      not_include         ashi_include_1
JMovies      not_include         not_include
Drama        ashi_in_drama       ashi_include_2

if i run with that code result is

category     master_post_name    alt_name
===========  ==================  ===============
JMovies      ashi_include_1      not_include
JMovies      ashi_include_2      not_include
JMovies      ashi_include_3      not_include
JMovies      not_include         ashi_include_1
Drama        ashi_in_drama       ashi_include_2

the problem is, cause im choice jmovies, category drama should not be in my result, even if drama have ashi in master_post or alt_name.

result must be like this

category     master_post_name    alt_name
===========  ==================  ===============
JMovies      ashi_include_1      not_include
JMovies      ashi_include_2      not_include
JMovies      ashi_include_3      not_include
JMovies      not_include         ashi_include_1

even if im choice jmovies and not fill input keyword result is not showing only jmovies but it showing drama too.

NOTE: alt_name and master_post_name have same keyword.

Isn't this just a case of adding some brackets to your query? — Chris, May 12 '18 at 15:51
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 12 '18 at 15:52
:-) https://en.oxforddictionaries.com/punctuation/parentheses-and-brackets — Chris, May 12 '18 at 15:55
ah i see, i know that must using "(" and ")" but i dont know how to use it and what name that is, so that is parentheses. — Jazuly, May 12 '18 at 15:58
Possible duplicate of [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) — Mark Rotteveel, May 12 '18 at 15:59

score 2 · Accepted Answer · edited May 12 '18 at 15:58

2

I'm not sure if I understand your question correctly, but you may try the following query:

SELECT * FROM master_post 
WHERE category = 'jmovies' AND (master_post_name LIKE '%ashi%' OR altname LIKE '%ashi%')

I suspect that the "problem" here is the fact that the AND operator has a greater precedence than the OR operator.

edited May 12 '18 at 15:58

Mark Rotteveel

100,966
191
140
197

answered May 12 '18 at 15:52

Bart Hofland

3,700
1
13
22

score 1 · Answer 2 · edited May 12 '18 at 15:59

1

I'm not an expert but give parenthesis a go:

SELECT * FROM master_post 
WHERE category = 'jmovies' AND (master_post_name LIKE '%ashi%' OR altname LIKE '%ashi%')

see this answer: How exactly does using OR in a MySQL statement differ with/without parentheses?

edited May 12 '18 at 15:59

Mark Rotteveel

100,966
191
140
197

answered May 12 '18 at 15:52

Shefeto

178
1
10

How to Use AND and OR in MySQL

2 Answers2