[MySQL/PHP] Pakt verkeerde veld

[mauricedb]

Voor school moeten we een website bouwen (Formule 1) met behulp van MySQL en PHP (althans, dat is onze keuze). Ik heb in grote lijnen de website in elkaar zitten met alle queries erin en bijna alles doet het, behalve één ding(etje), namelijk:

In een tabel staan vijf velden, rondeID (Auto increment), raceID (foreign), coureurID (foreign), rondenummer en tijd. Deze tabel levert de rondetijden van een race. Met behulp van GROUP BY coureurID laat de tabel keurig van één coureur één tijd zien, maar het probleem is dat hij nu alleen de tijd pakt van de eerste ronde (wat dus lang niet altijd de snelste ronde is). Het komt namelijk ook voor dat de snelste tijd van een coureur in ronde 3 is, maar hij laat dus enkel en alleen maar de eerste ronde/tijd zien van een coureur.

Ik weet niet precies op welk commando ik moet zoeken, dus als iemand van jullie mij op weg kan helpen of kan vertellen hoe en wat, dan zou ik je erg dankbaar zijn!

Hieronder nog twee screens, wat het waarschijnlijk wel wat duidelijker maakt:

Screen met GROUP BY:


Zonder GROUP BY:

[Gomez12]

Lees je eens in over group by. Je zit op de juiste weg, je pakt enkel de aparte group by functionaliteit van mysql die dit veroorzaakt.

Gewoon lezen wat de standaard requirements zijn voor een group by en het probleem is opgelost.

[mauricedb]

Bedankt voor het reageren Ik zit het te proberen met MIN(tijd), maar daar lukt het ook niet mee, dan krijg ik maar één resultaat en dan zet die daar automatisch coureur 1 bij, terwijl dat helemaal niet zijn tijd is. Gebruik ik dan wel 't goede, maar zet ik het er verkeerd neer Of zit ik compleet verkeerd?

Ik heb deze pagina een beetje doorgekeken, maar dan kom ik dus bij die MIN() uit.

[pedorus]

Het vinden van de min(ronde) hier is een zogenaamd groupwise max-probleem, daar is genoeg over te vinden

[Juup]

SQL is een beetje gaar wat betreft min() en max().
Het wordt gezien als vergelijkbaar met avg().

Er kan dus eigenlijk geen row worden teruggegeven want er is (meestal) geen waarde voor tijd die overeenkomt met de gemiddelde tijd.

Oplossing: join met zichzelf en vergelijken op de min(f1.tijd) = f2.tijd

[NMe]

Allereerst: Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk? Verder inderdaad gewoon een groupwise maximum zoals pedorus zegt, dat is een standaardoplossing voor een standaardprobleem.

Juup schreef op donderdag 15 december 2011 @ 23:11:
SQL is een beetje gaar wat betreft min() en max().
Het wordt gezien als vergelijkbaar met avg().

Ehm...wat? MIN() geeft altijd netjes de laagste waarde terug die voldoet aan je WHERE-clause en/of joins, MAX() dito maar dan met de hoogste waarde. AVG() doet netjes middelen en COUNT() telt het aantal rijen in je resultset. Wat is daar "gaar" aan?

[ Voor 20% gewijzigd door NMe op 15-12-2011 23:16 ]

[mauricedb]

Ik denk dat ik 'm wel door heb, hoe die werkt, maar ik krijg het tot zover nog niet werkend Ik weet waar jullie heen willen, dank daarvoor, nu 'm nog juist implementeren

Had tot enkele weken geleden bijna geen kennis van MySQL/PHP, dus voor mij is dat met die groupwise nog wel even een dingetje die ik moet uit puzzelen hoe die precies in elkaar zit en hoe ik die dan moet verwerken. Hij moet groeperen op coureurid zodat er geen dubbele records/coureurs inzitten en hij moet kijken naar de laagste tijd van een coureur en die moet die dan tonen, samen met het eraan vast zittende rondenummer. Right?

[NMe]

Ja, maar de manier waarop je het omschrijft suggereert dat je het lastiger in je hoofd hebt zitten dan het eigenlijk is. Laat eens zien welke query je in gedachten hebt?

[mauricedb]

Ik heb nu de volgende, lange, code (wat uiteraard niet werkt... ):

SQL:

SELECT coureurid, tijd, rondenummer
FROM coureur, ronde AS ronde1 
(SELECT coureurid, MIN(tijd) AS tijdcor FROM coureur, ronde GROUP BY coureurid) AS ronde2 
WHERE ronde2.coureurid = ronde1.coureurid AND ronde1.tijd = ronde2.tijdcor 
ORDER BY tijd ASC

Code even aangepast en in [/code] gezet

[ Voor 59% gewijzigd door mauricedb op 16-12-2011 10:14 ]

[RobIII]

Lees Hoe werkt dat GROUP BY nu eigenlijk? dan nog eens een keer...
Kort: Alle velden die niet in een Aggregate functie opgenomen zijn (min, max, avg, sum etc.) dienen in je group by clause te staan. Dat is in dit forum pas een keer of, oh, 300 miljard miljard keer aan de orde geweest...
Edit: Met dank aan de (on)leesbaarheid gemist dat 't _nu? wel klopt

Verder zou het handig zijn als je code tags zou gebruiken als je code post en 't een beetje probeert te beperken tot de relevante velden/delen/stukken.

mauricedb schreef op vrijdag 16 december 2011 @ 09:55:
Code even aangepast en in [php] gezet

Je kunt ook gewoon [code=sql] gebruiken en daarbij is 't wel fijn als je je code even formatteert/indent om 't een beetje leesbaar te maken. Alles op 1 regel van hier-tot-tokio-inc.-scrollbar maakt 't niet bepaald leesbaarder...

[ Voor 53% gewijzigd door RobIII op 16-12-2011 10:12 ]

[mauricedb]

Edit: Met dank aan de (on)leesbaarheid gemist dat 't _nu? wel klopt

Nee helaas..

[pedorus]

mauricedb schreef op vrijdag 16 december 2011 @ 09:55:
Ik heb nu de volgende, lange, code (wat uiteraard niet werkt... ):

SQL:

SELECT coureurid, tijd, rondenummer
FROM coureur, ronde AS ronde1 
(SELECT coureurid, MIN(tijd) AS tijdcor FROM coureur, ronde GROUP BY coureurid) AS ronde2 
WHERE ronde2.coureurid = ronde1.coureurid AND ronde1.tijd = ronde2.tijdcor 
ORDER BY tijd ASC

Code even aangepast en in [/code] gezet

Vanwaar die "FROM coureur" als je daar niets mee doet, zonder dat je een JOIN ermee doet? (2x) Welke coureurid wil je terughebben (van welke tabel)?

[NMe]

Achter "AS ronde1" mist sowieso een komma, dus dat gaat inderdaad niet werken. Daarnaast is de "SELECT coureurid" ambiguous aangezien je uit 3 verschillende tabellen selecteert die allemaal een eigen coureurid hebben. En dan is die impliciete cross join die je doet zoals pedorus boven me al schrijft niet productief voor het doel waarvoor je deze query nodig hebt.

[mauricedb]

Heb 'm aangepast naar dit, maar dan geeft die ook een (standaard) error aan:

SQL:

SELECT rondenummer, tijd 
FROM ronde AS ronde1, (SELECT MIN(tijd) AS tijdcor FROM ronde) AS ronde2 
WHERE ronde1.tijd = ronde2.tijdcor 
ORDER BY tijd ASC

[ Voor 5% gewijzigd door mauricedb op 16-12-2011 20:52 ]

[pedorus]

Bovenstaande query is correcte sql en geeft dus als het goed is geen error? Welke error krijg je dan? Het enige probleem is dat ie niet doet wat je volgens de TS wil (geen rekening gehouden met coureurid)

[Spiral]

SQL:

SELECT c.Coureurnaam, rondenummer, MIN(tijd) as fastest
FROM ronde AS ronde1
INNER JOIN tabelCoureur AS c ON ronde1.coureurid = c.coureurid
GROUP BY c.coureurid
ORDER BY fastest ASC;

Je groepeert op de coureurid.
dan geef je aan wat voor groepsfunctie je wilt gebruiken
en eventueel op die groepsfunctie sorteren.

Soms heb je gewoon even een werkend voorbeeld nodig en ik hoop dat je hieruit ook lering trekt.

[pedorus]

Spiral schreef op vrijdag 16 december 2011 @ 21:14:

SQL:

SELECT c.Coureurnaam, rondenummer, MIN(tijd) as fastest
FROM ronde AS ronde1
INNER JOIN tabelCoureur AS c ON ronde1.coureurid = c.coureurid
GROUP BY c.coureurid
ORDER BY fastest ASC;

Je groepeert op de coureurid.
dan geef je aan wat voor groepsfunctie je wilt gebruiken
en eventueel op die groepsfunctie sorteren.

Soms heb je gewoon even een werkend voorbeeld nodig en ik hoop dat je hieruit ook lering trekt.

Maar ehh, dat is geen werkend voorbeeld omdat je een onbepaalde waarde voor rondenummer teruggeeft. Waarschijnlijk ook niet eens per ongeluk de juiste.

[Spiral]

Ik dacht er te licht over ... het kan idd niet de juiste ronde weergeven.

zoals al eerder in topic is aangegeven.
http://dev.mysql.com/doc/...mum-column-group-row.html.

[ Voor 46% gewijzigd door Spiral op 16-12-2011 21:53 . Reden: Linkje toegevoegd ]

[mauricedb]

Ik denk dat het wel te doen is voor mij, maar mijn struikelpunt is denk ik de rest van de query. Ik denk dat daar iets verkeerd gaat. Ik moet ''veel'' andere gegevens uit meerdere tabellen halen waardoor ik denk ik struikel.

Ik post hieronder de gehele query waarmee ik het resultaat van de eerste screen in de TS krijg. Als iemand samen met mij de juiste code wil implementeren in de query, graag! Ik heb er denk ik gewoon nog te weinig kennis van waardoor ik door de bomen het bos niet meer zie. Ik wil er graag van leren, maar nu weet ik gewoon niet hoe en wat.

SQL:

SELECT coureur.coureurid, voornaam, achternaam, ronde.coureurid, 
teamcor.coureurid, team.teamid, teamnaam, teamcor.teamid, ronde.rondeid, 
rondenummer, tijd, race.raceid, racenaam, plaats, circuit, datum, ronde.raceid, 
race.seizoenid, seizoen.seizoenid, seizoen, teamcor.seizoenid 
FROM coureur, team, teamcor, race, seizoen, ronde 
WHERE ronde.coureurid = coureur.coureurid AND coureur.coureurid = teamcor.coureurid 
AND team.teamid = teamcor.teamid AND seizoen.seizoenid = race.seizoenid 
AND ronde.raceid = race.raceid AND seizoen.seizoenid = teamcor.seizoenid 
AND seizoen.seizoenid = $seizoenid AND race.raceid = $raceid 
GROUP BY coureur.coureurid ORDER BY tijd ASC

[pedorus]

Op deze manier is het ook wel lastig. Zou je de query eens kunnen herschrijven naar een versie die gebruik maakt van gewone JOINS? Dan is het verder een kwestie van bijvoorbeeld de juiste LEFT JOIN en WHERE ... IS NULL toevoegen naar analogie van het mysql-artikel. Iets als

SQL:

LEFT JOIN ronde AS ronde2 
    ON (ronde2.raceid = ronde.raceid AND ronde2.coureurid = ronde.coureurid 
        AND (ronde2.tijd < ronde.tijd OR 
            (ronde2.tijd = ronde.tijd AND ronde2.rondenummer < ronde.rondenummer))
WHERE ronde2.rondeid IS NULL

En zonder die GROUP BY.

[mauricedb]

Okee, naar mijn inziens zou dit dan de code moeten zijn met het gebruik van joins, maar deze code werkt niet

SQL:

SELECT voornaam, achternaam, teamnaam, rondenummer, 
tijd, racenaam, plaats, circuit, datum, seizoen 
FROM coureur, team, ronde, race, seizoen 
INNER JOIN ronde ON coureur.coureurid = ronde.coureurid
INNER JOIN teamcor ON coureur.coureurid = teamcor.coureurid 
INNER JOIN teamcor ON team.teamid = teamcor.teamid 
INNER JOIN ronde ON race.raceid = ronde.raceid 
INNER JOIN seizoen ON seizoen.seizoenid = race.seizoenid 
INNER JOIN teamcor ON seizoen.seizoenid = teamcor.seizoenid 
WHERE seizoen.seizoenid = $seizoenid AND race.raceid = $raceid

Laatste regel is om de id's uit de url te halen.

[YopY]

mauricedb schreef op zaterdag 17 december 2011 @ 00:10:
Okee, naar mijn inziens zou dit dan de code moeten zijn met het gebruik van joins, maar deze code werkt niet

SQL:

SELECT voornaam, achternaam, teamnaam, rondenummer, 
tijd, racenaam, plaats, circuit, datum, seizoen 
FROM coureur, team, ronde, race, seizoen 
INNER JOIN ronde ON coureur.coureurid = ronde.coureurid
INNER JOIN teamcor ON coureur.coureurid = teamcor.coureurid 
INNER JOIN teamcor ON team.teamid = teamcor.teamid 
INNER JOIN ronde ON race.raceid = ronde.raceid 
INNER JOIN seizoen ON seizoen.seizoenid = race.seizoenid 
INNER JOIN teamcor ON seizoen.seizoenid = teamcor.seizoenid 
WHERE seizoen.seizoenid = $seizoenid AND race.raceid = $raceid

Laatste regel is om de id's uit de url te halen.

Je doet ook drie keer een INNER JOIN op teamcor en ronde- weet niet of dat toegestaan is in MySQL joins. Probeer eens:

INNER JOIN teamcor ON (coureur.coureurid = teamcor.coureurid AND team.teamid = teamcor.teamid AND seizoen.seizoenid = teamcor.seizoenid)

zelfde met de dubbele join op ronde

[Voutloos]

Sorry, maar ga aub nog eens (oa) de mysql join en group by documentatie lezen, want je huidige query pogingen raken kant noch wal.

[mauricedb]

Ik wil er best tijd insteken (en dat heb ik ook al wel gedaan met de hele site) en ik snap het principe van die joins ook wel, maar het lukt mij gewoon niet. Heb de tip van YopY opgevolgd maar dan pakt die 'm ook niet. Als ik de query in PHPMyAdmin uitvoer geeft die ook met die joins de ene na de andere foutmelding, dat bijv van coureur.coureurid de tabel coureur niet uniek is of zoiets dergelijks. Zoals gezegd wil ik er best tijd insteken en van leren, maar nu zit ik gewoon al ''dagen'' te kloten en ik kom gewoon niet verder. Als ik nou vooruitgang boekte, dan was het nog leuk geweest, maar nu.. Heb ook al genoeg sites bezocht die de joins uitleggen, maar dan gaan ze (vaak) maar van twee tabellen uit waarbij één link moet worden gelegd of zoiets dergelijks.

[pedorus]

Tsja, no offence, maar dit lijkt gewoon nergens naar. Met twee tabellen staan er toch ook geen meerdere tabelnamen achter elkaar gescheiden door komma's? Of komt dezelfde tabelnaam twee keer of zelfs nog meer voor (zonder AS alias of voor een punt)?

De grap is trouwens dat je die nette JOINs eigenlijk niet nodig hebt, dat was alleen bedoelt om wat meer inzicht te krijgen. In principe kun je waarschijnlijk met eenvoudig knippen en plakken in de posts van 16 december 2011 22:?? een werkende query krijgen, die vervolgens onbegrijpelijk is.

[mauricedb]

pedorus schreef op zaterdag 17 december 2011 @ 19:10:
Tsja, no offence, maar dit lijkt gewoon nergens naar. Met twee tabellen staan er toch ook geen meerdere tabelnamen achter elkaar gescheiden door komma's? Of komt dezelfde tabelnaam twee keer of zelfs nog meer voor (zonder AS alias of voor een punt)?

Nee, omdat je dan inderdaad maar 2 tabellen hebt, en niet een stuk of 7
Ik zou met die JOINS coureurid uit tabel coureur moeten halen, maar ik moet ook als normale gegevens de voor- en achternaam uit tabel coureur halen. Dus daarom krijg ik (denk ik) die foutmelding dat het niet uniek is.

pedorus schreef op zaterdag 17 december 2011 @ 19:10:
De grap is trouwens dat je die nette JOINs eigenlijk niet nodig hebt, dat was alleen bedoelt om wat meer inzicht te krijgen. In principe kun je waarschijnlijk met eenvoudig knippen en plakken in de posts van 16 december 2011 22:?? een werkende query krijgen, die vervolgens onbegrijpelijk is.

Zolang die werkt en ik 'm (een beetje) snap, vind ik het best Maar hoe ik dat dan voor elkaar krijg... geen idee. Dan komen we eigenlijk terug bij de TS.

[Voutloos]

mauricedb schreef op zaterdag 17 december 2011 @ 23:23:
Dus daarom krijg ik (denk ik) die foutmelding dat het niet uniek is.

Ok, dan moet je ook nog ff lezen wat een alias is.

[mauricedb]

Okee, ik heb 'm nu met JOINS werkend in PHPMyAdmin (niet letten op de where, normaal haalt hij dit uit de url):

SQL:

SELECT coureur.coureurid, voornaam, achternaam, teamnaam, ronde2.rondenummer, 
ronde2.tijd, race.racenaam, race2.plaats, race2.circuit, race2.datum, seizoen 
FROM (coureur, ronde AS ronde2, team, race AS race2, seizoen) 
INNER JOIN ronde ON coureur.coureurid = ronde2.coureurid AND race2.raceid = ronde2.raceid 
INNER JOIN teamcor ON coureur.coureurid = teamcor.coureurid 
AND team.teamid = teamcor.teamid AND seizoen.seizoenid = teamcor.seizoenid 
INNER JOIN race ON seizoen.seizoenid = race.seizoenid 
WHERE race.raceid = 1 
GROUP BY coureur.coureurid 
ORDER BY ronde2.tijd ASC

Morgen ga ik wel verder met het uitproberen van pedorus z'n code in mijn query, kijken of ik dat voor elkaar krijg. In ieder geval tot zover bedankt voor het reageren op mijn posts

[mauricedb]

Ik krijg het niet voor elkaar met die JOINS. Het lukt me wel om de code van pedorus (licht aangepast) te implementeren, maar daarmee krijg ik geen ander resultaat (oftewel de tijden/rondenummers blijven hetzelfde als op de screen in de TS). Als iemand mij wil helpen met het aanpassen van onderstaande code waarmee ik het gewenste resultaat krijg, graag. Ik ga ieder geval geen tijd meer in die JOINS steken, of iemand moet daar de (kant-en-klare) oplossing voor hebben.

SQL:

SELECT coureur.coureurid, voornaam, achternaam, ronde.coureurid, teamcor.coureurid, 
team.teamid, teamnaam, teamcor.teamid, ronde.rondeid, rondenummer, tijd, race.raceid, 
racenaam, plaats, circuit, datum, ronde.raceid, race.seizoenid, seizoen.seizoenid, seizoen, teamcor.seizoenid  
FROM coureur, team, teamcor, race, seizoen, ronde  
WHERE ronde.coureurid = coureur.coureurid AND coureur.coureurid = teamcor.coureurid  
AND team.teamid = teamcor.teamid AND seizoen.seizoenid = race.seizoenid  
AND ronde.raceid = race.raceid AND seizoen.seizoenid = teamcor.seizoenid  
AND seizoen.seizoenid = $seizoenid AND race.raceid = $raceid  
GROUP BY coureur.coureurid 
ORDER BY tijd ASC

[pedorus]

Vreemd, het copy-pasten werkte niet? Ik geef toe, 11 delen was meer dan verwacht.

SQL:

SELECT coureur.coureurid, voornaam, achternaam, ronde.coureurid, 
teamcor.coureurid, team.teamid, teamnaam, teamcor.teamid, ronde.rondeid, 

ronde.

rondenummer, 

ronde.

tijd, race.raceid, racenaam, plaats, circuit, datum, ronde.raceid, 
race.seizoenid, seizoen.seizoenid, seizoen, teamcor.seizoenid 
FROM coureur, team, teamcor, race, seizoen, ronde 

LEFT JOIN ronde AS ronde2 
    ON (ronde2.raceid = ronde.raceid AND ronde2.coureurid = ronde.coureurid 
        AND (ronde2.tijd < ronde.tijd OR 
            (ronde2.tijd = ronde.tijd AND ronde2.rondenummer < ronde.rondenummer))
WHERE ronde2.rondeid IS NULL

AND

ronde.coureurid = coureur.coureurid AND coureur.coureurid = teamcor.coureurid 
AND team.teamid = teamcor.teamid AND seizoen.seizoenid = race.seizoenid 
AND ronde.raceid = race.raceid AND seizoen.seizoenid = teamcor.seizoenid 
AND seizoen.seizoenid = $seizoenid AND race.raceid = $raceid 

ORDER BY 

ronde.

tijd ASC

Dit is een erg lelijke manier van joinen, 2 verschillende join/types worden hier gecombineerd wat het lastig leesbaar maakt. Ook vraag ik me af waarom je id's meerdere keren laat terugkomen.

Een goede query met nette joins bevat geen tabelnamen gescheiden door komma´s, dus geen "coureur, ronde AS ronde2, team, race AS race2, seizoen" bijvoorbeeld. Dat zou dus iets als "coureur inner join ronde on ... inner join race on ... inner join seizoen on ... and ..." moeten zijn, en leer je als het goed is in een willekeurige sql-tutorial.

Nou ja, ehh, veel succes met het onderhoud van dit soort queries als je weigert moeite te steken in het leren maken van nette queries...