CODEWITHSUNDEEP
phpmysqlmysqli
Sideshow
Sideshow

Reputation: 1351

MySQL using LIKE and AND not getting correct results

Hopefully a simple question to people with better brains than me - I am having trouble with my MySQL syntax when Selecting rows from a table.

$query="SELECT auth, first_name, last_name FROM main_user
    WHERE (auth='1' OR auth='2'
      AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%')
    LIMIT 10";

I am basically wanting to retrieve the client name based on the user input whos 'auth' is set to either 1 or 2.

My current results seem to be bringing back results where only 'auth' values are matching.

Upvotes: 0

Views: 52

Answers (5)

Rubin Porwal
Rubin Porwal

Reputation: 3845

Please try executing the below mentioned sql select query.

I have replaced OR operator with IN in below sql query

 $query="SELECT auth, first_name, last_name FROM main_user
WHERE auth in ('1','2')
  AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%')
LIMIT 10";

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try to put these OR in brackets like so:

SELECT 
  auth, 
  first_name, 
  last_name 
FROM main_user
WHERE (auth = '1' OR auth = '2')
  AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%')
LIMIT 10;

Upvotes: 2

juergen d
juergen d

Reputation: 204844

You got the () wrong. Try

SELECT auth, first_name, last_name
FROM main_user
WHERE (auth = '1' OR auth = '2')
AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%'
LIMIT 10

or simpler

SELECT auth, first_name, last_name
FROM main_user
WHERE auth in ('1','2')
AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%'
LIMIT 10

Upvotes: 1

Vikdor
Vikdor

Reputation: 24134

auth='1' OR auth='2' AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%'

This is equivalent to 

(auth='1') 
OR (
       auth='2' 
   AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%'
   )

You should instead write it as 

(
    auth='1' 
OR  auth='2'
)  
AND CONCAT(first_name,' ',last_name) LIKE '%".$client_name."%'

Upvotes: 2

Perleone
Perleone

Reputation: 4038

You need to put the two OR clauses in parens:

WHERE ( ( auth='1' OR auth='2' )
     AND ...

Upvotes: 0

Related Questions