Di solito, “cercare in un database” significa cercare quelle righe che contengono un “documento” in un campo, che corrisponde a delle parole chiave (di solito usando la funzione MATCH AGAINST); mettiamo invece che vogliate fare il contrario, cioè avete una tabella con delle righe che contengono un campo in cui avete memorizzato delle parole chiave, e volete selezionare solo quelle righe in cui le parole chiave corrispondono ad un testo o un particolare documento. Questa è quella che io chiamo una ricerca invertita.

Uno scenario reale: avete un sito di offerte di lavoro, ed i candidati cercano annunci di loro gradimento, ma non ne trovano nessuno, oppure ce ne sono troppo pochi e non interessanti. O continuano a collegarsi al sito ripetendo la ricerca periodicamente (ad esempio con le parole chiave “veterinar* gatt*“, perché vogliono prendersi cura degli animali, e hanno un debole per i gatti in praticolare), o si iscrivono al feed RSS di quella ricerca (se ne avete uno)… oppure “salvano” la ricerca e aspettano che sia il sito ad inviare loro una mail non appena compare un nuovo annuncio pertinente, ed è qui che torna utile questo articolo.

Salvare le parole chiave per ogni utente in una tabella MySQL è semplice, ma quanto è semplice verificare se un annuncio appena inserito corrisponde alle parole chiave? In PHP, costruireste un array delle parole chiave, e per ogni elemento richiamereste strpos() per verificare che siano tutte contenute nel testo.

In MySQL la funzione INSTR() è l’equivalente di strpos() in PHP, ma non potete salvare degli array in un campo (intendo array veri e propri, non serializzati), e d’altra parte salvare tante righe quante sono le parole chiave scelte da ogni utente è sconveniente, siccome la normalizzazione non è necessaria: un “insieme di parole chiave” è quasi sempre unico per ogni utente che si iscrive ad una ricerca, per cui ha senso salvarlo in un campo testuale nella forma di “parola1,parola2,parola3,…“; anche qui, in PHP useremmo explode(“,”,$parole) per ottenere un array, ma in MySQL non esiste nessuna funzione simile, per cui è necessario crearne una.

Idealmente, avreste bisogno di una funzione a cui passare il testo/documento in vostro possesso (la somma di titolo e corpo dell’offerta di lavoro, in questo caso), la stringa nella forma “parola1,…,parolaN“, e un parametro per indicare che il delimitatore (“colla” nel caso di implode/explode) è una virgola, quindi:

funzione(document, keywords, delimiter)

Questa funzione dovrebbe restituire un valore intero positivo se tutte le parole chiave sono presenti nel testo, oppure 0 in caso contrario.

La funzione che ho messo assieme incollando informazioni prese un po’ dovunque è:

DELIMITER ;;

DROP FUNCTION IF EXISTS `matchkwdarray`;;
CREATE FUNCTION `matchkwdarray`(`str` text, `arr` text, `delimit` tinytext) RETURNS char(1) CHARSET utf8
 DETERMINISTIC
BEGIN
 DECLARE i INT DEFAULT 1;
 DECLARE matches INT DEFAULT 1;
 DECLARE token TINYTEXT;
 label:WHILE(matches>0) DO
 SET token=replace(substring(substring_index(arr, delimit, i), length(substring_index(arr, delimit, i - 1)) + 1), delimit, '');
 IF token='' THEN LEAVE label; END IF;

 SET matches=INSTR(str, token);
 SET i=i+1;
 END WHILE;
 RETURN matches;
END;;

DELIMITER ;

Per aggiungere questa funzione al vostro database MySQL, potete collegarvi sia tramite CLI, sia usando una interfaccia web al database, come dovranno fare tutti gli utenti su un hosting condiviso, non avendo accesso alla linea di comando; phpMyAdmin, rigonfio com’è, non supporta comunque routine e funzioni, per cui avete bisogno di un altro DB manager, ed io ho scelto Adminer che svolge il suo lavoro egregiamente, più rapido di phpMyAdmin, e tutto in un piccolissimo singolo file PHP di circa 150kb.

Un esempio d’uso della funzione:

SELECT matchkwdarray('questo è solo un piccolo test molto semplice', 'solo,test', ',')
Restituisce: 2

SELECT matchkwdarray('questo è solo un piccolo test molto semplice', 'casa,albero', ',')
Restituisce: 0

SELECT matchkwdarray('questo è solo un piccolo test molto semplice', 'solo,test,semplice,albero', ',')
Restituisce: 0

Questa funzione è efficiente: appena verifica che una parola chiave non è contenuta nel testo interrompe la procedura e restituisce subito 0; ovviamente, se non cercate una precisione del 100% ma vi accontentate di una corrispondenza inferiore, potete modificarla per verificare comunque tutte le parole chiave, e restituire una percentuale di corrispondenza; potete anche rimuovere l’ultimo carattere dalle singole parole in modo tale che siano ammesse più varianti delle stesse.