CODEWITHSUNDEEP
phpmysql
Rodney
Rodney

Reputation: 624

MySQL select using LIKE and AND in same statement not working

If I use this statement:

$query = "select * from Products where category_description='".$search_dept."' ORDER BY $orderby LIMIT $offset, $rowsPerPage";

I get 3 results returned, which is correct.

If I use this statement:

$query = "select * from Products where material like \"%$trimmed%\" OR ProductName like \"%$trimmed%\" ORDER BY $orderby LIMIT $offset, $rowsPerPage";

I get correct results.

However, if I try to combine them:

$query = "select * from Products where category_description='".$search_dept."' AND material like \"%$trimmed%\" OR ProductName like \"%$trimmed%\" ORDER BY $orderby LIMIT $offset, $rowsPerPage";

I am getting incorrect results. It is returning too many. Any ideas what I'm doing wrong?

Upvotes: 0

Views: 214

Answers (3)

xdazz
xdazz

Reputation: 160853

You need ().

... AND ( material like \"%$trimmed%\" OR ProductName like \"%$trimmed%\" )

Upvotes: 1

Chris Trahey
Chris Trahey

Reputation: 18290

I suggest adding parenthesis around the two logical sets of criteria (more importantly the OR section). As it is, you are implicitly trusting order of operations, and I don't think it's deciding the way you want it it. Parenthesis should do the trick:

$query = "select * from Products 
    where category_description='".$search_dept."' 
    AND (material like \"%$trimmed%\" OR ProductName like \"%$trimmed%\") 
    ORDER BY $orderby LIMIT $offset, $rowsPerPage";

Upvotes: 3

flowfree
flowfree

Reputation: 16462

You need to group the second and third conditional.

$query = "SELECT * FROM Products 
          WHERE category_description='".$search_dept."' AND 
               (material LIKE \"%$trimmed%\" OR ProductName LIKE \"%$trimmed%\") 
          ORDER BY $orderby 
          LIMIT $offset, $rowsPerPage";

Upvotes: 1

Related Questions