Datenbankabfrage - Top 10

[Uwe_B]

Hallo,
gegeben ist folgende Datenbankabfrage:

$q = "SELECT   `username`, `score`, `time`
      FROM     `highscores`
      WHERE    `nid` = '" . mysql_real_escape_string($node->nid) . "'
      ORDER BY `score` DESC
      LIMIT    0,10";

Ausgegeben wird jeweils der Username, seine Punktezahl und das Datum, beginnend mit der höchsten Punktezahl. Soweit so richtig, leider werden die User so mehrfach ausgegeben. Ich würde gerne erreichen, dass jeder User nur einmal in der Liste auftaucht und zwar nur mit seiner höchsten Punktzahl.

Da die Liste ja ohnehin mit der höchsten Punktzahl beginnt, sollte es am einfachsten sein, wenn Zeilen mit Usern die bereits abgefragt wurden einfach übersprungen werden, bis die Top-10 voll ist. Oder was meint ihr?

Und wenn das der beste Weg ist, wie ist dann die Lösung?

Gruß,
Uwe

 

[Yaslaw]

Mit einem Subquery in dem du die Maximale Punktzahl pro user ermittelst

Etwa in der Art

SELECT
	h.username, h.score, h.time
FROM
	highscores h,
	(SELECT username, MAX(score) AS max_score
	FROM highscores
	WHERE nid = {$nid}
	GROUP BY username) AS mpu
WHERE
	h.username = mpu.username
	AND h.score = mpu.max_score
ORDER BY score DESC
LIMIT 10

 

[Uwe_B]

Danke für die Antwort, aber habs gerade rausbekommen. Einfach

GROUP BY `username`

for ORDER BY setzen, na das war ja mal erfrischend einfach.

 

[RudolfG]

Danke für die Antwort, aber habs gerade rausbekommen. Einfach

GROUP BY `username`

for ORDER BY setzen, na das war ja mal erfrischend einfach.

Das dürfte aber nicht das selbe Ergebnis ermitteln. Bei der GROUP BY-Lösung werden die highscores eines Benutzers addiert und dann jeder Benutzer nur einmal angezeigt. Bei der Lösung von yaslaw verhält sich das gegen wie gewünscht!

Gruß
RudolfG

 

[Uwe_B]

Ups, ihr habt recht, da hatte ich mich wohl zu früh gefreut. Dann nochmals danke, ich werde das mal ausprobieren.

EDIT: Der Code funktioniert genaus so 1zu1 perfekt, Danke !

 

[Uwe_B]

Ich habe da nochmal eine Frage. Ich würde das Feld username gerne aus der highscores-Tabelle entfernen, da die Daten ja schon in der Tabelle users->name der Drupal-Installation vorhanden sind. Die Abfrage würde ich also dementsprechend ändern und stattdessen die userid abfragen:

$q = "SELECT
    h.userid, h.score, h.time
FROM
    highscores h,
    (SELECT userid, MAX(score) AS max_score
    FROM highscores
    WHERE nid = {$nid}
    GROUP BY userid) AS mpu
WHERE
    h.userid = mpu.userid
    AND h.score = mpu.max_score
ORDER BY score DESC
LIMIT 10";

Habs getestet, klappt natürlich auch. Jetzt werden die UserIDs ausgegeben, die Ausgabe sieht momentan so aus:

$res = mysql_query($q);
if (mysql_num_rows($res) > 0) {
echo "<table><tr><th class='platz'>#</th><th class='spieler'>Spieler</th><th class='punkte'>Punkte</th><th class='datum'>Datum</th>";
    $i = 1;
    while ($row = mysql_fetch_assoc($res)) {
	echo "<tr>";
    echo "<td class='platz'>".$i.".</td><td class='spieler'><a href='/user/".$row['userid']."' title='".$row['userid']."'>".$row['userid']."</a></td><td class='punkte'>".$row['score']."</td><td class='datum'>".$row['time']."</td>";
	echo "</tr>";
        $i++;
    }
echo "</tr></table>";
} else {
    echo 'keine Highscores...';
}  ?>

Wie kann ich die userid nun durch den Namen ersetzen, der aus der Tabelle users Feld name anhand der userid (ebenfalls ein Feld in der users-Tabelle) ermittelt werden kann? Kann ich das direkt über die Ausgabe steuern oder muss da die Abfrage erweitert werden?

Gruß,
Uwe

 

[Biergamasda]

geht beides, ich würds über einen join im SQL lösen

kleines bsp

select username from highscores join usernames on highscores.id = usernames.id

das baust einfach in die Abfrage bei dir ein

//edit
hab grade gesehen dass das bei dir etwas anders läuft

Schematisch läufts wie folgt ab

select u.username ... from highscores h, usernames u, (select....
where ... and h.userid = u.userid...

 

[Uwe_B]

Es funktioniert, danke!

Falls jemand eine ähnliche Abfrage benötigen sollte, hier nochmal der komplette Code:

SELECT
    h.userid, h.score, h.time, u.name
FROM
    highscores h, users u,
    (SELECT userid, MAX(score) AS max_score
    FROM highscores
    WHERE nid = {$nid}
    GROUP BY userid) AS mpu
WHERE
    h.userid = mpu.userid
	AND h.score = mpu.max_score
	AND u.uid = h.userid
ORDER BY score DESC
LIMIT 10

Gruß,
Uwe

 

[Uwe_B]

Es ist nun ein weiteres, kleines Problem aufgetaucht:

Wenn ein Spieler mehrmals eine identische Punktzahl als Maximalscore erreicht hat, wird er mehrmals in der Liste geführt. Wie kann ich erreichen, dass die maximale Punktzahl des Users nur einmal angezeigt wird und zwar nur als der Datensatz, der den älteren Timestamp hat (Feld "time" in highscores).

Gruß,
Uwe

 

[Biergamasda]

Bin mir jetzt nicht sicher ob das funktioniert, aber spontan würde mir folgendes einfallen.

Im subquery:

select distinct...

(damit bekommst du keien doppelten einträge mehr raus)

Im normalen query:

select ...min(h.time)...

Wie gesagt, bin mir nicht sicher ob das hinhaut, nen versuch wärs aber mal Wert

lg