Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    239
    Thanks
    47
    Thanked 3 Times in 3 Posts

    Multi word search?

    How can you setup you web seach to do a multi word like search?

    I have a search on my site now that I put in.. it works great if you put in one word and spell it correct... but if you do not spell it right or have more that one word...

    your results are 0...

    if they have multi words and they are spelt correct and the way I have them in the table they will show...


    What can I do about this problem..

    Thanks.. Slayer.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Can't do anything about spelling mistakes. Too bad for the stupid user.

    But suppose the search is for "ocean resort"

    You might do something like:
    Code:
    SELECT * FROM table WHERE somefield LIKE '%ocean%' OR somefield LIKE '%resort%'
    Whether you use OR or AND between the two LIKEs depends on whether the search must find both (all) words or not.

    Since you are showing NONE of your current code, I can't change non-existent code for you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    239
    Thanks
    47
    Thanked 3 Times in 3 Posts
    This is what I currently have..

    PHP Code:
    SELECT FROM campgrounds WHERE (`nameLIKE'%$find%') OR (`cityLIKE'%$find%') OR (`notesLIKE'%$find%') OR (`moreinfoLIKE'%$find%') OR (`amenitiesLIKE'%$find%'ORDER BY `name
    Using this search..

    I would search for New Old Town and it would give me 0 results.
    if I just search town I get 15 results
    if I search just old I get 20 results
    and if I search for new I get 7 results.

    Not sure what I have done wrong.

    Thanks... all help appreciated.


    Slayer.
    Last edited by SlayerACC; 02-20-2013 at 09:19 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    ONE more time:

    You *MUST* split up the $find into SEPARATE WORDS and use LIKE on *EACH* word!

    So if $find is "New Old Town" your query would need to be:
    Code:
    SELECT * FROM campgrounds 
    WHERE (name LIKE '%new%' AND name LIKE '%old%' AND name LIKE '%town%') 
       OR (city LIKE '%new%' AND city LIKE '%old%' AND city LIKE '%town%') 
       OR (notes LIKE '%new%' AND notes LIKE '%old%' AND notes LIKE '%town%') 
       OR (moreinfo LIKE '%new%' AND moreinfo LIKE '%old%' AND moreinfo LIKE '%town%') 
       OR (amenities LIKE '%new%' AND amenities LIKE '%old%' amenities city LIKE '%town%') 
    ORDER BY name
    The above assumes you want to find ALL THREE WORDS (in any order or place) in the SAME field (e.g., maybe moreinfo contains "This town has more old buildings than old."

    If finding *ANY* ONE of the words in any of the fields is good enough, then replace all the AND with OR instead.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    239
    Thanks
    47
    Thanked 3 Times in 3 Posts
    how can you split up the words? and then search all the fields?

    Sorry...


    Slayer.
    Last edited by SlayerACC; 02-20-2013 at 10:17 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    I don't use PHP.

    I'll give it a try, but don't be surprised if I mess up the PHP code.
    Code:
    <?php
    
    $search = $_POST["search"];
    $words = explode( " ", $search ); // get array of words
    $pattern = "";
    for ( $w = 0; $w < count($words); ++$w )
    {
        $word = $wods[$w];
        if ( $word != "" )
        {
            if ( $pattern != "" ) $pattern .= " AND "; // or use " OR " as discussed!
            $pattern .= " ## LIKE '%" . $word . "%' ";
        }
    ]
    if ( $pattern == "" )
    {
        echo "You didn't give me anything to search for!  I quit!";
        exit( );
    }
    
    $pattern = "( " . $pattern . ") ";
    $where = " WHERE " . str_replace( "##", "name", $pattern );
    $where .= " OR " . str_replace( "##", "city", $pattern );
    $where .= " OR " . str_replace( "##", "notes", $pattern );
    $where .= " OR " . str_replace( "##", "moreinfo", $pattern );
    $where .= " OR " . str_replace( "##", "amenities", $pattern );
    
    $sql = "SELECT * FROM campground " . $where . " ORDER BY name";
    
    // remove next line after it starts working
    echo "DEBUG SQL: " . $sql . "<hr/>\n";
    
    $result = mysql_query( $sql ) or die( mysql_error() );
    
    ,,, etc ,,,
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    SlayerACC (02-21-2013)

  • #7
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    239
    Thanks
    47
    Thanked 3 Times in 3 Posts
    Hey Old Pedant..


    this is working.... sort of

    whe I search still for one word... works great...

    when I search for multi...

    I get this error..

    I echoing the search so see what it looks like to..

    ## LIKE '%new%' AND ## LIKE '%old%'
    and DEBUG SQL:

    ArrayDEBUG SQL: Resource id #15
    Thanks.

  • #8
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    239
    Thanks
    47
    Thanked 3 Times in 3 Posts
    I changed it to "OR"

    if ( $pattern != "" ) $pattern .= " AND "; // or use " OR " as discussed!
    $pattern .= " ## LIKE '%" . $word . "%' ";
    and it works like a charm...


    You are awesome...


    thank you!! sooooooo much!!!

    P.S. your php was just fine.... I got it worked out...


    Slayer.
    Last edited by SlayerACC; 02-21-2013 at 12:35 AM.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Quote Originally Posted by SlayerACC View Post
    and DEBUG SQL:
    Code:
    ArrayDEBUG SQL: Resource id #15
    Pardon me, but that's impossible if you actually used the code I showed.

    In my code $sql is nothing but a string. That looks like you echoed the $result.

    And if the OR version worked, the AND version should work, too. But only if you actually had some data that matched the condition! In the $pattern that you showed:
    Code:
    ## LIKE '%new%' AND ## LIKE '%old%'
    that would mean that one of the tested fields has to contain *BOTH* "new" and "old".

    As I said, it depends on what you want.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    239
    Thanks
    47
    Thanked 3 Times in 3 Posts
    you are correct Old Pedant,


    It was from my code.. I was echoing that info to see what the output was gonna be..

    I tested it using "And" too and you are right there as well..

    All works great..

    I wish I asked this question a year ago...

    Thanks again for everything.

    Slayer.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    You know, you COULD offer you users the choice of "Find matches on any words" vs. "Find matches on all words".

    Do something like this:
    Code:
    <label>
        <input type="radio" name="delimiter" checked value=" OR " />
        Find matches on any one or more words
    </label>
    <label>
        <input type="radio" name="delimiter" value=" AND " />
        Find matches only on all words
    </label>
    And then in your PHP code, you do:
    Code:
    $delimiter = $_POST["delimiter"];
    $search = $_POST["search"];
    $words = explode( " ", $search ); // get array of words
    $pattern = "";
    for ( $w = 0; $w < count($words); ++$w )
    {
        $word = $wods[$w];
        if ( $word != "" )
        {
            if ( $pattern != "" ) $pattern .= $delimiter;
            $pattern .= " ## LIKE '%" . $word . "%' ";
        }
    }
    ... rest same ...
    Now you have the best of both worlds: Both kinds of searches for the cost of a pair of radio buttons!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Regular Coder
    Join Date
    Sep 2009
    Location
    Calgary, Alberta
    Posts
    239
    Thanks
    47
    Thanked 3 Times in 3 Posts
    That is a great idea.... I will have to implement that to the website.

    Thanks again for everything.


    Slayer.


  •  

    Posting Permissions

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