CODEWITHSUNDEEP
mysqlsqldatabase
twan
twan

Reputation: 2659

Why am I getting a wrong result from this SQL query?

I am trying to create a simple search script.

So I wrote the following:

SELECT *
FROM snm_content
WHERE
    state = 1 AND
    `catid` NOT IN ('15', '23') AND
    `title` LIKE '%test%' OR `introtext` LIKE '%test%' OR `fulltext` LIKE '%test%'

The first check, WHERE state = 1 fails, I get a result with a state of -2. How can that be?

This is my result when I use the query in PHPmyadmin:

enter image description here

How can I still get that result when clearly writing state needs to be 1.

Upvotes: 0

Views: 47

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You need to apply your OR Clauses within parentheses

SELECT * FROM snm_content WHERE state = 1 AND `catid` NOT IN ('15', '23') AND 
(`title` LIKE '%test%' OR `introtext` LIKE '%test%' OR `fulltext` LIKE '%test%')

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

You need parentheses:

SELECT *
FROM snm_content
WHERE
    state = 1 AND
    catid NOT IN (15, 23) AND
    (title LIKE '%test%' OR introtext LIKE '%test%' OR fulltext LIKE '%test%');

Because of order of operations rules, your current WHERE clause is being interpreted as this:

WHERE
    ((state = 1 AND
    catid NOT IN (15, 23)) AND
    title LIKE '%test%') OR introtext LIKE '%test%' OR fulltext LIKE '%test%';

That is, the title field would have to match the wildcard expression in order for anything to be returned.

By the way, if you are trying to match the literal word test inside these fields, then consider using REGEXP with word boundaries:

SELECT *
FROM snm_content
WHERE
    state = 1 AND
    catid NOT IN (15, 23) AND
    (title REGEXP '[[:<:]]test[[:>:]]' OR introtext REGEXP '[[:<:]]test[[:>:]]' OR
     fulltext REGEXP '[[:<:]]test[[:>:]]');

Upvotes: 2

Related Questions