Calcul en % la similarité entre 2 champs MySQL

                               

                            
/*------------------------------*/
/*
 Titre : Calcul en % la similarité entre 2 champs MySQL                                                                
                                                                                                                          
 Date édition     : 31 Jan 2008                                                                                          
 Date mise a jour : 23 Sept 2019
                                                                                         
 Rapport de la maj:                                                                                                       
  - refactoring du code en PHP 7                                                                                          
  - fonctionnement du code vérifié                                                                                  
  - correction du code                                                                                                    
  - modification de la description                                                                                        
*/
/*------------------------------*/

    $db_server = 'localhost'; // Adresse du serveur MySQL
    $db_name = '';            // Nom de la base de données
    $db_user_login = 'root';  // Nom de l'utilisateur
    $db_user_pass = '';       // Mot de passe de l'utilisateur

    // Ouvre une connexion au serveur MySQL
    $conn = mysqli_connect($db_server,$db_user_login, $db_user_pass, $db_name);

/*******************************************************************************
    * Initialisation
    ***************************************************************************/

$valeur_pourcentage = 70;  // la valeur du pourcentage de mots communs
                           // le doublon parfait c'est 100% de mots communs!!
                           // Donc $valeur_pourcentage sera initialiser à  100
                           // dans ce cas.
                           // Si vous tolerez 90% de mots communs, mettez 90 ;)


$table_sql = ""; // nom de la table sql

$champ_id = ""; // nom de votre identifiant

$champ_recherche_sql = ""; // nom du champ sur lequel on effectue la
                                      // recherche de similarité

$nbre_elements = 100; // commencer petit, le script est long a executer
                     // le nombre d'element se trouve dna la premiere boucle de
// l'algo
                     // si vous souhaitez boucler sur la table entiere,
// remplacer
                     // $nbre_elements par sizeof($tab_matchv)



/*******************************************************************************
    * enlève les accents
    ***************************************************************************/


function TexteSansAccent($texte){ 

$accent=
'à€àà‚à à„à…à à¡à¢à à¤à¥à’à“à”à•à?' .
'?à˜à²à³à´àµà¶à¸àˆà‰àŠà‹éèêà«à‡à§àŒ?' .
'?àŽàà¬à­à®à¯à™àšà›àœà¹àºà»à¼à¿à‘à±'; 
$noaccent='AAAAAAaaaaaaOOOOOOooooooEEEEeeeeCcIIIIiiiiUUUUuuuuyNn'; 
$texte = strtr($texte,$accent,$noaccent); 
return $texte; 

}

/*******************************************************************************
    * magic_quote
    ***************************************************************************/

function AuStrip_Slashes($chaine) {
    return(get_magic_quotes_gpc() == 1 ? StripSlashes($chaine) : $chaine);}

/*******************************************************************************
    * Stop Words !!
    ***************************************************************************/

$stop_words = array("alors","au","aucuns","aussi","autre","avant","avec","avoir"

,
                    "bon","car","ce","cela","ces","ceux", "chaque","ci","comme",
                    "comment","dans","des","du","dedans","dehors","depuis",
"deux",
                    "devrait","doit", "donc","dos","droite","début","elle",
"elles",
                    "en","encore","essai","est","et","eu","fait","faites", 
"fois",
                    "font","force","haut","hors","ici","il","ils","je juste",
"la",
                    "le","les","leur","là ","ma",
                    "maintenant","mais","mes","mine","moins","mon","mot",
"même",
"ni",
                    "nommés","notre","nous","nouveaux",
                    "ou","oà¹","par","parce","parole","pas","personnes","peut"
,
"peu",
                    "pièce","plupart","pour","pourquoi",
                    "quand","que","quel","quelle","quelles","quels","qui","sa",
                    "sans","ses","seulement","si","sien","son",
                    "sont","sous","soyez sujet","sur","ta","tandis","tellement",
                    "tels","tes","ton","tous","tout","trop",
                    "très","tu","valeur","voie","voient","vont","votre",
"vous",
                    "vu","à§a","étaient","état","étions","été",
"être",
                    "un","deux","trois","quatre","cinq","six","sept","huit",
"neuf","dix",
                    "0","1","2","3","4","5","6","7","8","9","10",
                    "avec","chez","par","dans","des","en","de","une","votre",
                    "meilleurs","entre","entres",
                    "depuis","alors","ne","pas","du","meme",
                    "ou","nom","seuls","acceptes","ayant",
                    "mais","ou","et","donc","or","ni","car",
                    "vos","votre","mes","mien","mien","tien","tiens","tout",
                    "toute","toutes",
                    "que","quoi","qui","comment","peu","peut","pis","puis","pas"

,
                    "chaque","chacun","chacune",
                    "son","ses","au","aux","se","sur","ce","ceux","cette","ca",
                    "ci","ceci","cela","aussi","pour",
                    "petit","grand","moyen","large","haut","bas","milieu",
"droite",
                    "gauche","centre",
                    "dit","etre","leur","leurs",
                    "plus","moin","moins",
                    "es","est","sont","son","va","suis","ai","viens",
                    "a","b","c","d","e","f","g","h","i","l","m","n","o","p","q",
                    "r","s","t","u","v","w","x","y","z"
                    );

/*******************************************************************************
    * Set le temps d'excecution
    ***************************************************************************/

set_time_limit(0);



/*******************************************************************************
    * Premiere requete -place tout les champs dans un tableau
    ***************************************************************************/

$qv = $conn->query("SELECT $champ_id,$champ_recherche_sql FROM $table_sql");
     $p=0;
     $regs_split = array();
     $tab_matchv  = array();
     while ($rv = mysqli_fetch_array($qv)) {
            $tab_matchv[$p] = $rv[$champ_id];
            $p++;
            // Place tous les mots d'une chaine dans un tableau

            $ch = $rv[$champ_recherche_sql];
            // enleve les slashs
           $ch = AuStrip_Slashes($ch);
           // enleve les accent
           $ch = TexteSansAccent($ch);// enleve les accent
           // enleve les apostrophes
           $ch = str_replace("'"," ",$ch);
           // passe le texte en minuscule
           $ch = strtolower($ch); // passe minuscule
           $regs_split = preg_split("#[^[:alpha:]]+#", $ch);
           // enleve les mots bannis en faisant le difference des 2 tableaux
           $regs_split2 = array_diff($regs_split,$stop_words);
           //print_r($regs_split2);
           $rv = implode(',',$regs_split2);
           //echo $rv; echo '<br />';
           $tab_matchv[$p] = $rv;
           $p++;
             }
/*******************************************************************************
    * seconde requete  - place tout les champs dans un tableau
    ***************************************************************************/

$q = $conn->query("SELECT $champ_id,$champ_recherche_sql FROM $table_sql");
     $i=0;
     $regs1_split = array();
     $tab_match  = array();
     while ($r = mysqli_fetch_array($q)) {
            $tab_match[$i] = $r[$champ_id];
            $i++;
            // Place tous les mots d'une chaine dans un tableau
            $ch = $r[$champ_recherche_sql];
            // enleve les slashs
           $ch = AuStrip_Slashes($ch);
           // enleve les accent
           $ch = TexteSansAccent($ch);// enleve les accent
           // enleve les apostrophes
           $ch = str_replace("'"," ",$ch);
           // passe le texte en minuscule
           $ch = strtolower($ch); // passe minuscule


            $regs1_split = preg_split("#[^[:alpha:]]+#", $ch);
            // enleve les mots bannis en faisant le difference des 2 tableaux
            $regs1_split2 = array_diff($regs1_split,$stop_words);
            $rv = implode(',',$regs1_split2);

            $tab_match[$i] = $rv;
            $i++;
            }

/*******************************************************************************
    * Sortie ecrans
    ***************************************************************************/

echo "<strong>Nombre de champs : ".round(sizeof($tab_match)/2)."<br /><br" .
" /></strong>";
echo "<strong>Verifie les $nbre_elements premiers éléments<br /><br />";
echo "<strong>Tolérance maximum de mots communs en pourcentage :</strong>
     ".$valeur_pourcentage."%</strong><br /><br />";

/*******************************************************************************
    * l'algo
    ***************************************************************************/

$p = 0;
$k = 0;
$delete_id_tb1 = array();
$delete_id_tb2 = array();

while ($p < $nbre_elements)
    {
   $p++;
   $j = 0;
   while ($j < sizeof($tab_match))
    {
    $j++;
    $regs = array();
    $reception = array();
    // on prend un champ du tableau
    $ch_tab_match = $tab_match[$j];
    // on decoupe la chaine et on la passe dans un tableau
    $regs = preg_split('#[,]#', $ch_tab_match);
    // idem avec la chaine a traiter
    $reception = preg_split('#[,]#', $tab_matchv[$p]);
    // on fait la difference
    $non_matches = array_diff($regs, $reception);
    // taille de la table
    $non_matches_size  = sizeof($non_matches);
    // taille de la table
    $regs_size = sizeof($regs);
    // calcul le %
    $inaccuracy = 0;
    $inaccuracy = round(($non_matches_size/$regs_size)*100);
    $accuracy = 100-$inaccuracy;
    
    // echo le resultat en %
    if ($accuracy > $valeur_pourcentage AND $accuracy != 100) {
    // recupere l'ID
    $val = $j-1;
    $save_id = $tab_match[$j-1];
    $val2 = $p-1;
    $tab_matchv_id = $tab_matchv[$p-1];
    $delete_id_tb1[$k] = $tab_matchv_id;
    $delete_id_tb2[$k] = $save_id;
    echo 'Il y a <strong><font color="#ff0000">'.$accuracy.'</font></strong>%
          de contenu similaire entre: <br /><strong>ID: '.$tab_matchv_id.
'</strong>
           >>'.$tab_matchv[$p].'<br /><strong>ID : '.$save_id.'</strong> >> ';
    echo ''.$tab_match[$j].'<br /><br /><br />
          ';
    $k++;
    }
    $j++;
   }
   $p++;
  }

/*******************************************************************************
    * Pour supprimer - A vous de voir sur quel table la suppression se fait
    * Vous avez les 2 listes d'ID, le reste devrait etre un jeu d'enfant pour
 vous ;)
    ***************************************************************************/

echo '<br /><br />Liste des ID de la premiere table<br />';
echo implode(',',array_unique($delete_id_tb1));

echo '<br /><br />Liste des ID de la seconde table<br />';
echo implode(',',array_unique($delete_id_tb2));



/*******************************************************************************
    * Fin
    ***************************************************************************/
?>