SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Oct 2002
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy WHERE clause headache in SELECT!

    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:

    Code:
     $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

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2001
    Location
    USA
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Reevaluate your SQL query. You are doing some pretty loose checking; which is most likely causing all the entries to be returned.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    classic problem of evaluating ANDs and ORs

    ANDs take precedence

    you have WHERE x AND a OR b OR c

    this is being evaluated as WHERE ( x AND a ) OR b OR c

    what you want is WHERE x AND ( a OR b OR c)

    you must code the parentheses to force that sequence of evaluation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •