SQL (for mysql) getting good results, but need to exclude some columns

Question

I'm trying to write a custom search that will search "all categories" and individual categories (via a form/select dropdown). I'm having problems with my individual category search. When I test my sql, it returns good results, but I'm getting extra/unwanted categories in my results. I'm getting those extra categories in my results due to my AND and OR clauses.

For example, in my test query for "vector" I'm getting results from 3 categories because the word "vector" appears in titles, descriptions, or tags in 3 separate categories. What I want to do is restrict that to only showing the appropriate section (which is chosen from the form/select dropdown). So for example, I'm getting results from category 8, 12 and 13, but I only want to show category 8 (because that's what I'm selecting in my form/dropdown - that's my WHERE clause below).

My results make total sense, but I'm not sure how to only show only results from category 8 (in my test example). I basically want to do some further filtering to reduce the result set to only category 8.

SELECT title,category 
FROM stories 
WHERE category=8 
AND title LIKE '%vector%' 
OR desc LIKE '%vector%' OR tags LIKE '%vector%' 
ORDER BY time DESC

Answer 1

I could be wrong, but based on the column list from the SELECT bit of the query, the category column is called category, but in the WHERE clause, it is cat. Should they both be category? As a sidenote, if you know that the category is 8, why do you need to select that from the database?

But more importantly, you should parenthesize your logic in the WHERE clause, probably something like:

WHERE
    category=8
    AND (
        title LIKE '%vector%' OR
        desc LIKE '%vector%' OR
        tags LIKE '%vector%'
    )

That way the category will always be part of the query, but only one of the title/desc/tags bits need to match.

Answer 2

just add parentheses as appropriate:

SELECT title,category 
FROM stories 
WHERE category=8 
AND (title LIKE '%vector%' 
 OR desc LIKE '%vector%' OR tags LIKE '%vector%')
ORDER BY time DESC

Answer 3

You have a little problem with your operator precedence. AND has a higher precedence than OR, so your query is currently parsed as such:

SELECT title,category 
FROM stories 
WHERE (category=8 AND title LIKE '%vector%')
OR desc LIKE '%vector%' OR tags LIKE '%vector%' 
ORDER BY time DESC

What you really want is this query:

SELECT title,category 
FROM stories 
WHERE category=8 
AND
(title LIKE '%vector%' OR desc LIKE '%vector%' OR tags LIKE '%vector%')
ORDER BY time DESC

More information about operator precedence is MySQL is available in the documentation:

11.2.1: Operator Precedence

Answer 4

Missing brackets?

Try

SELECT title,category  
FROM stories 
WHERE category=8 
AND (title LIKE '%vector%' 
OR desc LIKE '%vector%' OR tags LIKE '%vector%') 
ORDER BY time DESC