[MySQL/PHP] Vergelijken resultaten verschillende query's

[thegersom]

Eerst mijn excuses moest dit te basic zijn.

Ik heb het volgend probleem:
Ik heb een tabel 'opus' met ID, naam, jaar enzovoort. Daarnaast heb ik een tabel 'instrumentopus' waarin ik de opusID link met eventueel meerdere instrumentID's. Dus iets van de vorm:

ID	opusID	instrumentID
1	4	8
2	4	10
3	5	8
4	5	11
5	5	13
6	6	8

Nu wil ik een zoekfunctie maken zodat de bezoeker verschillende instrumenten kan aanvinken dmv checkboxen. Ik geraak er alleen niet uit hoe de code te implementeren zodat ik enkel de opusID's krijg met die instrumentID's (of meerder).

Concreet:
Als iemand maar één chechbox aanvinkt, met bv instrumentID=8 is het niet zo moeilijk opusID 4,5 en 6 terug te krijgen.

Maar als iemand twee checkboxes aanvinkt, met bv instrumentID 8 en 11 hoe krijg ik dan opusID 5 terug?

Moet ik eerst verschillende query's uitvoeren met telkens WHERE instrumentID=x en deze dan allemaal met elkaar vergelijken in PHP? Maar hoe kan ik dan in PHP meerdere arrays met elkaar vergelijken.

Of kan ik één bepaalde query uitvoeren?

[Sh0ckTr00per]

dit werkt wel..

select distinct pr_name, a.pl_gm_id, b.pl_gm_id from v_player
inner join v_played a on a.pl_pr_id = pr_id and a.pl_gm_id = 332
inner join v_played b on b.pl_pr_id = pr_id and b.pl_gm_id = 287

zo krijg je iedereen die beide heeft. Kun je ook zo toepassen op jouw scenario.

[ Voor 181% gewijzigd door Sh0ckTr00per op 04-09-2008 18:30 ]

[thegersom]

Snap het toch nog niet goed zo.

SELECT DISTINCT * FROM opus
INNER JOIN instrumentopus ON opus.ID = instrumentopus.opusID AND instrumentopus.instrumentID = 8
INNER JOIN instrumentopus ON opus.ID = instrumentopus.opusID AND instrumentopus.instrumentID = 11

Heb dit geprobeerd maar werkt niet echt, of pas ik de join's verkeerd toe?

[Verwijderd]

Als je je InstrumentID's goed onder controle hebt, en dus SQL injection kunt uitsluiten, dan kun je de geselecteerde instumentID's in een komma gescheiden string zetten.

Daarna is een query als "select distinct opusID from opus where instrumentID in (<bovengenoemde string>)" voldoende.

[dusty]

Zoals Afterlife hierboven mij al zegt: Gewoon een "Where ... in ..." gebruiken. Hoef je ook geen dynamische queries te maken die meer dingen gaan joinen zodra je op meer instrumenten wilt zoeken.

[.oisyn]

Verwijderd schreef op donderdag 04 september 2008 @ 20:18:
Daarna is een query als "select distinct opusID from opus where instrumentID in (<bovengenoemde string>)" voldoende.

Euh, nee, dat vindt alle opusID's die ten minste 1 instrumentID uit de lijst hebben. Ik zou er een COUNT() aan toevoegen:

SQL:

SELECT opusID, COUNT(instrumentID) AS num_instrumenten
FROM instrumentopus
WHERE instrumentID IN (... je lijst ...)
GROUP BY opusID
HAVING num_instrumenten = [aantal elementen in je lijst]

[ Voor 28% gewijzigd door .oisyn op 04-09-2008 21:44 ]

[pedorus]

thegersom schreef op donderdag 04 september 2008 @ 18:13:
Dus iets van de vorm:
...

Oftewel:

SQL:

create table opus(opusID int, instrumentID int);
insert into opus values (4,8),(4,10),(5,8),(5,11),(5,13),(6,8);

Maar als iemand twee checkboxes aanvinkt, met bv instrumentID 8 en 11 hoe krijg ik dan opusID 5 terug?

Dit is een hele mooie theoretische vraag. Hier zit een mooi stukje theorie achter met een 'voor alle' oftewel een omgekeerde A oftewel "universal quantifier". SQL kent deze enkel niet, dus dit kan alleen met een workaround. Een 'voor alle' is te schrijven als 'er is niet een ... waarbij niet ...'. In dit geval is dat: "er er niet een instrumentID die matcht waarvoor geen bijbehorende combinatie (instrumentID, opusID) is". Dan kom je op:

SQL:

select distinct opusID 
from opus o1 
where not exists 
    (select * from opus o2 
     where o2.instrumentID in (8,11) and not exists
        (select * from opus o3 
         where o1.opusid=o3.opusID and o3.instrumentID=o2.instrumentID));

Enkel ik zou persoonlijk toch gaan voor een ander, minder theoretisch mooi, alternatief (wat .oisyn net heeft gepost zie ik):

SQL:

select opusID from opus 
where instrumentID IN (8,11) 
group by opusID 
having count(*)=2

(nog een andere is meerdere self-joins, wat hier al is aangegeven.)

Verwijderd schreef op donderdag 04 september 2008 @ 20:18:
Als je je InstrumentID's goed onder controle hebt, en dus SQL injection kunt uitsluiten, dan kun je de geselecteerde instumentID's in een komma gescheiden string zetten.

Dat kun je zo controleren met

PHP:

preg_match("/^[0-9]+(,[0-9]+)*$/",$instrumentIDs)

[thegersom]

Dank u! Werkt perfect.
Het is uiteindelijk dit geworden:

SQL:

SELECT opus.ID, opus.opusNumber, opus.opusSubNumber, opus.name, opus.year, genre.genreNL, bundel.bundelNL 
FROM instrumentopus 
INNER JOIN opus ON instrumentopus.opusID = opus.ID 
INNER JOIN genre ON opus.genreID = genre.ID 
INNER JOIN bundel ON opus.bundelID = bundel.ID 
WHERE instrumentID IN (29,49) 
GROUP BY opusID 
HAVING count(*) >= 2

[ Voor 88% gewijzigd door thegersom op 04-09-2008 22:44 ]