Search Optimization

PHP
1

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:

$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

$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 = %.

3

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.

4

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

5

Edited my post above. Gotta stop responding too quickly :stuck_out_tongue_winking_eye:

and yes, filtering the size of the terms is a good idea too.

6

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.

7

That looks more efficient. I’ll give it a whirl. Cheers.

8

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.

9

OK thanks. So it looks like my best option would be Preg-splitting and compound-AND. Much appreicated.

10

As an alternative, because you are searching 3 fields in a single table, you could look at creating a [google]mysql FULLTEXT index[/google] 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.