Php > Databases

Normalize fields and strings used in where (command's Sql)

Normalize fields and strings used in where (command's Sql)

<? 

// This function normalize fields and strings with or without accents, to where (commands sql). 
// This functions converts fields and strings with special characters into fields and strings with 
characters normalized and, 
// convert this to uppercase 

function normalize($field,$string,$is_normalized,$type){ 

         $characters_with_accents = "?????????Ç?"; // characters with accents 
         $characters_norm = "aeiouaeiouacouAEIAEOUAEIOUACOU"; // characters normalized 

         if ($is_normalized) // if is true, the field is normalized 
             $field_normalized = $field; 
         else 
              // normalize the field 
             $field_normalized = "upper(translate 
(".$field.",'".$characters_with_accents."','".$characters_norm."'))"; 
          
         $string_normalized =  strtoupper(strtr(trim 
($string),$characters_with_accents,$characters_norm)); 
          
         if (strcmp($type,"%like%") == 0) // the type of comparasion is 'like', in both sides (left 
and right) 
             return $field_normalized." like '%".$string_normalized."%'"; 
         elseif (strcmp($type,"like%") == 0) // the type of comparasion is 'like', in left side 
             return $field_normalized." like '".$string_normalized."%'";          
         elseif (strcmp($type,"%like") == 0) // the type of comparasion is 'like', in right side 
             return $field_normalized." like '%".$string_normalized."'";                       
         elseif (strcmp($type,"=") == 0) // the type of comparasion is '=' 
             return $field_normalized." = '".$string_normalized."'";          
} 
?> 

Examples of use: 

<? 
$sql1 = "select * from people where ".normalize("name","S?? Concei?",false,"%like%"); 
$sql2 = "select * from keywords where ".normalize("key","web",true,"like%"); 
$sql3 = "select * from letters where ".normalize("first_letter","a",true,"="); 

?> 

This examples gives: 

$sql1 = "select * from people where upper(translate(name,'?????????Ç?
?,'aeiouaeiouacouAEIAEOUAEIOUACOU') like '%SONIA CONCEICAO%'"; 
$sql2 = "select * from keywords where key like 'WEB%'"; 
$sql3 = "select * from letters where where first_letter='A'"; 

This is very useful in command's Sql, and works fine with Oracle. 
Is so mutch useful in search's where the field and respective string is not normalized.