Mega Code Archive

 

Categories / Php / MySQL Database

 

Normalize fields and strings used in where (commands 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.