[MySQL] Problem mit COUNT

[3_of_8]

Morgen.

Also, ich hab folgendes Problem:

Ich habe zwei Tabellen, users und ships. Ein Benutzer kann mehrere Schiffe haben. User und Ships haben jeweils eine id (mit autoincrement, ist primary). Ich will jetzt mit UPDATE oder so ähnlich bei jedem User den Wert des Feldes fleet eines Users auf die Anzahl der Schiffe setzen, die dem User gehören (also bei denen die ownerid gleich der userid des Users ist.)

[alcaeus]

z.B. so:

SQL-Code:

SELECT user_id, count(ship_id) FROM ships GROUP BY user_id

Sobald du alle Daten rausgeholt hast, laeufst du in einer Schleife ueber alle Zeilen hinweg, und updatest alle Benutzerschiffe. Zuerst solltest du den Count auf 0 setzen, und anschliessend auf die jeweilige Anzahl.

Greetz
alcaeus

[3_of_8]

Was verstehst du unter Benutzerschiffe? Ich glaube ich geb mal kurz die Struktur, damit wir nicht aneinander vorbeireden.

Tabelle users
userid INT(11) UNSIGNED
fleet INT UNSIGNED

Tabelle ships
shipid INT(11) UNSIGNED
ownerid INT(11) UNSIGNED

Wobei userid und shipid auch noch autoincrement und primary sind.

Ich will jetzt fleet bei jedem user auf die Anzahl der Schiffe setzen, die als ownerid die userid des jeweiligen Users haben. Meine momentane Abfrage sieht so aus, wird aber wohl nicht funzen:

UPDATE users u LEFT JOIN ships s ON (s.ownerid=u.userid) SET u.fleet=COUNT(s.*)

[alcaeus]

Erstmal alles auf 0 setzen:

Code:

// Erstmal alles auf 0
mysql_query('UPDATE users SET fleet = 0');
// Alle Schiffe zaehlen, gruppiert nach ownerid
$result = mysql_query('SELECT ownerid, count(shipid) as fleet FROM ships GROUP BY ownerid');
// In einer Schleife alle Ergebnisse durchlaufen und updaten
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  mysql_query('UPDATE users SET fleet = '. $row['fleet'] .' WHERE userid = '. $row['ownerid']);
}

Das ist ungetesteter Code, an dem du noch Hand anlegen musst; ich verwende immer nur ein DBAL fuer die Datenbank, und nehme selten die mysql_*-Funktionen *g*

Greetz
alcaeus

[3_of_8]

Die Idee hatte ich auch schon, aber ich dachte, man könnte das auch "in einem Rutsch" machen, so ähnlich wie ich oben.

[mkinzler]

Um Redundanzen zu vermeiden sollte auf das Feld Fleet in der Benutzerdatenbank verzichtet werden und die Anzahl bei Bedarf mit

SQL-Code:

mit SELECT ownerid, count(ship_id) FROM ships GROUP BY ownerid

ermittelt werden.

[alcaeus]

Naja, mach das mal wenn du 10000 User mit je 500 Schiffen hast, nur um ein Beispiel zu nennen: das Query laeuft ewig. Da ist es evtl. schon geschickter, die Anzahl zu "cachen" ;)

Greetz
alcaeus

[mkinzler]

Wenn die Tabellen richtig indiziert sind, dürfte das eigentlich kein Problem sein.

[3_of_8]

Moment... Redundanz... Was heißt das? Doppeldeutigkeit? :gruebel: Naja egal... Ich lass einfach mal meinen Code, der eh schon so ähnlich ist wie der von alcaeus. Danke @all.

[mkinzler]

Zitat:

Moment... Redundanz... Was heißt das? Doppeldeutigkeit?

Wenn ein sachverhalt mehrmals gespeichert ist. ( In deinem Fall explizit im Feld fleet und implizit durch die Anzahl der datensätze mit dem selben Fremschlussel ownerid)

[JocalAreaNetwork]

Zitat:

Zitat von mkinzler

Um Redundanzen zu vermeiden sollte auf das Feld Fleet in der Benutzerdatenbank verzichtet werden und die Anzahl bei Bedarf mit

SQL-Code:

mit SELECT ownerid, count(ship_id) FROM ships GROUP BY ownerid

ermittelt werden.

Das ist natürlich korrekt, aber nicht immer ist die sauberste Lösung auch die sinnvollste - wenn es darum geht z. Bsp ein Onlinegame zu schreiben, dann würde ich stark dazu raten die Daten zu cachen, wie oben schon vorgeschlagen. Natürlich muss dann immer darauf reagiert werden, wenn sich die Schiffsanzahl ändert, aber alles andere kann unter Umständen sehr langsam werden, vor allem, da dies ja nur einer von potentiell sehr vielen Fällen ist.

Grüße
JocalAreaNetwork

[3_of_8]

Jaa, genau. Ein Onlinegame solls werden. As said, ich machs jetzt auf die alcaeus-Art.

[Sharky]

Zitat:

Zitat von 3_of_8

... jedem User den Wert des Feldes fleet eines Users auf die Anzahl der Schiffe setzen, die dem User gehören ...

Hai 3_of_8,

ich frage mal anders herum. Du müsstest doch eigentlich wissen welche Anzahl von Schiffen ein User hat. An irgendeinem Punkt in deinem Code musst Du doch sicher ein neues Schiff in die Tabelle eintragen? Warum nicht an dieser Stelle ein UPDATE in dem Du in der Usertabelle die Anzahl der Schiffe des Users um eins erhöhst?
Pseudo-Code

Code:

insert_schip (userid);
begin
  INSERT INTO ships ('ownerid') VALUES (userid);
  UPDATE users SET flett = fleet + 1 WHERE userid = :userid;
end;

Analog dazu das ganze noch beim löschen und fertig.

[3_of_8]

Joa, das wäre natürlich auch eine Möglichkeit.