[MYSQL/PHP] unieke resultaten uit queries bundelen

[amoen]

ok ik heb de volgende tabel

code:

(id)    (hash)
1   d9b028cd6d95370e80455e991ac25dc4
4   d9b028cd6d95370e80455e991ac25dc4
3   f5fb6edb7dd0713dbe17082b8374bf43
2   f5fb6edb7dd0713dbe17082b8374bf43
4   f5fb6edb7dd0713dbe17082b8374bf43
1   cdde41f7134323aaa37a76b36ab2bcfe
1   da829630c15aad866d5138d91c62240d

en wil met de volgende queries:

code:

SELECT (h) FROM `tabel` WHERE `id` = '4' 
SELECT (h) FROM `tabel` WHERE `id` = '1'

checken welke codes (hash) aan BEIDE queries voldoen

nu hab ik al eerder iets dergelijks gemaakt waarin ik beide zoekresultaten in een array zet en dan alle hash codes naloop met de php-code:

code:

if ( in_array($hash, $unieke_array_y)  && in_array($hash, $unieke_array_z) ) {
  $definitieve_array[] = $hash;
}

vervolgens plaatst ie dan alleen die codes in $definitieve_array die in beide queries als resultaat naar boven kwamen...

probleem
het probleem is nu dat ik van tevoren niet weet hoeveel queries er zullen zijn (hangt af van de hoeveelheid id's waarop men zoekt) en kan ik bovendien die in_array-if statement niet variabel langer of korter maken...

snap iemand wat ik probeer?

de oplossing voor mijn probleem zou zijn:
een loopje die (afhankelijk van het aantal queries), checkt welke 'hash' in ALLE queries voorkomt...

[ Voor 3% gewijzigd door amoen op 17-07-2006 10:59 ]

[Brupje]

SELECT (h) FROM `tabel` WHERE `id` = '4' and `id` = '1' group by hash

dit geeft alle hashes die aan een van beide voldoen. Als je meerdere ids moet hebben kun je met php wel een string maken ervan en dan in de quere doen bv.

$ids= "1,2,4";

SELECT (h) FROM `tabel` WHERE `id` in ($ids) group by hash

edit: and van gemaakt

[ Voor 11% gewijzigd door Brupje op 17-07-2006 11:14 ]

[moozzuzz]

amoen schreef op maandag 17 juli 2006 @ 10:58:
de oplossing voor mijn probleem zou zijn:
een loopje die (afhankelijk van het aantal queries), checkt welke 'hash' in ALLE queries voorkomt...

in php maak je een scriptje dat

PHP:

//loopstructuur die gewenste id's vb uit een array? haalt (vb foreach oid)
$de_gewenste_ids .= ' AND `id` = '.$een_gewenst_id;

PHP:

// query wordt dan
$query = 'SELECT blah FROM blah WHERE 1'.$de_gewenste_ids.';';

Wanneer men id=1, id=3, id=4 en id=10 wenst krijgt men volgende query:

SQL:

SELECT blah FROM blah WHERE 1 AND `id`=1 AND `id`=3 AND `id`=4 AND `id`=10;

[amoen]

Brupje schreef op maandag 17 juli 2006 @ 11:08:
SELECT (h) FROM `tabel` WHERE `id` = '4' and `id` = '1' group by hash

dit geeft alle hashes die aan een van beide voldoen. Als je meerdere ids moet hebben kun je met php wel een string maken ervan en dan in de quere doen bv.

$ids= "1,2,4";

SELECT (h) FROM `tabel` WHERE `id` in ($ids) group by hash

edit: and van gemaakt

wow dit is zoo gemakkelijk haha en ik maar moeilijk doen met in_array ....
bedankt, hiermee ga ik verder!

jij ook bedankt moozzezz, dat loopje wat jij gaf heb ik ook al (in een andere vorm)
het ging me meer om een variabele if-statement, maar die heb ik nu nier meer nodig!

[moozzuzz]

* moozzuzz heeft ook weer iets bijgeleerd (of eigenlijk ergens uit het verre verleden naar boven gehaald)

thanx!

[Matthis]

moozzuzz schreef op maandag 17 juli 2006 @ 11:11:
[...]


in php maak je een scriptje dat

PHP:

//loopstructuur die gewenste id's vb uit een array? haalt (vb foreach oid)
$de_gewenste_ids .= ' AND `id` = '.$een_gewenst_id;

kun je toch eenvoudig implode() voor gebruiken ipv een loop?

[Dido]

Ik heb altijd geleerd bij Booleanse logica dat als ik alle rode autos en alle blauwe autos wil, ik moet selecteren op auto's die rood OF blauw zijn, aangezien de auto's die rood EN blauw zijn niet voorkomen.

Je hebt per record maar 1 ID, dus id=1 en id=4 voldoet geen enkel record aan.

Iets als

code:

select h from tabel where ID=1 and h in (select h from tabel where ID=4)

of

code:

select a.h from tabel a join tabel b on a.h = b.h where a.id=1 and b.id=4

lijkt me dan meer voor de hand te liggen.

Ik kan er natuurlijk helemaal naast zitten en over het hoofd zien dat MySQL erg goed interpreteerd wat de gebruiker eigenlijk bedoelt als ik iets intikt, maar of het zooo erg is betwijfel ik.

[amoen]

na een aantal voorstellen geprobeert te hebben, werkt deze als enige:

Dido schreef op maandag 17 juli 2006 @ 17:31:
select a.h from tabel a join tabel b on a.h = b.h where a.id=1 and b.id=4

hoe ik de query nu makkelijk variabel maak weet ik nog niet, maar handmatig werkt ie perfect voor me, thank dido!!

[P_de_B]

Kun je hier nu mee vooruit? Je moet immers vanaf de php code weten welke id's je hebt. Wil je niet een universeel scriptje dat aangeeft welke hashcodes in alle beschikbare id's zitten?

Daarnaast heeft Dido natuurlijk gelijk, WHERE ID =1 AND ID=2 geeft natuurlijk geen records terug.

als MySQL dat wel doet is het echt officeel een rampproduct
edit:

zoiets

SQL:

select [hash]
from tabel
group by [hash] having count(id) = (select count(distinct id) from tabel)

Het aantal unieke combinaties van hash en id wordt hier vergeleken met het aantal unieke id's. Wijkt het aantal niet af van elkaar heb je dus een hash die in elke id voorkomt.

[ Voor 32% gewijzigd door P_de_B op 17-07-2006 21:56 ]

[Dido]

P_de_B schreef op maandag 17 juli 2006 @ 21:45:
Kun je hier nu mee vooruit? Je moet immers vanaf de php code weten welke id's je hebt. Wil je niet een universeel scriptje dat aangeeft welke hashcodes in alle beschikbare id's zitten?

Dan issie heel simpel aan te passen om alle dubbelen eruit te halen:

code:

select a.h from tabel a join tabel b on a.h = b.h where a.id <> b.id

of eigenlijk nog simpeler:

code:

select h, count(*) from tabel group by h having count(*) > 1

Maar goed, dan krijg je alle niet-unieke hashes. Ik weet niet zeker of dat is wat de TS wil?

[P_de_B]

Dido schreef op maandag 17 juli 2006 @ 21:55:
[...]

Dan issie heel simpel aan te passen om alle dubbelen eruit te halen:

code:

select a.h from tabel a join tabel b on a.h = b.h where a.id <> b.id

of eigenlijk nog simpeler:

code:

select h, count(*) from tabel group by h having count(*) > 1

Maar goed, dan krijg je alle niet-unieke hashes. Ik weet niet zeker of dat is wat de TS wil?

Dat geeft toch aleen hashcode's die in meer dan 1 id voorkomen? Ik dacht dat ts wilde weten welke hashcodes in elk van de in de tabel beschikbare id's voorkomen.

id, hash
1, qw
2, qw
3, qw
1, er
2, er
3, er
1, ty
2, ty

uit bovenstaand voorbeeld moeten hashcodes 'qw' en 'er' getoond worden omdat die voor elk van de beschikbare id's 1,2 en 3 voorkomen.


Maar misschien lees ik het ook wel verkeerd hoor.

[ Voor 4% gewijzigd door P_de_B op 17-07-2006 22:00 ]

[Dido]

P_de_B schreef op maandag 17 juli 2006 @ 21:59:
Dat geeft toch aleen hashcode's die in meer dan 1 id voorkomen?

Klopt.

Ik dacht dat ts wilde weten welke hashcodes in elk van de in de tabel beschikbare id's voorkomen.

Dacht het niet, in de TS staat een hash die in drie ID's voorkomt. Maar goed, dan is het nog steeds niet ingewikkeld:

code:

select h, count(*) from tabel group by h having count(*) = max(count(*))

werkt alleen als er minimaal 1 hash in alle id's voorkomt.

Alternatief:

Bepaal het aantal ID's (een count(*) op de tabel met id als PK, of een max(ID) op deze tabel, werkt alleen als ID een autonum is zonder "gaten"), vervolgens

code:

select h, count(*) from tabel group by h having count(*) = aantalIDs

[P_de_B]

Dido schreef op maandag 17 juli 2006 @ 22:08:
[...]


Bepaal het aantal ID's (een count(*) op de tabel met id als PK, of een max(ID) op deze tabel, werkt alleen als ID een autonum is zonder "gaten"), vervolgens

code:

select h, count(*) from tabel group by h having count(*) = aantalIDs

Of dus zoals ik al gepost had?

[Dido]

P_de_B schreef op maandag 17 juli 2006 @ 22:10:
Of dus zoals ik al gepost had?

Als je dat er stiekum bij inedit kan ik het over het hoofd zien

Ik weet alleen niet of subqueries gevroten worden door de MySQL-versie van de TS en wat performance betreft is het bij een grote tabel handig om die subquery 1 keer apar te doen, en de waarde aan de volgende mee te geven, anders bepaal je voor iedrecord het aantal ID's.

[P_de_B]

Dat is waar, ik zocht alleen naar een sql only versie

Ik denk trouwens dat een beetje optimizer die count op de hele tabel er wel goed doet (dus maar 1 keer uitvoert)

[amoen]

het was inderdaad de bedoeling dat de hash-code aan alle id-eisen moet voldoen. dus niet simpelweg de dubbelen eruit filteren, maar echt kijken: heeft de hashcode EN id=1 EN id=4...

het probleem is echter:

in situatie x
wil ik weten welke hash-codes voldoen aan de eisen ID = 1 AND ID= 4

in situatie y
wil ik weten welke hash-codes voldoen aan de eisen ID = 1 AND ID= 4 AND ID= 12 AND ID= 44

en de query die ik dan nodig heb moet dan via php opgebouwd worden...
hehe

dus
select a.h from tabel a join tabel b on a.h = b.h where a.id=1 and b.id=4

en
select a.h from tabel a join tabel b on a.h = b.h where a.id=1 and b.id=4

en
select a.h from tabel a join tabel b on a.h = b.h where a.id=1 and b.id=4

moeten variabel worden

het eerste gedeelte is me zojuist gelukt wel met deze code

PHP:

<?php
$aantal_ids = 4;

$sql = "select ";

$x = 1;
for ($i=1; $i<=$aantal_ids; $i++){
 if ($x == $aantal_ids) { 
   $join = "";
 } else if ($x >= 1) {
   $join = "join ";
 } else {
   $join = "";
 } 
 $sql .= "x_".$i.".h from tabel x_".$i." ".$join;
 $x++;
}

echo $sql;
?>

met als resultaat:
select x_1.h from tabel x_1 join x_2.h from tabel x_2 join x_3.h from tabel x_3 join x_4.h from tabel x_4
(ik gebruik x_1 en x_2 in plaats van a en b is ff makkelijker)

maar hoe doe ik de rest nu?

[Dido]

je moet alleen je tabellen joinen en niet je velden, om te beginnen.

Je wilt dus (in pseudo-code) iets als:

code:

SQL = "select h from tabel x_1 "

For all Id from 2 to N
    SQL = SQL + "join tabel x_" + i + " on x_1.ID = x_" + i + " "
Next

SQL = SQL + "where x_1.ID = " + id(1) + " "

For all Id from 2 to N
    SQL = SQL + "and x_" + i + "=" + id(i) + " "
Next

Je moet goed je repeterende delen analyseren

select x_1.h from tabel x_1
[ join tabel x_n on x_1.ID = x_n.ID ]
where x_1.ID = ID(1)
[ and x_n.id = ID(n) ]

de stukken tussen [] zijn repeterend, de rest niet

Nota bene: "tabel" staat natuurlijk voor de *naam* van je eigen tabel, en is geen keyword of zo

[ Voor 12% gewijzigd door Dido op 17-07-2006 23:09 ]

[P_de_B]

Dan kun je het nog eenvoudiger doen volgens mij:

SQL:

select [hash]
from tabel
group by [hash] having count(id) >= (select count(distinct id) from tabel where id in (1,2,3))

Je hoeft alleen via PHP de query maar met de goede variabelen in het 'where id in' gedeelte op te bouwen. Voorwaarde is wel dat je MySQL > 4.0 (of 4.1) hebt voor het ondersteunen van subselects.

Je kunt de >= nog vervangen door = als je wilt dat je alleen hashcodes terug krijgt die precies in de opgegeven id's zitten. Je krijgt met >= ook hascodes terug die naast id 1,2 en 3 ook nog in 4 zit (terwijl je dat niet hebt opgegeven). Gebruik je het = teken krijg je die niet terug.

[Dido]

Daar gaat iets mis meer, P_de_B

code:

(select count(distinct id) from tabel where id in (1,2,3))

Geeft altijd 3 terug (op voorwaarde dat id's 1, 2 en 3 bestaan)

Je krijgt dus alle hashes met drie of meer id's, maar je weet niet welke id's er bij die hashes horen (een hash met id's 4, 5, 6 komt in jouw query terug, terwijl je die niet wilt).

Mischien iets als

SQL:

  select hash
    from tabel
   where id in (1, 2, 3)
group by hash
  having count(*) = 3;

Moet je twee stukje opbouwen, maar da's niet zo heel ingewikkeld.

Ik zit er nou naar te kijken, maar dit is wel heel simpel. Kan dat?

[amoen]

klikt wel erg simpel ja


kun je me misschien nog eens uitleggen wat die COUNT nou precies doet?
want dat ontgaat me compleet!

[Dido]

amoen schreef op dinsdag 18 juli 2006 @ 10:36:
klikt wel erg simpel ja

kun je me misschien nog eens uitleggen wat die COUNT nou precies doet?
want dat ontgaat me compleet!

In 2 stappen doet dit statement het volgende:

• Haal alle hashes op met een id= 1, 2, of 3
• Toon alleen de hashes die nu drie keer voorkomen.

Dat laatste betekent dus dat een hash voorkwam met id 1, 2, en 3.

Die count telt per hash hoevaak ie voorkomt. Misschien moet je de count(*) in je select opnemen, mischien hoeft dat niet.

Iets anders gesteld: je zoekt nu de hashes die drie keer voorkomen, en die bestaan met ID 1, 2, of 3. Als aan allebei de voorwaarden voldaan is heb je dus een hash met id 1, 2 en 3.

Let op dat de "3" bij de count het aantal ID's is, geen ID

[ Voor 19% gewijzigd door Dido op 18-07-2006 10:50 ]

[P_de_B]

Dido schreef op dinsdag 18 juli 2006 @ 10:21:
Daar gaat iets mis meer, P_de_B

inderdaad een beetje stom