ermitteln des Server besten aus zwei Tabellen?

xpkiller · 14 Februar 2012

Hallo hier im Forum,
sql ist noch ziemlich neu für mich und ich tue mich da etwas schwer. Ich habe schon einiges realisieren können, doch nun komme ich nicht weiter. Ich habe eine SQL - Datenbank mit 7 Tabellen (challenges, transaction, players, players_cache, records, teams, und votes). Ich möchte nun mit php den Server besten aus zwei Tabellen (players und records) ermitteln. Die Tabelle players hat 5 Spalten ( Id, Login, NickName, UpdatedAt, Wins, TimePlayed, TeamId) die Tabelle records hat 6 spalten (Id, challengeId, PlayerId, Score, CheckPoints, Date).
Ich habe folgendes gemacht:

PHP:

     //  $db = Datenbankverbindung ist hergestellt
 
 
      $SQL_Aabfrage = $db->query("SELECT T1.*, T2.* FROM players T1, records T2
                                  ORDER BY T2.PlayerId DESC LIMIT 1");
      $SQL_Aabfrage->setFetchMode(PDO::FETCH_ASSOC);
 
      $Ergebnis = $SQL_Aabfrage->fetchAll();
 
      if(count($Ergebnis) > 0) {
      foreach($Ergebnis as $Daten) {
          $Anzeige1 = $Daten['NickName'];
          $Anzeige2 = $Daten['Wins'];
          $Anzeige3 = $Daten['PlayerId'];
        }
 
        }
 
    // die Ergebnisse werden dann mit $Anzeige1, $Anzeige2 und $Anzeige3 ausgegeben.

Doch irgend wie stimmt das alles nicht so ganz, es wird der NickName, Wins und PlayerId von records angezeigt, aber die Ergebnisse stimmen nicht. Irgendwas mache ich doch da falsch?
Kann mir da jemand weiter helfen?
Gruß xpkiller

ukulele · 14 Februar 2012

Wie würde sich denn der "Server Beste" ermitteln? Derzeit sortierst du ja nach PlayerID, was irgendwie sinnfrei wirkt. Ich denke mal jeder Player hat mehrere Records von denen einige oder alle in die Bewertung einfließen. Wie das aber von statten gehen soll fehlt in deinem Code. Es gibt lediglich die Spalte Wins die aber bereits in Players steht, ist dort schon der ermittelte Wert drin?

xpkiller · 14 Februar 2012

Ja, ich weiß, das ist nicht so wie es sein soll. Ich habe mich aber auch vielleicht etwas falsch ausgedrückt. ich möchte den Server besten ermitteln, anhand der gefahrenen, bzw. gewonnenen Rennen (Wins), den Namen (NickName) und der gefahrenen Rekorde in der Tabelle "records", anhand der PlayerId für den "NickName aus der Tabelle players"
Leider weiß ich nicht, wie ich das realisieren kann, ich komme da nicht weiter. Müssen die (Score) und (challengeId) aus der Tabelle "records" auch irgendwie mit eingeschlossen werden?

Code:

TABELLE `players` (
  `Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
  `Login` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `NickName` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `UpdatedAt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Wins` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `TimePlayed` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `TeamId` mediumint(9) unsigned DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Login` (`Login`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Code:

TABELLE `records` (
  `Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
  `ChallengeId` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `PlayerId` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `Score` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `CheckPoints` text COLLATE utf8_unicode_ci,
  `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ChallengeId` (`ChallengeId`,`PlayerId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Vielleicht hilft das ein bisschen weiter?
Gruß xpkiller

ukulele · 15 Februar 2012

Ok ich verstehe dich besser. Ich kann dir mal den Code für die Ermittlung des Besten nach "Wins" geben, der ist einfach.

Code:

SELECT    p.NickName,
        p.Wins,
        p.PlayerID
FROM    players p,
        records r
WHERE    p.PlayerID = r.PlayerID
ORDER BY p.Wins DESC
LIMIT 1

In der Records Tabelle bin ich mir nicht sicher, wie die Daten aussehen. Ich vermute mal für jede ChallangeID gibt es mehrere Einträge und der höchste ist der Rekord. Ohne Testdaten kann ich das nur so versuchen, eventuell musst du noch das ein oder andere ändern:

Code:

SELECT    p.NickName,
        max(r.Score) AS Score,
        r.ChalangeID,
        p.PlayerID
FROM    players p,
        records r
WHERE    p.PlayerID = r.PlayerID
GROUP BY r.ChalangeID,p.PlayerID,p.NickName
HAVING max(r.Score)

ukulele · 15 Februar 2012

Für Wins musst du natürlich nur die Players Tabelle abfragen, der Join auf Records ist eigentlich unnötig. Später kann man das aber mit Score kombinieren, wenn der Code funktioniert.

xpkiller · 15 Februar 2012

Vielen Dank ukulele, das hat mir sehr geholfen. Ich habs jetzt hinbekommen,

Code:

"SELECT a.Score, b.NickName, b.Wins,
(SELECT COUNT(Id) FROM records WHERE PlayerId = a.Id)+0 AS Records
FROM records AS a
LEFT JOIN players AS b
ON a.PlayerId = b.Id
WHERE a.ChallengeId = b.Id
ORDER BY a.Score , a.Date, a.PlayerId
LIMIT 0,10"

Auf Join konnte ich aber nicht verzichten, so funktioniert es bei mir einwandfrei.
Gruß xpkiller

EDIT am 06.03.2012:
Ich habe mit den Einstellungen noch etwas experimentiert und habe nun den Code nochmals geändert,so wird es jetzt richtig angezeigt:

PHP:

      $SQL_Aabfrage = $db->query("SELECT p.Wins, p.NickName, max(r.Score) AS Score, r.ChallengeId,
                                  (SELECT COUNT(Id) FROM _records r WHERE PlayerId = p.Id)+0 AS Records
                                  FROM _players p, _records r
                                  WHERE p.Id = r.PlayerId
                                  GROUP BY r.ChallengeId,p.Id,p.NickName
                                  HAVING max(r.Score)");
                         
     
 
 
 
      $SQL_Aabfrage->setFetchMode(PDO::FETCH_ASSOC);
 
      $Ergebnis = $SQL_Aabfrage->fetchAll();
 
      if(count($Ergebnis) > 0) {
      foreach($Ergebnis as $Daten) {
          $Anzeige1 = $Daten['NickName'];
          $Anzeige2 = $Daten['Wins'];
          $Anzeige3 = $Daten['PlayerId'];
          $Anzeige4 = $Daten['Records'];
        }
 
        }

xpkiller · 6 März 2012

Nun habe ich noch ein kleines Problem, nach dem es gelungenen ist den Server Besten jetzt richtig an zu zeigen, wollte ich schlau sein und mal eben die beste Challenge auf dem Server mit den meisten Bewertungen und Stimmen anzeigen.
Ich habe wieder zwei Tabellen, aus der gelesen wird (_challenges und _votes). Die Tabellen sehen wie folgt aus:
_challenges` (
`Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
`Uid` varchar(27) COLLATE utf8_unicode_ci NOT NULL,
`Name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`Author` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`Environment` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`GoodRating` mediumint(9) unsigned DEFAULT '0',
`BadRating` mediumint(9) unsigned DEFAULT '0',
PRIMARY KEY (`Id`),
UNIQUE KEY `Uid` (`Uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

_votes` (
`Id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
`PlayerId` mediumint(9) unsigned NOT NULL,
`ChallengeId` mediumint(9) unsigned NOT NULL,
`Score` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`),
UNIQUE KEY `Vote` (`PlayerId`,`ChallengeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Dazu habe ich diesen Code eingegeben, es wird auch etwas angezeigt, nur entspricht es nicht der Wahrheit?

PHP:

      $SQL_Aabfrage = $db->query("SELECT p.Environment, p.Name, SUM(r.Score) AS Score, COUNT(r.Score) AS Count, r.ChallengeId, p.Uid
                                  FROM _challenges p, _votes r
                                  WHERE p.Uid = r.PlayerId
                                  GROUP BY r.PlayerId, r.Id, p.Uid, p.Name
                                  HAVING SUM(r.Score)");
 
 
 
 
      $SQL_Aabfrage->setFetchMode(PDO::FETCH_ASSOC);
 
      $Ergebnis = $SQL_Aabfrage->fetchAll();
 
      if(count($Ergebnis) > 0) {
      foreach($Ergebnis as $Daten) {
          $Anzeige1 = $Daten['Name'];
          $Anzeige2 = $Daten['Count'];
          $Anzeige3 = $Daten['Environment'];
          $Anzeige4 = $Daten['Score'];
        }
 
        }

Irgend wo habe ich da wohl einen Denkfehler?
Ich habe auch schon mit den Daten gespielt, aber ich komme nicht drauf?
Es soll die Challenge (p.Name) mit den meisten Bewertungen (r.PlayerId) und erhaltenen Stimmen (r.Score) angezeigt werden.

Gruß xpkiller

ukulele · 6 März 2012

Also ohne mich jetzt mit der Struktur befasst zu haben spuckt mir mein SQL Editor schonmal Fehler aus bei deinem Code:

HAVING sum(r.score) ist kein Boolean Wert, max(r.score) wie im Beispiel davor möglicherweise schon weil es sich vermutlich auf das Maximum der Tabelle bezieht. Du musst irgendetwas angeben das wahr oder falsch ist.

Alternativ kann es noch an Spaltennamen wie "Count" liegen, die eventuell als Funktion interpretiert werden. Abhilfe schafft das Klammern solcher Namen als [Count].

Häufige Ursache von zunächst blödsinnig wirkenden Ergebnissen sind dann noch die NULL Werte, wenn z.B. r.PlayerID oder Uid irgendwo mal NULL sind.

xpkiller · 6 März 2012

Ich habe noch mal einiges probiert, aber ich bringe es nur zusammen, das jetzt etwas angezeigt wird was zusammen passt. Doch es ist keineswegs die Challenge mit den meisten Bewertungen. Hier noch mal mein jetziger Code:

PHP:

SELECT p.Uid, p.Id, p.Environment, p.Name, MAX(r.Score) AS Score, MAX(r.PlayerId) AS Count
                                  FROM _challenges AS p, _votes AS r
                                  WHERE r.ChallengeId = p.Id
                                  GROUP BY r.ChallengeId, r.PlayerId, p.Id, p.Uid, p.Name
                                  HAVING MAX(r.Score)

Irgend wie ist doch da der Wurm drin?
Wie ermittle ich nur die Challenge mit der höchsten Bewertung ?
Gruß xpkiller

ukulele · 7 März 2012

Wie genau ist das denn aufgebaut, du ermittelst immer nur einen Spieler oder die Top 10? Es bietet sich ja an dort mit LIMIT und ORDER BY zu arbeiten aber ich blicke nicht durch die Tabellenstruktur.

Code:

SELECT    p.[Uid],
        p.Id,
        p.Environment,
        p.Name,
        sum(r.Score) AS Score
FROM    challenges p,
        _votes r
WHERE    r.ChallengeId = p.Id
GROUP BY r.ChallengeId, r.PlayerId, p.Id, p.[Uid], p.Name, p.Environment
ORDER BY sum(r.Score)
LIMIT 1

.. das kann aber auch Blödsinn sein. Ein ERD mit allen Tabellen und Relationen ist manchmal hilfreich

xpkiller · 8 März 2012

ukulele schrieb:
Wie genau ist das denn aufgebaut, du ermittelst immer nur einen Spieler oder die Top 10? Es bietet sich ja an dort mit LIMIT und ORDER BY zu arbeiten aber ich blicke nicht durch die Tabellenstruktur.
hilfreich

Ich will mal versuchen,das zu erklären. In der Tabelle _votes sind folgendeSpalten;
1: Id= Vortlaufende Nummerierung,
2: PlayerId = Hier sind nur die NR, der Player(aus der Tabelle _players) aufgelistet, die eine Bewertung abgegeben haben,
3: ChallengeId = Hier wir die ChallengeId = Nummerierung der Challenges aus der Tabelle _Challenges
4: Score = Hier werden die abgegebenen Stimmen der Spieler abgelegt(1-5)

Ich habe jetzt in der Tabelle _votes noch eine Spalte hinzugefügt:
5: ChallengeName = Hier wird nun der Name der Challenge abgelegt die bewertet wurde
Für jeden Spieler, der eine Bewertung abgegeben hat ist nun eine Spalte mit:
Id, PlayerID, ChallengeID, ChallengeName; Score vorhanden, dann brauch nur aus einer Tabelle gelesen werden.
Nun möchte ich die am besten Bewertete(PlayerId) und mit den meisten Stimmen(Score) Challenge mit dem ChallengeNamen anzeigen lassen. Sie muss natürlich auch ,wenn sich etwas an der Bewertung ändert, die aktuell beste dann anzeigen.
Das soll dann zum Beispiel so aussehen:

Server beste Challenge:
Challenge: ChallengeName
Bewertungen: 3(von 3Spielern wurde diese Challenge Bewertet) mit 8 Stimmen(Player1=2, Player2=5, Player3=1)= (Summe der Punke von Score für diese Challenge)
Environment:

Ich hoffe das ist so etwas verständlicher?
Gruß xpkiller

ukulele · 12 März 2012

Code:

SELECT    c.ChallangeName,
        p.Name,
        count(*) AS [Anzahl Bewertungen],
        sum(v.Score) AS [Gesamtpunkte],
        avg(v.Score) AS [Durchschnitt]
FROM    players p,
        challenges c,
        _votes v
WHERE    v.PlayerId = p.Id
AND        v.ChallangeId = c.Id
GROUP BY c.ChallangeName,p.Name
ORDER BY sum(v.Score) DESC
LIMIT 1

xpkiller · 12 März 2012

Hallo ukulele und vielen Dank, für deine Bemühung, es wird mit diesem Code zwar auch eine Challenge mit Bewertung und Punkte angezeigt, aber das ist auch nicht die am besten bewertete?

Code:

SELECT p.NickName, count(*) AS Count,
                                  sum(v.Score) AS Score,
                                  avg(v.Score) AS Score1,
                                  c.Name As ChallengeName
                                  FROM _players p,
                                        _challenges c,
                                        _votes v
                                  WHERE v.PlayerId = p.Id
                                  AND  v.ChallengeId = c.Id
                                  GROUP BY c.Name,p.NickName
                                  ORDER BY sum(v.Score) DESC
                                  LIMIT 1

Ich habe auch schon mit den Werten gespielt, aber ich bringe es auch nicht dazu, die beste anzuzeigen?
Vielleicht muss ich an meiner Tabellenstruktur was ändern?

ukulele · 12 März 2012

Dann vieleicht ORDER BY avg(v.Score) oder was ist für dich "die beste" Bewertung? Teste doch mal die Ausgabe ohne LIMIT und verändere die Sortierung.

Was willst du an der Tabellenstruktur ändern und warum?

xpkiller · 12 März 2012

Hi ukulele, ich habe noch mals einige Dateibeispiele durchgeführt und ich glaube ich habs jetzt hin bekommen.

Ich habe mit dem Code von dir ein bischen rumgespielt und nun wird die beste Challenge des Servers angezeigt:

Code:

SELECT v.ChallengeId, count(*) AS Count,
                                  sum(v.Score) AS Score,
                                  avg(v.Score) AS Score1,
                                  c.Name As ChallengeName,
                                  c.Environment
                                  FROM _players p,
                                        _challenges c,
                                        _votes v
                                  WHERE v.PlayerId = p.Id
                                  AND  v.ChallengeId = c.Id
                                  GROUP BY c.Name,v.ChallengeId
                                  ORDER BY sum(v.Score) DESC
                                  LIMIT 1

Mann oh Mann, das hat aber Geduld gekostet. Ich danke dir für deine Hilfe

Gruß xpkiller

ermitteln des Server besten aus zwei Tabellen?

xpkiller

Benutzer

ukulele

Datenbank-Guru

xpkiller

Benutzer

ukulele

Datenbank-Guru

ukulele

Datenbank-Guru

xpkiller

Benutzer

xpkiller

Benutzer

ukulele

Datenbank-Guru

xpkiller

Benutzer

ukulele

Datenbank-Guru

xpkiller

Benutzer

ukulele

Datenbank-Guru

xpkiller

Benutzer

ukulele

Datenbank-Guru

xpkiller

Benutzer