Recherche similaire en MySQL/PHP

Version imprimable

09/10/2015, 11h29
Nowwis

Recherche similaire en MySQL/PHP

Bonjour,

Je me permets de venir vous voir concernant une base de données en MySQL ou je dois gérer les "doublons".
J'ai un formulaire PHP où on peut mettre "nom, prénom, email, téléphone".
Dans ma base je dois vérifier si quelqu'un existe.
Pour l'email et le téléphone aucun soucis.

Pour le nom et le prénom je souhaiterais faire une recherche en supprimant les caractères spéciaux, les espaces, les tirets de ma base pour comparer à ma chaine préformaté en PHP.

Est-ce possible ? Y'a t'il une fonction pour ça ?

Après avoir épluché la doc je ne trouve pas de chose ressemblante...

Merci d'avance !
09/10/2015, 11h50
escartefigue

bonjour,

Quelle est la vocation de votre base de données, combien y aura-t- il d'individus ?

D'une façon générale, la recherche de doublons sur individus ne peut pas se faire avec seulement le nom et le prénom
- vous pouvez avoir n individus ayant les mêmes nom et prénom(s) mais qui sont des homonymes
- vous pouvez avoir n individus ayant des nom ou prénoms différents mais qui sont pourtant des doublons (erreur de saisie par exemple, ordre des prénoms inversé etc...)

Si vous voulez vraiment faire de la détection de doublons sur une population conséquente, alors c'est toute une démarche à mettre en place et pas simplement quelques requêtes SQL
Démarche que je peux vous indiquer si vous le souhaitez, pour l'avoir mise en place à plusieurs reprises sur des populations de plusieurs millions d'individus
09/10/2015, 11h58
Nowwis

Bonjour,

Merci pour la réponse.

Alors il y a plus de champ que ça en fait.
Mais j'ai voulu synthétiser.

Le but n'est pas sélectionné directement le membre avec le même nom et prénom, mais de proposer à l'utilisateur (administrateur) les ressemblance pour soit créer la fiche d'un client soit utiliser un client existant.

Il me faut juste proposer des champs avec un système de concaténation de champ par exemple.

Simon.
09/10/2015, 12h20
escartefigue

Il n'en reste pas moins que la solution à mettre en place n'est pas la même si vous avez à traiter 5000 personnes ou 50 millions de personnes.

Plus vous avez d'individus, plus vous avez de chances (de risques en l'occurrence) de rater des doublons fonctionnels si vous ne procédez que par concaténation et ce d'autant plus si votre table des individus est issue de la consolidation de plusieurs sources de données (exemple typique : fusion de 2 entreprises ayant dans leurs S.I. respectifs des individus communs)

La démarche la plus fiable est la suivante :
- définir les colonnes que vous souhaitez utiliser pour votre détection de doublons (par exemple n° de sécu, nom, prénom, date de naissance)
- supprimer les caractères spéciaux (votre demande initiale) accents, cédilles etc... et convertir en majuscule chaque colonne sélectionnée
- définir les combinaison de critères que vous souhaitez utiliser pour détecter les doublons : par exemple NNI+nom+prénom , nom+prénom+date de naissance
sachant que pour certaines colonnes, il est préférable (je parle par expérience) de ne prendre qu'une partie de l'information.
exemple typique, dans beaucoup de bases de données, la date de naissance ne fiabilise pas le jour, (qui est souvent mis par défaut à 01 car calculé depuis le n° de sécu)
autre exemple : nom et prénom, une comparaison sur les premiers caractères seulement est plus fiable que sur la totalité, car des erreurs de terminaison sont fréquente (DUPOND/DUPONT)
- définir la priorité des combinaisons les unes par rapport aux autres (quelles sont les combinaison les plus fiables)
- exécuter une itération de comparaison et vérifier un échantillon de résultat pour chaque règle, afin d'en vérifier la qualité des règles, et redéfinir les priorités
chaque exécution doit produire un tableau de bord, qui identifie, par règle utilisée, les individus rapprochés selon cette règle
- relancer le processus autant de fois que nécessaire pour le fiabiliser
09/10/2015, 12h23
Nowwis

Je suis donc obligé de passer par une colonne dans la base de donnée pour comparer je pense.

Je traite environ 10.000 entrée, c'est assez peu, je pense que ça sera rapide à gérer... Merci tout de même pour les infos pertinentes.

Vous me donnez le processus que j'envisageais, ma demande initial et de savoir si une fonction MySQL permet d'éviter la création d'une colonne dans MySQL (colonne "nomprenom" qui contiendra à l'enregistrement le nom et le prenom formaté pour effectuer des recherches dans le futur...)
09/10/2015, 13h48
escartefigue

Techniquement il y a d'autres solutions, comme la création de vues par exemple.
Mais l'ajout d'une colonne dans la table table comme la création de vue, ne sont pas la bonne voie car c'est une solution figée qui obligerait à modifier le DDL à chaque essai.

En effet comme je l'expliquais, ce genre de processus est itératif, il faut le jouer plusieurs fois pour fiabiliser les règles

Exemple : je fais un essai avec les règles suivantes
Règle 1 avec priorité la + forte :
- n° de sécu complet+nom complet + prénom complet
Règle 2 avec priorité moindre :
- n° de sécu complet+6 1ères lettres du nom + 4 premières lettre du prénom
Mes 2 règles (dans la vraie vie il faut en jouer plus que ça) s'appuient sur les mêmes colonnes, si j'utilise une vue, il m'en faut donc 2.
De plus il est probable, qu'à chaque essai que je doive modifier les règles de concaténation pour essayer d'autres valeurs de substring plus efficientes
J'ai connu une entreprise dans laquelle une très forte proportion de prénoms ne comportait qu'une initiale, du coup, il était plus efficace de ne comparer que la première lettre du prénom, bien sur combiné avec d'autres colonnes. (ex : messieurs "n°SECU1 DUPONT MARCEL" et "n°SECU1 DUPONT M" sont bien les mêmes personnes)

Ce genre de situation ne s'analyse qu'empiriquement, d'où le besoin de rejouer et d'affiner les règles.
Donc si on veut ne pas modifier le DDL des vues (ou des tables) a chaque run, le plus simple est de créer des tables dédiées à ce processus, et qui sont alimentées en fonction des règles appliquées.
La zone à comparer peut être un char ou varchar suffisamment long pour contenir la concaténation la plus riche (somme de toutes les longueurs max de toutes les colonnes candidates)

Dans l'idéal la solution est une base de donnée ou un sous-ensemble de tables dédiées, pour contenir les identifiants d'origine (non altérés), les règles à appliquer, leur priorité, les différentes combinaisons de valeurs résultantes de ces règles et la règle associée, autant d'itération résultats que d'itération de traitement et enfin, les tableaux de bord pour aide à la décision.
Soit une dizaine de tables

Dans mon exemple ci-dessus, j'ai toujours constaté que la règle 2 est plus fiable que la 1 (pour les raisons données plus haut).
10/10/2015, 13h29
Artemus24

Salut à tous.

Juste une question : quel est l'intérêt de faire une recherche sur le nom et le prénom si tu possèdes déjà le code insee (ou celui de la sécurité sociale) ?

@+
10/10/2015, 19h34
escartefigue

Citation:

Envoyé par Artemus24

Salut à tous.

Juste une question : quel est l'intérêt de faire une recherche sur le nom et le prénom si tu possèdes déjà le code insee (ou celui de la sécurité sociale) ?

@+

Excellente question en voici les raisons :
- dans certains SI le NNI est saisi sans contrôle
- les enfants mineurs, ont provisoirement le NNI des parents
- dans quelques cas, le NNI est reconstitué à partir des autres éléments de signalétique et les 3 derniers chiffres (n° d'ordre) sont mis à zéro pour les reconnaitre
- certains SI ne savent pas gérer les NNI exotiques (n° monaco, lunaire etc...)

Du coup on trouve des NNI faux, et des doublons de NNI

Il existe depuis plusieurs années, un processus de certification des NNI, via une base centrale et un application - la PRC du GIE AGIRC ARRCO- qui permet de vérifier qu'un NNI est correct et qu'il correspond à la bonne personne (nom, prénom, date de naissance etc...)

Tout NNI non certifié reste et doit rester douteux
11/10/2015, 11h37
Artemus24

Salut Escartefigue.

--> NNI : numéro national d'identification (le numéro de sécurité sociale).

Citation:

Envoyé par Escartefigue

- dans certains SI le NNI est saisi sans contrôle

D'accord. Un manque de contrôles durant le saisie, c'est un autre problème.

Citation:

Envoyé par Escartefigue

- les enfants mineurs, ont provisoirement le NNI des parents

Je ne le savais pas, enfin je ne me suis jamais posé de questions à ce sujet.
J'ai toujours cru que le NNI était attribué à une personne, dès sa naissance.

Est-ce qu'un NNI devient obligatoire, soit quand tu deviens majeur ou soit quand tu commences à travailler.

Citation:

Envoyé par Escartefigue

- dans quelques cas, le NNI est reconstitué à partir des autres éléments de signalétique et les 3 derniers chiffres (n° d'ordre) sont mis à zéro pour les reconnaitre

Pourquoi reconstituer le NNI ? Soit tu en as un, soit c'est celui des parents. A priori, on le connait.

Citation:

Envoyé par Escartefigue

- certains SI ne savent pas gérer les NNI exotiques (n° monaco, lunaire etc...)

Un NNI, c'est bien pour ma France. Mais quand est-il des autres pays ?
Est-ce que pour chaque personne dans le monde, il existe un NNI ?

Est-ce que l'on crée un NNI pour les étrangers qui travaillent en France ?

Citation:

Envoyé par Escartefigue

Du coup on trouve des NNI faux, et des doublons de NNI

Un doublon de NNI, c'est dans le cas des enfants mineurs, non ? C'est le même NNI pour des frères et des soeurs.
Et c'est le même NNI entre les enfants et les parents des parents.

Citation:

Envoyé par Escartefigue

Il existe depuis plusieurs années, un processus de certification des NNI, via une base centrale et un application - la PRC du GIE AGIRC ARRCO- qui permet de vérifier qu'un NNI est correct et qu'il correspond à la bonne personne (nom, prénom, date de naissance etc...)

J'ai toujours cru qu'il n'y avait pas d'ambiguïté au sujet des NNI.
Et pourquoi c'est les caisses Agir et Arrco qui maintenant s'en charge ? Et que fait la sécurité sociale ?

Citation:

Envoyé par Escartefigue

Tout NNI non certifié reste et doit rester douteux

Les NNI, ça ma l'air d'être encore une usine à gaz.

@+
11/10/2015, 13h17
escartefigue

Citation:

Envoyé par Artemus24

--> NNI : numéro national d'identification (le numéro de sécurité sociale).
D'accord. Un manque de contrôles durant le saisie, c'est un autre problème.

On dérive un peu la, mais bon
Alors Non le NNI (on parle désormais de NIR) n'est pas seulement le n° de sécurité sociale, il existe d'autres nomenclatures valides en France, par exemple les nomenclatures "Monaco", "cafat" (nouvelle calédonie), "Lunaire" (Algérie Française) etc..

Citation:

Envoyé par Artemus24

J'ai toujours cru que le NNI était attribué à une personne, dès sa naissance.

En effet, mais ce dont je parle, c'est des informations que l'on trouve dans les S.I. des entreprises françaises.
Pour avoir travaillé sur de nombreux chantiers de convergence de système d'information de caisses de retraites (qui gèrent donc les retraites complémentaires des entreprises) et d'organismes de prévoyance, j'ai constaté que très souvent, les apprentis en début de carrière sont enregistrés sur le NNI du père ou de la mère, ils ont ensuite leur propre n°. En fait les gens ne connaissent leur propre NNI que quand ils ne sont plus rattachés à la sécu de leurs parents.

Citation:

Envoyé par Artemus24

Pourquoi reconstituer le NNI ? Soit tu en as un, soit c'est celui des parents. A priori, on le connait.

La encore, je parle de ce que j'ai rencontré dans les systèmes d'information, certaines boites procèdent ainsi, ça n'est pas gênant pour le sujet qui nous préoccupe si on l'a détecté : il suffit de ne rapprocher que sur les 10 premiers caractères du NNI avec d'autres critères pour le fiabiliser. C'est l'analyse de la qualité des critères de rapprochement qui permet de savoir sur quelles combinaisons on peut faire ce rapprochement, c'est l'une des raisons pour lesquelles j'expliquai plus haut que c'est un traitement itératif, avec ajustements successifs.

Citation:

Envoyé par Artemus24

Un NNI, c'est bien pour ma France. Mais quand est-il des autres pays ?
Est-ce que pour chaque personne dans le monde, il existe un NNI ?

Il existe probablement l'équivalent dans chaque pays, mais je n'ai travaillé sur ce sujet que pour des entreprises françaises (DOM TOM inclus)

Citation:

Envoyé par Artemus24

Et pourquoi c'est les caisses Agir et Arrco qui maintenant s'en charge ? Et que fait la sécurité sociale ?

Ca a toujours été les 2, la sécu ne gère que la retraite de base (autant dire une misère :mrgreen:), la retraite complémentaire est gérée depuis des lustres par l'ARRCO (pour la partie non cadre) et par l'AGIRC(pour la partie cadres) sachant que tout cadre cotise aussi pour l'ARRCO (prélèvement sur la tranche de salaire correspondante, la tranche A)

Citation:

Envoyé par Artemus24

Les NNI, ça ma l'air d'être encore une usine à gaz.

Non, ce sont les S.I. qui ne sont pas fiables, ce n'est pas la même chose, le NNI, dans la mesure où il a fait l'objet d'une certification via le processus PRC (mais la ça ne concerne que le domaine retraite), est fiable.

J'ajoute, que comme il existe plusieurs nomenclatures valides de NIR, dont la syntaxe est relativement proche, il est techniquement possible d'avoir des doublons entre 2 NNI de nomenclatures différente.
Il faut donc ne comparer que les NIR issus d'une même nomenclature pour vérifier que 2 individus sont bien les mêmes (doublon fonctionnel) ou non
11/10/2015, 15h23
Artemus24

Salut Escartefigue.

Merci pour toutes ces précisions !

@+