[opgelost] Opbouwen select query
Ik heb een tabel cases:
case_id,
..
..
Daarnaast heb ik een koppeltabel:
id,
case_id,
categorie_id
1 case kan 1, 2 of 3 categorieen hebben. Dus ook 1, 2 of 3 records in de koppeltabel.
Uiteindelijk wil ik het case_id van de koppeltabel selecteren en maar 1x tonen als het alle (1, 2 of 3) waardes bevat van alle geselecteerde categorieen. En de case_id's die bijvoorbeeld maar 1 of 2 van de 3 geselecteerde waardes bevat mag hij niet tonen.
Heb op dit moment de onderstaande query. Op het moment dat ik 1 categorie selecteer gaat het goed, maar bij 2 of 3 vindt hij het niet leuk en haal ik geen resultaten meer op. Terwijl die er wel zijn. Dit heeft te maken met de AND, als ik de vervang naar OR haalt hij wel alle resultaten op, maar toont hij ook die maar 1 of 2 van de 3 categorieën bevat.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
$Tquery1 = "SELECT id,categorie_id,case_id FROM koppeltabel WHERE categorie_id = '".$categorie_id[0]."'";
if (!(empty($categorie_id[1]))){
$Tquery1 .= " AND categorie_id = '".$categorie_id[1]."'";
}
if (!(empty($categorie_id[2]))){
$Tquery1 .= " AND categorie_id = '".$categorie_id[2]."'";
}
$Tquery1 .= "GROUP BY case_id ORDER BY id DESC";
if (!(empty($categorie_id[1]))){
$Tquery1 .= " AND categorie_id = '".$categorie_id[1]."'";
}
if (!(empty($categorie_id[2]))){
$Tquery1 .= " AND categorie_id = '".$categorie_id[2]."'";
}
$Tquery1 .= "GROUP BY case_id ORDER BY id DESC";
(het bovenstaande verdient vast niet de schoonheidsprijs)
Hoe bouw ik mijn query op de beste manier op?
Gewijzigd op 31/05/2013 15:10:14 door Bas IJzelendoorn
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT case_id
FROM koppeltabel
WHERE categorie IN (1,2,3)
GROUP BY case_id
HAVING COUNT(*) = 3
ORDER BY case_id
FROM koppeltabel
WHERE categorie IN (1,2,3)
GROUP BY case_id
HAVING COUNT(*) = 3
ORDER BY case_id
Hiermee selecteer je eerst eigenlijk alle regels, groepeer je op case_id en dan filter je op het aantal rijen. Alleen de case_ids met drie rijen (dus alle drie de mogelijkheden) worden via de HAVING geselecteerd.
Mij is alleen niet helemaal duidelijk of de categorieen waarop je selecteert variabel is. Zo ja, dan moet je mogelijk het where statement en het having statement aanpassen aan de selectie van categorieen.
Gewijzigd op 30/05/2013 14:10:43 door Erwin H
tabel cases:
id <- koppeling case_id uit de koppeltabel
naam
datum
actief <- en hij mag alleen de resultaten geven die 'actief' zijn
Hoe doe ik dit?
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT case_id
FROM koppeltabel
WHERE categorie IN (1,2,3)
AND actief = true
GROUP BY case_id
HAVING COUNT(*) = 3
ORDER BY case_id
FROM koppeltabel
WHERE categorie IN (1,2,3)
AND actief = true
GROUP BY case_id
HAVING COUNT(*) = 3
ORDER BY case_id
Mocht je nu een ander aantal cetegorieen nodig hebben dan pas je je query daarop aan:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT case_id
FROM koppeltabel
WHERE categorie IN (4,5)
AND actief = true
GROUP BY case_id
HAVING COUNT(*) = 2
ORDER BY case_id
FROM koppeltabel
WHERE categorie IN (4,5)
AND actief = true
GROUP BY case_id
HAVING COUNT(*) = 2
ORDER BY case_id
Zo te zien heb je de nodige categorieen al in een array staan, dus de query kan je vrij eenvoudig in php opbouwen
Code (php)
Het if statement wil je er wel omheen, want als je array leeg is krijg je dit:
Code (php)
1
WHERE categorie IN ()
en dat levert je een foutmelding op.
Gewijzigd op 30/05/2013 17:16:18 door Erwin H
Helpt me al een eind op weg. Nu is het echter zo dat 'actief' niet uit de tabel 'koppeltabel' komt maar uit de tabel 'cases'.
Daarnaast moet hij net als het veld 'actief', ook de velden 'naam' en 'datum' uit de tabel 'cases' halen. De gehele structuur ziet er zo uit:
categorieen:
id,
type,
categorie_naam
koppeltabel:
id,
*case_id, <- id van cases
categorie_id <- id van categorieen
cases:
id <- koppeling case_id uit de koppeltabel
*titel
*datum
*actief <- en hij mag alleen de resultaten geven die 'actief' zijn
De velden waar een * wil ik met behulp van de query uit de database
Ik heb nu het volgende:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php
// selectie boxen optellen
foreach($_POST['Torganisatie'] as $Torganisatie) {
$categorie_id[] = mysql_real_escape_string($Torganisatie);
}
// query opbouwen
if ( count( $categorie_id ) > 0 ){
$sql = "SELECT k2.id, k1.case_id, k2.active, k2.titel
FROM koppeltabel AS k1,
cases AS k2
WHERE k1.categorie_id IN (".implode( ",", $categorie_id ).")
AND k2.id IN (".implode( ",", $categorie_id ).")
AND k2.active='actief'
GROUP BY k1.case_id
HAVING COUNT(*) = ".count( $categorie_id )."
ORDER BY k2.id";
}
// query uitvoeren
$resultaat = mysql_query($sql);
if($resultaat && mysql_num_rows($resultaat) >= 1){
// resultaten tonen
while($res = mysql_fetch_array($resultaat)) {
$id = mysql_real_escape_string ($res['id']);
$titel = mysql_real_escape_string ($res['titel']);
echo $id.'<br>';
echo $titel.'<br>';
}
// indien geen resultaat
} else {
echo 'geen resultaat<br>';
echo $sql; // <---- VERWIJDEREN
}
?>
// selectie boxen optellen
foreach($_POST['Torganisatie'] as $Torganisatie) {
$categorie_id[] = mysql_real_escape_string($Torganisatie);
}
// query opbouwen
if ( count( $categorie_id ) > 0 ){
$sql = "SELECT k2.id, k1.case_id, k2.active, k2.titel
FROM koppeltabel AS k1,
cases AS k2
WHERE k1.categorie_id IN (".implode( ",", $categorie_id ).")
AND k2.id IN (".implode( ",", $categorie_id ).")
AND k2.active='actief'
GROUP BY k1.case_id
HAVING COUNT(*) = ".count( $categorie_id )."
ORDER BY k2.id";
}
// query uitvoeren
$resultaat = mysql_query($sql);
if($resultaat && mysql_num_rows($resultaat) >= 1){
// resultaten tonen
while($res = mysql_fetch_array($resultaat)) {
$id = mysql_real_escape_string ($res['id']);
$titel = mysql_real_escape_string ($res['titel']);
echo $id.'<br>';
echo $titel.'<br>';
}
// indien geen resultaat
} else {
echo 'geen resultaat<br>';
echo $sql; // <---- VERWIJDEREN
}
?>
Echter krijg ik nu 1 case 2x eruit eruit, ipv 2 á 3 cases. Volgens mij zit ik gewoon echt onnodig te prutsen :S
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT c.id, c.type, c.categorie_naam, d.titel, d.datum, d.actief
FROM koppeltabel AS k
LEFT JOIN categorieen A c ON c.id = k.categorie_id
LEFT JOIN cases AS d ON d.id = k.case_id
WHERE k.categorie_id IN ('.implode( ',', $categorie_id ).')
AND d.actief = true
GROUP BY k.case_id
HAVING COUNT(*) = '.count( $categorie_id ).'
ORDER BY c.id DESC';
FROM koppeltabel AS k
LEFT JOIN categorieen A c ON c.id = k.categorie_id
LEFT JOIN cases AS d ON d.id = k.case_id
WHERE k.categorie_id IN ('.implode( ',', $categorie_id ).')
AND d.actief = true
GROUP BY k.case_id
HAVING COUNT(*) = '.count( $categorie_id ).'
ORDER BY c.id DESC';
Wellicht nog ergens toch een foutje, maar met die LEFT JOIN werkt het ook wel.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT k.case_id
FROM koppeltabel k
INNER JOIN cases c ON k.case_id = c.id
WHERE k.categorie IN (4,5)
AND c.actief = true
GROUP BY k.case_id
HAVING COUNT(*) = 2
ORDER BY k.case_id
FROM koppeltabel k
INNER JOIN cases c ON k.case_id = c.id
WHERE k.categorie IN (4,5)
AND c.actief = true
GROUP BY k.case_id
HAVING COUNT(*) = 2
ORDER BY k.case_id
Hier kan je dan eventueel nog extra kolommen uit de cases tabel bij voegen in de SELECT, mits je die ook in de GROUP BY zet.
Informatie uit de categorie tabel kan je er eigenlijk niet bij zetten. Doe je dat wel dan krijg je mogelijk meer dan 1 rij per case eruit.
Gewijzigd op 30/05/2013 21:32:20 door Erwin H
Maar de categorie kan je inderdaad niet pakken omdat je daarvan 3 verschillende hebt (in 1 rij).
Maar goed, al met al komen we er wel.
Moest hem hier en daar nog even ietsjes aanpassen naar eigen velden. Maar hij werkt nu perfect. Mijn dank is groot! Ik zag het vandeweek gewoon weg ff niet meer :)