opzet database: werk van leerlingen inleveren
Een dump daarvan staat online op http://www.zunflappie.nl/temp/phphulp_digiwerk.sql.zip
Als je die inlaad (128kb, InnoDB) kan je mijn tabellen zien.
Bedoeling van mijn (toekomstige) systeem:
- leerkracht van een basisschool (zoals ikzelf) meld zich aan
- leerkracht maakt school aan (als die er nog niet is)
- leerkracht voegt leerlingen toe (of eventueel andere leerkrachten)
- leerkracht voegt opdrachten toe
- leerlingen melden zich aan op site
- leerlingen lezen opdrachten en leveren die digitaal in (of tekst, of bestand)
- leerkracht vraagt lijst met zijn klas/school op en ziet dus het ingeleverde werk
Dit om het innemen van (digitaal) werk te vermakkelijken. Geen emailen meer, alles op 1 rij etc.
En leerlingen kunnen op school én thuis inleveren.
Nu is het idee leuk en ik kan alles met PHP gaan doen, maar ik verwacht dat het storm gaat lopen (ja, eerlijk waar!) en dus moet het snel en makkelijk met SQL. Dus geen filters gebruiekn in PHP, maar alleen selecteren wat nodig is.
Ik ga hiervoor PDO gebruiken (veiligheid/snelheid). Maar sommige SQL-queries zijn lastig.
Ten eerste
Is de database maximaal genormaliseerd? Geen informatie dubbel etc?
Ik zit zelf ook te twijfelen over de tabel groepen, maar hoe anders?
Dit zal toch niet uitgebreid worden etc.
Ten tweede
Opdrachten die een leerling heeft uitgevoerd moet hij zien.
Maar hij moet ook een lijst zien van opdrachten die, bij zijn school+groep horen die nog niet gedaan zijn.
Wat is daarvoor de SQL?
Ik verwacht iets met WHERE NOT(), maar dat gaat niet werken.
Ik kan niet zeggen: WHERE NOT school = 1, 2, 3, 4, 6, 7, 8, 1282, 1283 etc als hij op school 5 zit.
Maar hoe wel?
Ik verwacht dat in dit project nog meer vragen gaan komen, maar ik begin bij het begin: de database.
Gewijzigd op 01/01/1970 01:00:00 door Eddy Erkelens
Gesponsorde koppelingen:
Maar dat is voor de meeste basisschool-leerlingen te ingewikkeld.
Er komt ook een inlog voor de school (ander kan iedereen exact zien welke kinderen/namen er op welke school in welke plaats zitten), maar dat is voor de leerkracht.
De WHERE school = 5 AND groep = 8 (groep7) is natuurlijk logisch.
Maar dat kunnen ook wel eens 90 kinderen zijn.
En als die allemaal 10 werkstukken inleveren, dat zijn er 900.
Geen probleem, maar ik wil weten wat de leerling nog NIET ingeleverd heeft.
Dan kan je NOT IN gebruiken (mysql 5 en hoger)
Chilion
Nee, ik een tabel met scholen + een tabel met groepen.
De combinatie (school/plaats + groep) is bepalend.
Ik ga natuurlijk geen honderden tabellen aanmaken ;).
Ik heb dus een koppeltabel voor de opdrachten (waarin staat opgeslagen: school+groep).
Een tabel gebruikers (school+groep)
Zie ook die databasedump.
De school bestaat uit 2 delen: naam van de school + plaatsnaam.
Bijvoorbeeld 'De Wegwijzer' is zo algemeen... daar zijn er vast meer van.
Gewijzigd op 01/01/1970 01:00:00 door Peter Wessels
Maar wat jij zegt: het is inderdaad nogal complex.
En dat wil ik er dus juist uithalen.
Ik zal eens kijken of ik een soort mirror van Teletop kan maken. Zo niet: dan ga ik door met mijn vereenvoudigdere versie.
En daar komen de ingewikkeldere queries.
Ik heb dit:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT
CASE
WHEN EXISTS
(
SELECT id
FROM werken AS w
WHERE w.gebruiker_id = 2
AND w.opdracht_id = 3
)
THEN 'true'
ELSE 'false'
END AS gedaan
CASE
WHEN EXISTS
(
SELECT id
FROM werken AS w
WHERE w.gebruiker_id = 2
AND w.opdracht_id = 3
)
THEN 'true'
ELSE 'false'
END AS gedaan
Dat geeft gewoon TRUE, zoals het hoort!
Dan onderstaande code:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT DISTINCT
g.id,
g.naam,
w.id AS werk_id,
w.datum,
w.opdracht_id
FROM gebruikers AS g
LEFT JOIN werken AS w
ON ( g.id = w.gebruiker_id )
WHERE g.school_id = 1
AND g.groep_id = 8
g.id,
g.naam,
w.id AS werk_id,
w.datum,
w.opdracht_id
FROM gebruikers AS g
LEFT JOIN werken AS w
ON ( g.id = w.gebruiker_id )
WHERE g.school_id = 1
AND g.groep_id = 8
Dit geeft resultaten te veel.
Wel alle namen en OF ze hun werk hebben ingeleverd.
Maar ik krijg alle opdrachten.
Ik wil dus dit bij maar 1 opdracht.
Uiteraard heb ik een AND o.id = 3 toegevoegd, maar dan krijg ik niet meer alle namen.
De combinatie werkt niet (foutmelding!):
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT DISTINCT
g.id,
g.naam,
w.id AS werk_id,
w.datum,
w.opdracht_id,
SELECT
CASE
WHEN EXISTS
(
SELECT id
FROM werken AS w
WHERE w.gebruiker_id = g.id
AND w.opdracht_id = w.id
)
THEN 'true'
ELSE 'false'
END AS gedaan
FROM gebruikers AS g
LEFT JOIN werken AS w
ON ( g.id = w.gebruiker_id )
WHERE g.school_id = " . mysql_real_escape_string($_SESSION['school_id']) . "
AND g.groep_id = " . mysql_real_escape_string($_SESSION['groep_id']) . "
g.id,
g.naam,
w.id AS werk_id,
w.datum,
w.opdracht_id,
SELECT
CASE
WHEN EXISTS
(
SELECT id
FROM werken AS w
WHERE w.gebruiker_id = g.id
AND w.opdracht_id = w.id
)
THEN 'true'
ELSE 'false'
END AS gedaan
FROM gebruikers AS g
LEFT JOIN werken AS w
ON ( g.id = w.gebruiker_id )
WHERE g.school_id = " . mysql_real_escape_string($_SESSION['school_id']) . "
AND g.groep_id = " . mysql_real_escape_string($_SESSION['groep_id']) . "
De foutmelding die ik krijg is #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT
CASE
WHEN EXISTS
(
SELECT id
FROM werken AS w
WHERE w.gebruiker' at line 8
Dus los werkt het wel, in combinatie niet.
Nu lijkt me dit wel gaan werken, maar het moet toch wel makkelijker kunnen?
Wat ik wil:
Een lijst met alle namen van groep 7 van school 1, en dan de datum erbij wanneer ze hun werk hebben ingeleverd.
Wanneer die nog leeg is, is het werk nog niet ingeleverd.
De rest kan met php, maar dit wil ik met de database doen.
En een query in een query-loop in een query-loop lijkt me ook niet alles.
(ik heb nu al een query in een loop....., daar kom ik later op terug).
Dan de vraag: wie kan me helpen met deze sql-actie?
Ik wil een overzicht van alle groepen op mijn school (via school_id) met daarbij een COUNT op aantal gebruikers.
Bij de leerlingen lukt dat wel (groep 1 t/m 8), maar voor de groep leerkrachten lukt dat niet.
Met onderstaande query krijg ik goede resultaten voor de groepen 1-8, maar bij de groep leerkrachten krijg ik het aantal van ALLE leerkrachten (ook van andere scholen).
Dus er zit ergens een klein foutje.
Ik denk bij de OR ...
Maar ik kom er niet uit, wellicht iemand van jullie?
Query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
g.id,
g.naam,
COUNT( gebr.id ) AS aantal
FROM
groepen AS g
LEFT JOIN gebruikers AS gebr
ON ( gebr.groep_id = g.id )
WHERE
g.school_id = ".$_SESSION['school_id']."
OR
g.school_id = 1
AND NOT
gebr.id = 1
GROUP BY
g.id
ORDER BY
g.naam ASC
g.id,
g.naam,
COUNT( gebr.id ) AS aantal
FROM
groepen AS g
LEFT JOIN gebruikers AS gebr
ON ( gebr.groep_id = g.id )
WHERE
g.school_id = ".$_SESSION['school_id']."
OR
g.school_id = 1
AND NOT
gebr.id = 1
GROUP BY
g.id
ORDER BY
g.naam ASC
Database-dump: http://www.zunflappie.nl/temp/eddy.sql.zip
Gewijzigd op 01/01/1970 01:00:00 door Eddy Erkelens
Ik ben nu bezig met een overzichtstabel.
Per groep/klas moet dit dynamisch gemaakt worden.
Dit moet het resultaat worden:
Nu nog met weinig cijfers, maar dat is geen probleem.
Het gemiddelde berekenen ook niet.
Maar de queries wel!
Ik heb nu 3 queries:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
// alle queries hier neerzetten
$gebruikers = fetch(" SELECT gebruikers.id, gebruikers.naam
FROM gebruikers
WHERE gebruikers.groep_id = ".intval($_GET['g'])."
");
$opdrachten = fetch("SELECT id, titel FROM opdrachten WHERE groep_id = ".intval($_GET['g'])."");
$cijfer = fetch("
SELECT
b.naam, b.waarde
FROM werken AS w
LEFT JOIN beoordelingen AS b
ON (w.beoordeling_id = b.id)
WHERE w.gebruiker_id = ".$gebruiker['id']."
AND w.opdracht_id = ".$opdracht['id']." ");
?>
// alle queries hier neerzetten
$gebruikers = fetch(" SELECT gebruikers.id, gebruikers.naam
FROM gebruikers
WHERE gebruikers.groep_id = ".intval($_GET['g'])."
");
$opdrachten = fetch("SELECT id, titel FROM opdrachten WHERE groep_id = ".intval($_GET['g'])."");
$cijfer = fetch("
SELECT
b.naam, b.waarde
FROM werken AS w
LEFT JOIN beoordelingen AS b
ON (w.beoordeling_id = b.id)
WHERE w.gebruiker_id = ".$gebruiker['id']."
AND w.opdracht_id = ".$opdracht['id']." ");
?>
De eerste haalt de gebruikers op, de tweede de opdrachten, de derde het cijfer.
Nu heb ik die laatste echter in de for-loop staan.
Ik doe dit:
Code (php)
Niet echt de beste manier. Want bij 30 kinderen maal 10 opdrachten = 300 queries.
Maar ik ben het overzicht inmiddels al lang bijster.
Let op dat er dus 4 tabellen zijn bij betrokken:
gebruikers + opdrachten + werken + beoordelingen
Wie kan mij echt even een flink opzetje geven richting de goede SQL?
Ik zit er nog steeds mee te kloten.
Wat ik nu heb werkt prima, maar kan (neem ik aan) veel handiger en fout-ongevoeliger.
Quote:
SELECT
g.id,
g.naam,
COUNT( gebr.id ) AS aantal
g.id,
g.naam,
COUNT( gebr.id ) AS aantal
En dan:
Quote:
GROUP BY
g.id
g.id
Dat gaat dus niet, je moet in de GROUP BY ook nog g.naam vermelden om gokwerk van MySQL te voorkomen.
Wanneer jij 4 tabellen moet joinen, ga dan 4 tabellen joinen. Het wil niet zeggen dat dit sneller is dan wat losse queries, dat zul je moeten controleren. Ga ook met EXPLAIN aan de slag om te achterhalen hoe de database de query uitvoert om te zien of er wel indexen worden gebruikt. Het blijft MySQL...
pgFrank schreef op 14.02.2009 10:10:
En dan:
Dat gaat dus niet, je moet in de GROUP BY ook nog g.naam vermelden om gokwerk van MySQL te voorkomen.
Wanneer jij 4 tabellen moet joinen, ga dan 4 tabellen joinen. Het wil niet zeggen dat dit sneller is dan wat losse queries, dat zul je moeten controleren. Ga ook met EXPLAIN aan de slag om te achterhalen hoe de database de query uitvoert om te zien of er wel indexen worden gebruikt. Het blijft MySQL...
Quote:
SELECT
g.id,
g.naam,
COUNT( gebr.id ) AS aantal
g.id,
g.naam,
COUNT( gebr.id ) AS aantal
En dan:
Quote:
GROUP BY
g.id
g.id
Dat gaat dus niet, je moet in de GROUP BY ook nog g.naam vermelden om gokwerk van MySQL te voorkomen.
Wanneer jij 4 tabellen moet joinen, ga dan 4 tabellen joinen. Het wil niet zeggen dat dit sneller is dan wat losse queries, dat zul je moeten controleren. Ga ook met EXPLAIN aan de slag om te achterhalen hoe de database de query uitvoert om te zien of er wel indexen worden gebruikt. Het blijft MySQL...
PDO met InnoDB in het geval, maar dat maakt het verschil niet.
Die extra GROUP BY moet er inderdaad nog in.
JOINen is het probleem niet, maar ik heb echt geen idee meer hoe het moet.
Net iets te complex voor mijn geest (zondagochtend om half 10 al helemaal).
Maar wellicht dat die GROUP BY net even het benodigde zetje geeft.
