Substituting Letters and Number

[imperium2335]

Hi,

 

I am trying to build a search engine that looks up part numbers.

 

The thing is some people typing in l (L) for 1s (ones), o(ohs) for 0(zeros) and 5 for s's.

 

How can I get it to bring back results when a part number matches?

e.g. PLE12S05

would appear for the following queries:

PLEl25o5

PLE125oS

PLE1250S

etc...

 

I can seem to get it working if the query is made up of all mistaken letters for numbers and vice versa but it doesn't work for a mix of letters/numbers.

 

$lettertonumber = str_replace('l', '1', $queryCleaned);
$lettertonumber = str_replace('o', '0', $lettertonumber);
$lettertonumber = str_replace('s', '5', $lettertonumber);
$numbertoletter = str_replace('1', 'l', $queryCleaned);
$numbertoletter = str_replace('0', 'o', $numbertoletter);
$numbertoletter = str_replace('5', 's', $numbertoletter);

 

Can anyone help?

[jamesxg1]

$letters = array('l', 'o', 's');
$numbers = array('1', '0', '5');
$final = str_replace($letters, $numbers, $DIRTY_QUERY);

 

Is this what you are looking for?

 

James.

[imperium2335]

Hi,

 

Thanks for your reply but no, it's just fetching back the wrong results.

 

What I would expect to be a solution is some kind of loop for each letter where 'OR' queries are built depending on the number of variations. So for 115o, it would be separate queries such as this:

115o,1150,1l50,ll50,ll5o,l15o etc if you know what I mean? but this way seems very clumsy.

[jamesxg1]

Take a look at substr_replace

 

James.

[imperium2335]

I'm families with it, but how can that help? It would still need a loop like above no?

[imperium2335]

familiar even! lol

[TeNDoLLA]

Why are you trying to fix a problem that does not exist? If the people can't type properly its not your fault or is it? Also if you can have both numbers and letters at any point of the strings then its impossible to try to build some mechanism to notice that when there is a mistyped char and when not.

[imperium2335]

They manage to do it:

 

hxxp://www.plccenter.com/Search/Basic?searchText=6es5100

 

hxxp://www.plccenter.com/Search/Basic?searchText=6es5l0o

 

...

[imperium2335]

Perhaps something to do with the levenshtein function?

[imperium2335]

Anyone have any ideas it's driving me mad.

[harristweed]

best I can do is:

<?php$seach_items=array();
  $search = "PLEl25o5";
  $seach_items[]=$search;
  $swap=array(1=>"l", 5=>"s", 0=>"o", l=>"1", o=>"0", s=>"5");
  foreach ($swap as $key => $value) {
  if(strpos($search,"$key")){
  $seach_items[]=str_replace("$key","$value","$search");
  }
  }
$query="('". implode("' or '",$seach_items)."')";
echo"$query";
?>

my excuse is i'm old!

[imperium2335]

Nice, that is very close to what I was after.

 

The only down side is that it doesn't work if you have two letters/numbers next to each other. e.g.

 

for TP0026, it won't echo out a result for TPoo26.

 

Thanks for your insight!

[harristweed]

 

for TP0026, it won't echo out a result for TPoo26.

To get over that, you need to examine each character in the string individually

[imperium2335]

I think it's going to end up super complicated! I'm just gonna be happy with your method which seems to work fine.

 

Thanks for your help!

[imperium2335]

How would I go about adapting the above script to analyse each letter?

[imperium2335]

Can anyone help me? It's driving me crazy

[jcbones]

Still not quite there, but closer:

<?php
  $search = "PLEl25o5";  
  $swap=array('1'=>"l", '5'=>"s",'0'=>"o","l"=>'1',"s"=>'5',"o"=>'0'); //all entries must be strings in order for in_array to work.
$len = strlen($search); //get length of search parameters.
$arr = array(); //new array.
$searched = array_pad($arr,$len,$search); //fill the array with the search according to the string length.	
for($i = 0, $index = 1; $i < $len; $i++) { //for loop.
	$letter = substr($search,$i,1);	//get current letter.
	if(in_array($letter,$swap,true)) { //if it is in the swap array.
		$key = array_search(substr($search,$i,1),$swap,true); //get the key.						
		$searched[$index][$i] = $key; //store the new search to the searched array.
		++$index;
	}
}
$query="('". implode("' or '",$searched)."')";
echo $query;
?>

OUTPUT:

('PLEl25o5' or 'PLE125o5' or 'PLEl2so5' or 'PLEl2505' or 'PLEl25os' or 'PLEl25o5' or 'PLEl25o5' or 'PLEl25o5')

 

[imperium2335]

Thanks JC, that's much closer now but it's still missing a few combinations.

 

Will doing these calculations on a database of about 100,000 parts kill the database server? (it's for a search engine).

 

If it can't be improved any further, I think I'm going to use what you came up with, so thank you very much for your help!

[Zane]

This is the way I would do it.

  $partnumber = "PLEl25o5";
$sql = "SELECT * FROM parts\n";
$sql .= "WHERE partnum = '$partnumber'\n";
if(strpos($partnumber, "l") != false)
	$sql .= "AND (partnum LIKE '%l%' OR partnum LIKE '%1%')\n"
if(strpos($partnumber, "s") != false)
	$sql .= "AND (partnum LIKE '%s%' OR partnum LIKE '%5%')\n"	
if(strpos($partnumber, "o") != false)
	$sql .= "AND (partnum LIKE '%o%' OR partnum LIKE '%0%')\n"
    
    echo $sql;
    // run query with $sql	
?>

It's not tested, but all I'm doing is concatenating the SQL query conditionally whenever those letters appear.

[imperium2335]

I tried it but it didn't work, just get no results:

<?php
  $partnumber = "6ES510";
$sql = "SELECT * FROM parts";
$sql .= " WHERE partnum = '$partnumber'";
if(strpos($partnumber, "l") != false)
	$sql .= " AND (partnum LIKE '%l%' OR partnum LIKE '%1%')" ;
if(strpos($partnumber, "s") != false)
	$sql .= " AND (partnum LIKE '%s%' OR partnum LIKE '%5%')"	 ;
if(strpos($partnumber, "o") != false)
	$sql .= " AND (partnum LIKE '%o%' OR partnum LIKE '%0%')" ;

include("system/dbconnectold.php") ;

$result = mysql_query($sql)or die(mysql_error()) ;

while($row = mysql_fetch_assoc($result))
	echo $row['partnum'] ;


?>

In my database I have a part called "6ES510", but if I type "6ES5l0" (small L) I get no results, same goes for all the other cases.

[DavidAM]

If you have control of the database and this is an important piece of the application, I would consider the following:

• Add a column to the table called partnumber_search, use the same datatype as partnumber, consider indexing this column (NOT UNIQUE)
  
• Decide on a strategy: numbers to letters or letters to numbers (I would probably use numbers to letters)
  
• Write an SQL function to translate a partnumber to the value it would have if all numbers were converted to their potential mistaken letters (or vice-versa if you choose the letters to numbers option)
  
• Update the table setting the new column from the partnumber using the function
  
• When searching for a partnumber, search the new field using this function against the user's input
  
• Possibly add to the start of the ORDER clause IF(partnumber = userinput,0,1) - so exact matches appear first in the list
  
• Revise your INSERTS/UPDATES (or use a trigger) to set the new column's value
  

 

You have to consider if the additional disk space required for this "solution" offsets the additional processing of trying to calculate and query EVERY conceivable permutation of the user's input or missing some potential rows

 

In building the function for translating the input, I would ignore the case of the entered letters. In fact, I would convert the input value to uppercase and then translate (so "1" would become "L" not "l"). This will account for the situation where the user thinks it is an "l" (lowercase "L") but enters "L" (uppercase "L") because that's the way they think.