SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Where Clauses (How to?)

    Hi All,

    I am trying to put together a somewhat complicated search query.

    I have 5 search boxes for different values that i am using to query my database - keywords and years. I'm using full-text searching.

    I'm not sure, though, how to put all this together in one search query because I need ( I think) to use multiple where clauses in the query with different columns.

    For example, I need something like:

    Select * WHERE MATCH(column1) AGAINST('word word' IN BOOLEN MODE)
    AND WHERE MATCH(colum2) AGAINST('word')

    Is something like this possible? I am having trouble finding solutions to this on the web.
    Thanks in advance!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes it's possible -- just don't repeat the WHERE keyword
    Code:
    SELECT ...
      FROM ...
     WHERE MATCH(column1) AGAINST('word word' IN BOOLEAN MODE)
       AND MATCH(column2) AGAINST('word')
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, r937 -

    Is it also possible to change the 'AND' to be:

    SELECT -
    FROM -
    WHERE MATCH(column1) AGAINST('word' IN BOOLEAN MODE)
    OR / NOT MATCH(column2) AGAINST(word)

    Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, obvioulsy, you cannot write OR / NOT

    you could write AND, or you could write OR

    you could write AND NOT, or you could write OR NOT

    but you doesn't has to call me johnson
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks very much - still learning the ins and outs of mysql.

    cheers!

  6. #6
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swto View Post
    Thanks very much - still learning the ins and outs of mysql.

    cheers!
    Off topic:
    Full text searching is support by Myisam table only, not by the InnoDB


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
  •