WHERE clause headache in SELECT!

[darthmahon]

Hey,

Ok I want to be able to select items from my news table that only relates to the category specified but it needs to search all of the fields that have FULLTEXT as well.

I have the following:

$news = "SELECT * FROM NEWS WHERE category = $cat AND title LIKE '%" . $VARDescription . "%' OR shorttext LIKE '%" . $VARDescription . "%' OR text LIKE '%" . $VARDescription . "%' LIMIT $limitvalue, $limit";

Problem is, it selects everything from the table if for example, title=test, it will find all entries with test, even if the category is not the one I specified in my search.

I only want it to select the data that relates to the category I have selected in my search, but at the same time it needs to check all of the fields to make sure it only gets the relevant data. Any ideas?

Does that make sense?

Cheers,
Chris

[tekky]

try this... (order of precedence kinda thing)

$news = "SELECT * FROM NEWS WHERE category = $cat AND (title LIKE '%" . $VARDescription . "%' OR shorttext LIKE '%" . $VARDescription . "%' OR text LIKE '%" . $VARDescription . "%') LIMIT $limitvalue, $limit";

should result in a SELECT that works sorta....

WHERE cat=1 AND
title like %me% or shorttext like %me% or text like %me%
LIMIT 1, 5

each line being a seperate portion of the clause, ie: any ONE of the or's needs to be true, and cat must = 1 inorder for the WHERE to be true

[darthmahon]

Wonderful, works exactly how I wanted 🙂

Thank you very much! 😃