[PHP/MySQL] Kruistabel maken.

[ripexx]

Ik zit met het volgende probleem. Voor een CMS wat ik aan het bouwen ben wil ik een overzicht maken wanneer een bepaalde scheidsrechter moet fluiten. Even de relevate tabellen:

User	Team	Wedstrijd
uid	tid	wid
naam	naam	tid
	afkt	datum
		tijd
		s_uid_1
		s_uid_2

Verklaring: elke wedstrijd heeft 2 scheidsrechters (zijn de regels ) s_uid_1 is de uid van scheidsrechter 1 enz.

Het volgende resultaat zou ik willen bewerkstelligen:

code:

+-----------------+------+------+-------+------+
|                 |  1-9 |  8-9 |  15-9 | 22-9 | 
+-----------------+------+------+-------+------+
| Scheidrechter A |  D1  |  H1  |  H3   |      |
+-----------------+------+------+-------+------+
| Scheidrechter B |  H1  |  H1  |       |  D2  |
+-----------------+------+------+-------+------+
| Scheidrechter C |      |  D1  |  H3   |      |
+-----------------+------+------+-------+------+
| Scheidrechter D |      |      |  D2   |      |
+-----------------+------+------+-------+------+
| Scheidrechter E |  H2  |  H2  |       |  D3  |
+-----------------+------+------+-------+------+
| Scheidrechter F |  D2  |  D3  |       |  D2  |
+-----------------+------+------+-------+------+

Hierbij is dus het aantal data en user onbeperkt (afhankelijk van selectie).

Een aantal ideeen heb ik al bedacht maar deze zijn niet echt praktisch te doen. Na deel is dat je snel uit komt bij aantal rijen + 1 query's. Dit is bij 5 of 10 scheidsrechter geen probleem maar op er zijn bij ons ongeveer 100-150 scheidsrechters. Waardoor het niet altijd even handig. Wie kent dit probleem en heeft er een oplossing voor. Ben zelf niet zo'n held in (my)sql en kan zelf dan ook geen oplossing bedenken waarbij een query het zelfde resultaat heeft.

[whoami]

Data ophalen mbhv SQL, data presenteren (dus de lay-out verzorgen) doe je dan met PHP.
SQL is er niet om je data te formatten.

[Verwijderd]

Ik weet dat je in JetSQL (die oa in ms access gebruikt word) met de statemant "PIVOT" een kruistabel kan maken. Maar ik weet niet of dit ook werkt ni MySQL, ik vrees van niet .

[justmental]

In je SQL moet je zoiets realiseren:

code:

Scheidrechter A  1-9  D1
Scheidrechter A  8-9  H1
Scheidrechter A  15-9 H3
Scheidrechter B  1-9  H1
Scheidrechter B  8-9  H1
...

Daar schrijf je procedurele code omheen om de layout te realiseren.
Als de scheidsrechtersnaam ongelijk is aan de vorige scheidsrechtersnaam, dan volgende regel etc.

[ripexx]

whoami schreef op 07 August 2003 @ 22:23:
Data ophalen mbhv SQL, data presenteren (dus de lay-out verzorgen) doe je dan met PHP.
SQL is er niet om je data te formatten.

Oke dat is duidelijk. Zal eens kijken of ik wat met array's lan gaan stoeien in PHP. Zal wel neer komen op veel vuldig sorteren

Thx, had eigenlijk gehoopt dat dit via een moeie query kon, maar helaas.

Verwijderd schreef op 07 August 2003 @ 22:23:
Ik weet dat je in JetSQL (die oa in ms access gebruikt word) met de statemant "PIVOT" een kruistabel kan maken. Maar ik weet niet of dit ook werkt ni MySQL, ik vrees van niet .

Nee deze komt zover ik weet niet in het mysql manual voor dus zal wel niet, ergens een keer gelezen dat dit commando alleen in access voorkomt en niet eens in MSSQL.

justmental schreef op 07 augustus 2003 @ 22:27:
In je SQL moet je zoiets realiseren:

code:

Scheidrechter A  1-9  D1
Scheidrechter A  8-9  H1
Scheidrechter A  15-9 H3
Scheidrechter B  1-9  H1
Scheidrechter B  8-9  H1
...

Daar schrijf je procedurele code omheen om de layout te realiseren.
Als de scheidsrechtersnaam ongelijk is aan de vorige scheidsrechtersnaam, dan volgende regel etc.

Dit is via een join te doen en dan eerst een order op scheidsrechter naam en dan op datum. Zal eens gaan kijken hoe ik dit kan verwerken in PHP. Thx voor de tip

[ Voor 27% gewijzigd door ripexx op 07-08-2003 22:30 ]

[kvdveer]

Je zult dit deels in php op moeten lossen vrees ik. Zoiets?

PHP:

$query = "SELECT * FROM wedstrijd ORDER BY datum";
while($row = mysql_fetch_object(...)) {
  $scheids[$row->scheids][$row->datum] = $row->team;
  $datum[] = $row->datum;
}
echo "<TABLE><TR><TH />";
foreach($datum as $dag)  {
  echo "<TH>" . date(...,$dag) . "</TH>"
}
echo "</TR>"
foreach($scheids as $sc) {
   foreach($datum as $dag)  {
     if(isset($sch[$dag]) echo "<TD>$sch[$dag]</TD>"
     else echo "<TD />";
   }

}

[ripexx]

Thx, ik zal er eens naar kijken, maar met dit weer zit ik zelfs te zweten terwijl ik dit tik.

[kvdveer]

ripexx schreef op 07 August 2003 @ 22:49:
Thx, ik zal er eens naar kijken, maar met dit weer zit ik zelfs te zweten terwijl ik dit tik.

Wees voorzichtig: zout water kan kortsluiting veroorzaken.

[ripexx]

kvdveer schreef op 07 August 2003 @ 22:50:
[...]

Wees voorzichtig: zout water kan kortsluiting veroorzaken.

Zal ik zeker doen Nu eerst ff film kijken om af te koelen, zodra ik er uit ben of zinvolle code heb zal ik het wel ff posten. Thx.

[ripexx]

Oke ben nu aan het prutsen met de query maar ik krijg maar een rij per wedstrijd terug, terwijl ik er dus twee nodig heb, dit omdat er twee scheidsrechters zijn.

code:

SELECT
  team.afk,
  wedstrijd.datum,
  leden.achternaam, 
  wedstrijd.scheids1,
  wedstrijd.scheids2 
FROM 
  leden 
    LEFT JOIN wedstrijd ON leden.lid_id = wedstrijd.scheids1 
      LEFT JOIN team ON wedstrijd.team_id = team.team_id 
WHERE
  wedstrijd.waar = "T" LIMIT 0, 50

De uitkomst is ongeveer zo iets:

NU
team	datum	achternaam	scheids 1	scheids 2
D1	2004-09-01	user 1	1	2
D3	2004-09-01	user 6	6	8
H1	2004-09-01	user 12	12	11
GW
team	datum	achternaam	scheids 1	scheids 2
D1	2004-09-01	user 1	1	2
D1	2004-09-01	user 2	1	2
D3	2004-09-01	user 6	6	8
D3	2004-09-01	user 8	6	8
D1	2004-09-01	user 12	12	11
H1	2004-09-01	user 11	12	11

Wie heeft een idee hoe ik dat voor elkaar krijg of is mijn db ontwerp zo fout.

[Janoz]

Je zou een OR in je join conditie kunnen stoppen.

[kvdveer]

Janoz schreef op 08 August 2003 @ 09:08:
Je zou een OR in je join conditie kunnen stoppen.

Of je dat wilt is afhankelijk van de vraag: Wat wil je als een wedstrijd twee maal dezelfde scheids heeft? Wil je dan twee keer dat record (dan geen or, maar een union gebruiken) of wil je dat record een keer (dan wel een or gebruiken). Als het je om het even is, gebruik dan een OR.

Je query wordt dan:

SQL:

SELECT  
   team.afk,
   wedstrijd.datum,
   leden.achternaam,
   wedstrijd.scheids1,
   wedstrijd.scheids2
FROM
   leden
   LEFT JOIN wedstrijd 
      ON (leden.lid_id = wedstrijd.scheids1 OR leden.lid_id = wedstrijd.scheids2) 
   LEFT JOIN team ON wedstrijd.team_id = team.team_id 
WHERE
   wedstrijd.waar = "T" LIMIT 0, 50

--edit--
Extra toelichting
Hoe pakt een RDBMS een JOIN aan? Nu, dat gaat als volgt. Hij maakt intern een hele grote tabel aan die _ALLE_ combinaties van de twee opgegeven tabellen bevat. Bij een FULL JOIN worden ook rijen toegevoegd waar de velden van de ene tabel NULL zijn en de andere tabel ingevuld. Left en Right joins werken ook ongeveer zo. Dat zijn in totaal dus ongeveer N_tabel1 * N_tabel2 rijen, bij een FULL JOIN is dat (N_tabel1+1) * (N_tabel2+1).
Vervolgens wordt je JOIN-conditie naar de WHERE verplaatst, waarna de rijen gefilterd worden.
Ik sla voor de eenvoud even alle optimalisaties over, in werkelijkheid gaat dit STUKKEN slimmer er vindt ook wat pre-filtering plaats en zo... Waar het om gaat is: Je kunt je JOIN conditie gewoon invullen als een WHERE conditie invullen. Ik heb hier zelf query's draaien waar ik een less-than gebruik of berekeningen.

[ Voor 37% gewijzigd door kvdveer op 08-08-2003 11:04 ]

[ripexx]

Damm moet toch echt wat aan mijn SQL gaan doen.

kvdveer schreef op 08 augustus 2003 @ 10:49:
Of je dat wilt is afhankelijk van de vraag: Wat wil je als een wedstrijd twee maal dezelfde scheids heeft? Wil je dan twee keer dat record (dan geen or, maar een union gebruiken) of wil je dat record een keer (dan wel een or gebruiken). Als het je om het even is, gebruik dan een OR.

In princiepe mag een scheidsrechter niet alleen een wedstrijd fluiten dus kan dit niet voor komen. Wordt door de applicatie afgevangen namelijk. Er zal een lijst van beschikbare scheidrechter worden gemaakt, men kan dan dus ook niet dubbel worden ingedeeld. Maar dit zit in de applicatie en is redelijk fool proof.

Je query werkt trouwens uitstekend, was zelf al bezig maar gebruikte net de verkeerde combi van () en OR op het verkeerde moment Zal zo dadelijk eens kijken of ik het met PHP in een mooie tabel kan gieten.
//edit
Thx voor de extra toelichting maakt het net even duidelijker Ook zie ik nu dat ik problemen waar ik vroeger tegen aanliep veel beter door de db had kunnen laten oplossen dan door de applicatie. SQL is gemakelijk om zo even leren, eenvoudige selects, updates etc zijn zo gemaakt maar om het echt goed te begrijpen en er slim mee om te gaan is toch een vak apart. * ripexx slaps himself for underestimating SQL

[ Voor 19% gewijzigd door ripexx op 08-08-2003 11:14 ]