Results 1 to 7 of 7

Thread: Query question

  1. #1
    Join Date
    Oct 2002
    Posts
    32

    Question Unanswered: Query question

    I want to build a Frequently Asked Questions (FAQS) page query that will find matches if words in a user-specified search string are found in any order in the question and/or answer fields combined.

    I'm replacing spaces in a user-specified search string with the percent sign as follows:

    $SearchString= str_Replace (' ', '%', $SearchString);

    I'm building the where clause for the FAQS page as follows:

    $Where = "WHERE (Question LIKE '%$SearchString%' OR Answer LIKE '%$SearchString%')";

    This query only returns a match if all the search words are found in the Question field or if all the search words are found in the Answer field. Is there any way I can get the query to return a match if all the search words are found in the two fields combined (e.g., Question field contains "Why won't this work?", Answer field contains "I'm not sure.", and SearchString is "Why%not")?

    Thanks!

    Timm

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE concat(question,answer) like '%searchstring%'


    rudy

  3. #3
    Join Date
    Oct 2002
    Posts
    32

    Question Follow-up question

    Thanks, Rudy. That was the piece I needed.

    I now have a new question. If Question field contains "Why won't this work?", Answer field contains "I'm not sure.", and SearchString is "Why%not", the match is found. Great!

    However, if the search string is "not%Why", a match is NOT found.

    It appears that for a match to be found, the search words have to appear in the order in which they are listed (not necessarily consecutive order). Is there a way to get around this?

    Thanks!

    Timm

  4. #4
    Join Date
    Oct 2002
    Posts
    32

    Question Follow-up question

    Thanks, Rudy. That was the piece I needed.

    I now have a new question. If Question field contains "Why won't this work?", Answer field contains "I'm not sure.", and SearchString is "Why%not", the match is found. Great!

    However, if the search string is "not%Why", a match is NOT found.

    It appears that for a match to be found, the search words have to appear in the order in which they are listed (not necessarily consecutive order). Is there a way to get around this?

    Thanks!

    Timm

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is there a way to get around it? yes, break up your search strings so that the pieces are independent -- you're right, '%why%not%' implies that particular sequence of words

    if X is the column being searched, in your case concat(Question,Answer), then you currently have

    WHERE X LIKE '%A%B%'

    you could add

    OR X LIKE '%B%A%'

    to get both original and reverse combinations, but you'd have to split the words up to be able to reverse them

    but wait a sec -- if you have three words, you need 6 ORs!!

    clearly not the way to go

    like i said, break up your words -- you'd do that in either case, but here, you don't have to rearrange them into all possible combinations:

    WHERE X LIKE '%A%'
    AND X LIKE '%B%'

    notice that i used AND

    most search engines, e.g. google, return results which contain any of your search words, with, obviously, those that contain all of them being ranked higher

    if you want results that contain any, not necessariyl all, then use OR


    rudy

  6. #6
    Join Date
    Oct 2002
    Posts
    32
    Okay, I have two more sets of questions.

    First, if I continue going in the current direction, how do I break a user-submitted string into pieces that I can test?

    Second, I read some info about match() against() statements. Is this method preferable to what I am trying to do now? If it is, how do I use the alter table statement to get a full text index, and does it permanently alter the table or is it a temporary thing?

    Again, THANKS!

    Timm

    P.S. Sorry for double-submitting my previous post. I clicked back and/or refreshed the screen after viewing the post.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. do the unstringing in a scripting language like php

    2. i've never used MATCH, and don't intend to start

    see 6.8 MySQL Full-text Search


    rudy

Posting Permissions

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