Sql select where clause issue

Question

Hope someone can help this is driving me made.

Can anybody tell me what is wrong with this select, It currently does the search properly but it checks every result in "Galleries" rather than just the ones in "category: summer" and "Status: used".

So what i want it to do is search for the keywords ($find) only if they are in category "summer" and Status "used". Any ideas what i need to do. Lol ill take anything at this point. stressed :)

$result = mysql_query("
  SELECT
     Gallery_URL,
     Thumbnail_URL,
     Nickname,
     Description 
  FROM Galleries
  WHERE 
    Category = 'summer' 
    AND Status = 'Used'
    AND Nickname LIKE '%$find1%'
    OR Nickname LIKE '%$find2%'
    OR Nickname LIKE '%$find3%'
    OR Description LIKE '%$find1%'
    OR Description LIKE '%  $find2%'
    OR Description LIKE '%$find3%'
  LIMIT 0 , 10");

score 1 · Answer 1 · answered Apr 13 '13 at 14:20

Add parentheses like this:

SELECT Gallery_URL, Thumbnail_URL, Nickname, Description 
FROM Galleries  
WHERE 
   Category = 'summer' 
   AND Status = 'Used' 
   AND (Nickname LIKE '%$find1%' 
   OR Nickname LIKE '%$find2%' 
   OR Nickname LIKE '%$find3%' 
   OR Description LIKE '%$find1%' 
   OR Description LIKE '%  $find2%' 
   OR Description LIKE '%$find3%')

score 1 · Accepted Answer · edited May 23 '17 at 10:25

You are shortcircuiting the AND and OR operators. Try to isolate the OR by placing them in a group,

SELECT  Gallery_URL, 
        Thumbnail_URL, 
        Nickname,
        Description 
FROM    Galleries 
WHERE   Category = 'summer' AND 
        Status = 'Used' AND 
        (
            Nickname LIKE '%$find1%' OR 
            Nickname LIKE '%$find2%' OR 
            Nickname LIKE '%$find3%' OR 
            Description LIKE '%$find1%' OR 
            Description LIKE '% $find2%' OR 
            Description LIKE '%$find3%' 
        )
LIMIT   0 , 10

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

score 0 · Answer 3 · edited Apr 13 '13 at 14:42

Try grouping the expressions in your where clause. For example

$result = mysql_query("SELECT Gallery_URL, Thumbnail_URL
, Nickname, Description 
FROM Galleries 
WHERE Category = 'summer' 
AND Status = 'Used' 
AND (Nickname LIKE '%$find1%' 
OR Nickname LIKE '%$find2%' 
OR Nickname LIKE '%$find3%' 
OR Description LIKE '%$find1%' 
OR Description LIKE '% $find2%' 
OR Description LIKE '%$find3%') 
LIMIT 0 , 10");

Sql select where clause issue

3 Answers3

Linked