Operatore IN e stringhe CSV

[MarcoGrazia]

Ciao, dovrei estrarre da due tabelle dei valori. Fin qui nulla di strano, il problema è la relazione tra le parti.

Problema:
due tabelle, una con delle voci di testo da passare al suggeritore ed una con l'indicazione del registro a cui la voce appartiene.

Esempio:
(tabella registri)
registro 1: uccelli;
registro 2: pesci;
registro 3: alberi.

(tabella voci)
voce 1: nuotano; appartenenza: 1, 2;
voce 2: emettono versi; appartenenza 1;
voce 3: volano; appartenenza 1;
voce 4: respirano con le branchie; appartenenza 2;
... e così via ...

Ora è chiaro che ci sono voci che appartengono ai due registri, infatti ci sono uccelli che nuotano come i pesci, potevo creare più voci identiche ma con il registro diverso e non mi sembra elegante, un CSV con i valori dei registri di appartenenza della voce mi pareva al momento la cosa giusta.
Ma come faccio a estrapolare le voci partendo dalla tabella registri?

Ho provato con l'operatore IN ma non funziona, allo stesso tempo ho provato con il modo indicato da @marino51 in questo vecchio post https://forum.mrw.it/threads/mysql-operatore-in-su-campo-varchar.46094/ ma non ha funzionato.

Ora mi sto grattando la testa, ma non esce nulla

Ma a parte gli scherzi non ho idee.

 

[MarcoGrazia]

Sto trasformando il CSV in JSON, e credo di aver risolto, ma metterò la conferma, con tanto di codice domani.
A quanto pare è lo stesso sistema che utilizzano le mappe di google per la ricerca e la presentazione dei dati.

 

[MarcoGrazia]

Ecco la soluzione ma attenzione che è un poco elaborata.
Quindi, date due tabelle come sopra, più o meno, ricerco i valori di riferimento.
tabelle:
(animali)

CREATE TABLE animali
(
    id_animali INT NOT NULL AUTO_INCREMENT,
    tipo VARCHAR(100) NOT NULL,
    PRIMARY KEY (id_animali)
);

che contiene i seguenti valori:

1. uccelli
2. pesci
3. invertebrati
4. rettili
5. eccetera...

(suggeritore)

CREATE TABLE suggeritore
(
    id_voci INT NOT NULL AUTO_INCREMENT,
    voce VARCHAR(100) NOT NULL,
    registro JSON,
    PRIMARY KEY (id_voci)
);

con i seguenti valori:

1. volano, {"regs": {"reg1": 1}
2. nuotano, {"regs": {"reg1": 1, "reg2": 2, "reg3": 3, "reg4": 4}
3. strisciano, {"regs": {"reg3": 3, "reg4": 4}
4. eccetera...

Mettiamo ora che voglio un suggerimento sugli animali che nuotano, mi servirà sicuramente l'ID della tabella animali per i pesci, ma anche per gli uccelli, infatti alcuni nuotano, ma anche per invertebrati e rettili. Insomma ci vuole l'interra tabella!
Se avessi voluto solo quelli che volano mi sarei rivolto all'ID 1.

Quindi la mia query di ricerca diventa:
SELECT id_voci, voce, JSON_SEARCH(registro, 'all', '2') IS NOT NULL AS REGS FROM suggeritore; --
Questa mi tira fuori tutti i valori, dato che ID 2 contempla tutti gli animali presenti nella tabella animali.
Ma se avessi scelto che so l'ID 3 mi avrebbe tirato fuori solo gli animali nuotano e strisciano, cioè gli invertebrati.

C'è solo un problema, piccolo piccolo, di secondaria importanza! In realtà la quesry mi tira fuori tutto il "cucuzzaro" perché JSON_SEARCH() mi fa uscire sia quelli cercati che quelli non cercati, mettendo come REGS: 0 quelli che non corrispondono e REGS: 1 quelli importanti.
Alcuni suggeriscono di usare IS NOT NULL e così ho fatto, ma se lo togliete avrete i valori a zero come NULL e gli altri con i valori cercati, ma alla fine sempre tutto il cucuzzaro, perciò se la tabella suggeritore contiene migliaglia di records... bè la risposta è ovvia. Un casino di voci da cui spulciare i dati utili.
questo perché il secondo parametro di JSON_SARCH() è un comando, ed io ho DOVUTO usare ALL, accetta solo due valori, cioè ONE e ALL.
ONE cioè uno, si ferma al primo valore utile cercato ma se come nel caso il valore cercato è su più records non può essere utili, il secondo ALL è ovvio, tutto! Funziona ma non per tabella con troppi records.

Ah! Funziona ovviamente con ricerca della query con PHP PDO, MYSQLI eccetera, io ho usata questa sinrtassi è funge:

    $id = 2;
    
    $sql = 'SELECT id_voce, voce, JSON_SEARCH(registri, \'all\', :u) IS NOT NULL AS REGS FROM suggeritore; --';
    $handle = $pdo->prepare($sql);
    $handle->bindValue(':u', $id, PDO::PARAM_INT);
    $handle->execute();
    echo '<p>ID: ' . $id . '</p><pre>';var_dump($handle->fetchAll());echo '</pre>';

Buon appetito

 

[MarcoGrazia]

FUNZIONA MA NON BASTA!

voglio dire, i dati vengono fuori ma poi devo vagliare l'array risultante con il PHP e quindi sto cercando una soluzione alternativa con lo SQL che mi estragga i dati cercati senza altre operazioni.

Avevo pensato ad una tabella pivot tra le due tabelle animali e suggeritore, ma non sono così bravo con l'SQL.

Pensavo ad una tabella che riunisse i dati delle due sopra.

(suggeritore)

CREATE TABLE suggeritore
(
id_voci INT NOT NULL AUTO_INCREMENT,
voce VARCHAR(100) NOT NULL,
registro JSON,
PRIMARY KEY (id_voci)
);

(animali)

CREATE TABLE animali
(
id_animali INT NOT NULL AUTO_INCREMENT,
tipo VARCHAR(100) NOT NULL,
PRIMARY KEY (id_animali)
);

(scambio)

CREATE TABLE scambio
(
id_scambio INT NOT NULL AUTO_INCREMENT,
animali_id INT,
suggeritore_id INT,
PRIMARY KEY (id_scambio)
);

ma non saprei andare oltre.