CODEWITHSUNDEEP
phpmysqlsearch
user1433995
user1433995

Reputation:

MySQL: Search all fields from a specific type field

I have a MySQL users table with multiple fields including interger user type field (admin or normal). I am looking to have a master search which can search given string in all fields but with a where clause of user type (for a specific type)

I am using

$sql = "SELECT * FROM users WHERE user_fname like '%".$data."%' OR user_lname like '%".$data."%' OR user_login like '%".$data."%' OR user_callsign like '%".$data."%' AND user_type='2'";

But When I search, it query the whole table neglecting my user_type clause. I am running out of ideas, please suggest me how would I over come this.

Thanks,

Upvotes: 0

Views: 83

Answers (3)

haris
haris

Reputation: 3875

$sql = "SELECT * FROM users WHERE (user_fname like '%".$data."%' OR user_lname like '%".$data."%' OR user_login like '%".$data."%' OR user_callsign like '%".$data."%') AND user_type='2'";

Upvotes: 0

Cheruvian
Cheruvian

Reputation: 5867

You need to parenthesize. Order of operations for Or and And are equal which means it'll go from left to right

Your query should look more like this:

SELECT      * 
FROM    users 
WHERE   (user_fname like '%".$data."%' 
OR      user_lname like '%".$data."%' 
OR      user_login like '%".$data."%' 
OR      user_callsign like '%".$data."%')
AND     user_type='2'

Also you should be sanitizing your input and/or using prepared queries. Otherwise you leave yourself open to massive SQL injection attacks

Look into using PDO or MySQLi (if you must).

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You need to organize your OR conditions like (all ORs) and then apply AND filter so it will not neglect user_type='2' AND part

$sql = "SELECT * FROM users
 WHERE (user_fname like '%".$data."%' 
 OR user_lname like '%".$data."%'
 OR user_login like '%".$data."%'
 OR user_callsign like '%".$data."%')
 AND user_type='2'";

Upvotes: 2

Related Questions