SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search Optimization

    Hi,

    I had a client point something out I never really thought about.

    A product called "SALA Ultra-Lok" is in the database. If you search "sala ultra-lok", or "sala", or "ultra-lok", etc, no problems it is found.
    But if you search "sala ultra lok" (or any variation without the hyphen), it will not be found.

    I've fixed it with this:
    PHP Code:
    $strings explode(" "$search);
                foreach(
    $strings as $key => $value){
                    
                    
    $q .= 
                        AND (
                            p.productTitle LIKE '%"
    .$this->escape_string($value)."%' OR
                            p.productSKU LIKE '%"
    .$this->escape_string($value)."%' OR 
                            b.brandName LIKE '%"
    .$this->escape_string($value)."%'
                        )"
    ;
                        
                } 
    Is this right? Or is there a better way?

    Cheers,
    Rhys

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    $this->escape_string

    this function has no meaning without code, so the question cant be answered.

    preg_replace("~[\W_]+~","%",$search);

    (untested)

    Idea being: Anything that isnt a alphanum = %.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That looks more efficient. I'll give it a whirl. Cheers.

  4. #4
    SitePoint Enthusiast VideoWhisper's Avatar
    Join Date
    Dec 2008
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That looks fine.

    But it will return many results if people search for something like a-team as "a team" because it returns everything with a in it.

    A quick fix would be to use only search terms greater than 2 chars and discard the rest. So for a-team just look for team.

  5. #5
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, good idea. Strangely, "sala ultra lok" will return just those 8 products, where a "sala" as a term returns the 192 products containing sala.

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TacMaf View Post
    Thanks, good idea. Strangely, "sala ultra lok" will return just those 8 products, where a "sala" as a term returns the 192 products containing sala.
    Because your search string doesnt look for those terms independantly.

    % is the wildcard character. Putting in "sala ultra lok" returns the query string: p.productTitle LIKE '%sala ultra lok%' OR ....
    Which will only return products using that phrase. If you want to tokenize the result, you're gonna have to reconstruct the code a bit; change the preg_replace to a preg_split, and construct a multiple-or query using each part.

    Give example, SL. Okay, here goes.

    DB Entries:
    1) sala ultra lok toolkit
    2) sala ultra-lok toolkit
    3) ultra-lok sala toolkit
    4) ultra mega wrench

    Search Term: sala ultra lok

    Your original query would match 1.
    The preg_replace would match 1 and 2.
    Preg-splitting and compound-or would match all 4.
    Preg-splitting and compound-AND would match 1,2, and 3.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK thanks. So it looks like my best option would be Preg-splitting and compound-AND. Much appreicated.

  8. #8
    SitePoint Guru TacMaf's Avatar
    Join Date
    Dec 2005
    Location
    Manchester, United Kingdom
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's just a part of the module. It just calls the mysql_real_escape_string on the string. The question is more about the search string and breaking it up to make the search more broad.

    Cheers,
    Rhys

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Edited my post above. Gotta stop responding too quickly ;P

    and yes, filtering the size of the terms is a good idea too.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    As an alternative, because you are searching 3 fields in a single table, you could look at creating a mysql FULLTEXT index on those fields, this may well do a better job of finding these "close" matches.

    I'd copy that table, set up the index and see if it returns matches that are in line with your clients expectations.


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
  •