tabel uit database ordenen
wie kan me helpen dit op te lossen.
Ik heb gebruikers die op verschillende datums punten krijgen. Niet elke gebruiker krijgt op elke datum punten
Hoe krijg ik dit met PHP? Ik heb het nu als volgt, waardoor de 2 punten van Jan op 13-01 automatisch naar Piet gaan bij uitvoer van de code:
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
34
35
36
37
38
39
40
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
34
35
36
37
38
39
40
<?php
$query_3= mysql_query("SELECT * FROM scores GROUP BY user ORDER BY user") or die(mysql_error());
echo "<table border='1'>";
echo "<tr><td> </td>";
while($row_3 = mysql_fetch_array($query_3)){
$_ENV['user'] = $row_3['user'];
echo "<td>". $_ENV['user'] . "</td>";
}
echo "</tr>";
$query_4= mysql_query("SELECT * FROM scores GROUP BY datum") or die(mysql_error());
while($row_4 = mysql_fetch_array($query_4)){
echo "<tr>";
echo "<td>". $row_4['datum']. "</td>";
$query_5 = mysql_query("SELECT * FROM scores WHERE datum = '".$row_4['datum']. "' ORDER BY user") or die(mysql_error());
while($row_5 = mysql_fetch_array($query_5)){
echo "<td>". $row_5['score']. "</td>";
}
echo "</tr>";
}
echo "</table>";
?>
$query_3= mysql_query("SELECT * FROM scores GROUP BY user ORDER BY user") or die(mysql_error());
echo "<table border='1'>";
echo "<tr><td> </td>";
while($row_3 = mysql_fetch_array($query_3)){
$_ENV['user'] = $row_3['user'];
echo "<td>". $_ENV['user'] . "</td>";
}
echo "</tr>";
$query_4= mysql_query("SELECT * FROM scores GROUP BY datum") or die(mysql_error());
while($row_4 = mysql_fetch_array($query_4)){
echo "<tr>";
echo "<td>". $row_4['datum']. "</td>";
$query_5 = mysql_query("SELECT * FROM scores WHERE datum = '".$row_4['datum']. "' ORDER BY user") or die(mysql_error());
while($row_5 = mysql_fetch_array($query_5)){
echo "<td>". $row_5['score']. "</td>";
}
echo "</tr>";
}
echo "</table>";
?>
Gewijzigd op 12/01/2013 01:13:34 door Jochanan V
Code (php)
1
2
3
4
5
2
3
4
5
SELECT ud.datum, ud.user, COALESCE(us.score, 0) AS score
FROM
(SELECT DISTINCT d.datum, u.user FROM score AS d, score AS u) AS ud
LEFT JOIN score AS us ON ud.user = us.score AND ud.datum = us.datum
ORDER BY ud.datum DESC, ud.user
FROM
(SELECT DISTINCT d.datum, u.user FROM score AS d, score AS u) AS ud
LEFT JOIN score AS us ON ud.user = us.score AND ud.datum = us.datum
ORDER BY ud.datum DESC, ud.user
Resultaat:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
2013-01-13 Jan 2
2013-01-13 Kees 0
2013-01-13 Piet 0
2013-01-12 Jan 9
2013-01-12 Kees 6
2013-01-12 Piet 8
2013-01-11 Jan 7
2013-01-11 Kees 3
2013-01-11 Piet 5
2013-01-13 Kees 0
2013-01-13 Piet 0
2013-01-12 Jan 9
2013-01-12 Kees 6
2013-01-12 Piet 8
2013-01-11 Jan 7
2013-01-11 Kees 3
2013-01-11 Piet 5
Dit resultaat kan je gebruiken om de tabel te gegeneren