Recherche insensible aux accents sous MySQL

Pour ma part, j'utilise une fonction de réécriture de la chaîne, comme ceci :

function Rewrite($chaine){
	$rewrite = strtr($chaine,"ÀÁÂÃÄÅÆÈÉÊËÇÌÍÎÏÒÓÔÕÖÙÚÛÜÝéèêëçàáâãäåùúûüìíîïôöòóõýýÿŠšÐŽžÑñØøðÞþßæƒ","AAAAAAAEEEECIIIIOOOOOUUUUYeeeecaaaaaauuuuiiiioooooyyySsDZzNnOooBbSaf");
	$rewrite = str_replace("-","",$rewrite);
	$rewrite = str_replace("_","",$rewrite);
  $rewrite = str_replace(" ","-",$rewrite);
	$rewrite = str_replace("~","",$rewrite);
	$rewrite = str_replace("²","",$rewrite);
	$rewrite = str_replace("^","",$rewrite);
	$rewrite = str_replace("°","",$rewrite);
	$rewrite = str_replace("=","",$rewrite);
	$rewrite = str_replace("+","",$rewrite);	
  $rewrite = str_replace("\"","",$rewrite);
  $rewrite = str_replace("/","",$rewrite);
	$rewrite = str_replace("?","",$rewrite);
  $rewrite = str_replace("%","",$rewrite);
  $rewrite = str_replace(",","",$rewrite);
  $rewrite = str_replace(";","",$rewrite);
  $rewrite = str_replace("!","",$rewrite);
  $rewrite = str_replace(":","",$rewrite);
  $rewrite = str_replace("'","",$rewrite);
  $rewrite = str_replace("#","",$rewrite);
  $rewrite = str_replace(".","",$rewrite);
  $rewrite = str_replace(":","",$rewrite);
  $rewrite = str_replace("(","",$rewrite);
  $rewrite = str_replace(")","",$rewrite);
  $rewrite = str_replace("@","",$rewrite);
  $rewrite = str_replace("*","",$rewrite);
  $rewrite = str_replace("]","",$rewrite);
  $rewrite = str_replace("[","",$rewrite);
  $rewrite = str_replace("{","",$rewrite);
  $rewrite = str_replace("}","",$rewrite);
  $rewrite = str_replace("--","-",$rewrite);    
	return strtolower($rewrite);
}

A toi de la personnaliser selon tes besoins. Et tu peux aussi tout mettre dans la fonction strtr().
Les str_replace() ne sont utiles que si tu souhaites remplacer d'autres caractères spéciaux que les accents.

Ensuite tu n'as plus qu'à appliquer la fonction à ta chaîne dans ta requête :

$reponse = $bdd->query("SELECT ID, Name, test FROM Table WHERE Name REGEXP \'".Rewrite($search)."\'");

Modifié par xtense (22 Jan 2011 - 16:49)

Salut,

plus simplement il suffit de choisir un interclassement (ou collation) mysql insensible à la casse : cf. cet article. Par exemple latin1_swedish_ci ou utf8_unicode_ci

A lire en complément : http://fluxbb.fr/aide/doku.php?id=mysql_charset_collation
Modifié par Heyoan (23 Jan 2011 - 12:42)