Greater than operator not working in a query

Question

Could anyone suggest why the greater than operator is being ignored in this MySQL query?

$sql = "SELECT *
FROM `items`
WHERE `short_description` LIKE '%".$term."%'
    OR `description` LIKE '%".$term."%'
    AND `quantity` > 0
ORDER BY year DESC, denomination ASC, description ASC $max";

I have a similar query on the same site that works

$sql = "SELECT *
FROM `items`
WHERE `category` = '".$cat_id."'
    AND `quantity` > 0
ORDER BY year DESC, denomination ASC, description ASC;";

Everything works well, except the quantity comparison on the first query, Its has got me stumped.

relates to turning the results in to pages using the statement $max = LIMIT 5 created in an option earlier in the page. — user1500618, Jan 31 '13 at 02:53

score 1 · Answer 1 · answered Jan 31 '13 at 02:30

1

Try

$sql = "SELECT *
FROM `items`
WHERE (`short_description` LIKE '%".$term."%'
    OR `description` LIKE '%".$term."%')
    AND `quantity` > 0
ORDER BY year DESC, denomination ASC, description ASC $max";

I am thinking that your OR statement is the problem.

answered Jan 31 '13 at 02:30

luk3thomas

2,512
1
18
20

Yes, great, thanks to all, I learn every day. I really do appreciate the prompt responses for a non-professional like me! – user1500618 Jan 31 '13 at 02:45

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

you should group your OR and AND conditions by enclosing them in a parenthesis,

$sql = "SELECT * FROM `items` 
         WHERE  (`short_description` LIKE '%".$term."%' OR 
                 `description` LIKE '%".$term."%')  AND 
                  `quantity` > 0 
          ORDER BY year DESC, denomination ASC, description ASC $max";

followup question: is $max a variable which contains LIMIT clause?

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?

edited May 23 '17 at 12:27

Community

1
1

answered Jan 31 '13 at 02:28

John Woo

258,903
69
498
492

Ok, have heard of that, do you know of a good reference where i can learn more about this type of hack? Thanks – user1500618 Jan 31 '13 at 02:52
Would using mysql_real_escape_string to convert the user input be enough? – user1500618 Jan 31 '13 at 02:57
[SQL injection that gets around mysql_real_escape_string()](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/5741264#5741264) – John Woo Jan 31 '13 at 03:10

score 0 · Answer 3 · answered Jan 31 '13 at 02:30

0

"SELECT * FROM items WHERE (short_description LIKE '%".$term."%' OR description LIKE 
'%".$term."%') 
AND quantity > 0 
ORDER BY year DESC, denomination ASC, description ASC $max";

Try that... You have an Or condition, and an and.

answered Jan 31 '13 at 02:30

Justin Donohoo

380
2
14

Yes, great, thanks to all, I learn every day. I really do appreciate the prompt responses for a non-professional like me! – user1500618 Jan 31 '13 at 02:46

paxdiablo · Answer 4 · 2013-01-31T02:39:03.627

It's not being ignored, it's just that you misunderstand how it's being applied. The expression:

WHERE `short_description` LIKE '%".$term."%' (call this XX,)
OR `description` LIKE '%".$term."%'          (          YY,)
AND `quantity` > 0                           (      and ZZ.)

is interpreted as:

where XX or (YY and ZZ)

whereas what you probably want is:

where (XX or YY) and ZZ

Hence you have to override the default interpretation as follows:

WHERE (`short_description` LIKE '%".$term."%' OR
       `description` LIKE '%".$term."%')
  AND `quantity` > 0

The reason your second query doesn't have this problem is because you're not mixing AND and OR.

In addition, though unrelated to your specific question), you should be very wary of double-ended like clauses such as like '%something%'. They're real performance killers for decent sized tables and there are ways to improve the performance considerably.

It may not matter in this case if your tables are small but it's something to keep in mind.

Yes, great, thanks to all, I learn every day. I really do appreciate the prompt responses for a non-professional like me! — user1500618, Jan 31 '13 at 02:45

Greater than operator not working in a query

4 Answers4