[MySQL] Forum: Letzter Beitrag von... am...?

bisselbock · 22 Januar 2007

Hallo! Ich brauche eine recht komplexe SQL-Abfrage. Folgende Ausgangssituation:

Code:

-- 
-- Tabellenstruktur für Tabelle 'forum_emoteicons'
-- 

CREATE TABLE forum_emoteicons (
  IconID int(3) unsigned NOT NULL auto_increment,
  Dateiname varchar(100) collate latin1_general_ci NOT NULL,
  IconText varchar(100) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (IconID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle 'forum_posts'
-- 

CREATE TABLE forum_posts (
  PostID int(7) unsigned NOT NULL auto_increment,
  ThreadID int(6) NOT NULL,
  `Text` text collate latin1_general_ci NOT NULL,
  UserID int(5) NOT NULL,
  Erstellt datetime NOT NULL,
  IP varchar(15) collate latin1_general_ci NOT NULL,
  Provider varchar(70) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (PostID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle 'forum_threads'
-- 

CREATE TABLE forum_threads (
  ThreadID int(6) unsigned NOT NULL auto_increment,
  Titel varchar(100) collate latin1_general_ci NOT NULL,
  EmoteIcon int(3) NOT NULL,
  Starter int(5) NOT NULL,
  Klicks int(6) NOT NULL,
  Antwortzahl int(5) NOT NULL,
  Erstellt datetime NOT NULL,
  lastPosterID int(5) unsigned NOT NULL,
  lastPosterTime datetime NOT NULL,
  PRIMARY KEY  (ThreadID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle 'forum_users'
-- 

CREATE TABLE forum_users (
  UserID int(5) unsigned NOT NULL auto_increment,
  Nickname varchar(20) collate latin1_general_ci NOT NULL,
  Vorname varchar(40) collate latin1_general_ci NOT NULL,
  Nachname varchar(50) collate latin1_general_ci NOT NULL,
  Email varchar(60) collate latin1_general_ci NOT NULL,
  ICQ varchar(20) collate latin1_general_ci NOT NULL,
  Passwort varchar(32) collate latin1_general_ci NOT NULL,
  Homepage varchar(200) collate latin1_general_ci NOT NULL,
  Wohnort varchar(50) collate latin1_general_ci NOT NULL,
  Beitragszahl int(5) NOT NULL,
  Erstellt datetime NOT NULL,
  Gesperrt tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (UserID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Dem geschulten Auge wird schnell klar, dass es sich dabei um die DB-Struktur eines einfachen Forums (ohne Kategorien) handelt. Nun möchte ich auf der ersten Seite eine Übersichtsseite mit folgenden Informationen darstellen:
- ein Bildchen (aus forum_emoteicons)
- den Threadnamen (aus forum_threads)
- den Threadstarter (ebenfalls in forum_threads als ID festgehalten und per Join auf forum_users)
- und (jetzt wird es schwieriger) das Datum des letzten Posts sowie den Namen des Users, der diesen Beitrag verfasst hat.

Mit der folgenden Abfrage bekomme ich wirklich schon fast alle Informationen, die ich haben will, nur eben nicht den Usernamen des letzten Posters. Komme ich da mit einer Abfrage ran? Wird schwierig, oder?

Code:

SELECT t.ThreadID, t.Titel, t.Starter, DATE_FORMAT(t.Erstellt,"%d.%m.%y %H:%i") as kurzDatum, t.Antwortzahl, 
u.Nickname, DATE_FORMAT(MAX(p.Erstellt),"%d.%m.%y %H:%i") AS maxdat, e.Dateiname, e.IconText 
FROM forum_threads t, forum_users u, forum_posts p, forum_emoteicons e 
WHERE u.UserID = t.Starter AND t.ThreadID = p.ThreadID AND t.EmoteIcon = e.IconID GROUP BY t.ThreadID, t.Titel 
ORDER BY t.Erstellt 
DESC LIMIT 0,10

Es soll nachher quasi möglich sein, dass ResultSet so zu verwenden, dass ich wie in diesem Forum ausgeben kann "Letzter Beitrag am 13.09.2006
von bisselbock".

Danke für eure Hilfe...

finswimmer · 31 Januar 2007

Hallo,
da es ja immer noch keine Antwort gibt, scheint es ja wirklich etwas schwierig zu sein. Als korrigiert mich, wenn mein Ansatz falsch ist.

Man kann das sicherlich auch mit einer Einzigen Abfrage machen, aber dafür sind meine MySql-Kenntnisse doch zu gering. Vielleicht kann ja jemand meinen Ansatz zu einer Abfrage zusammen führen.

Zuerst kümmern wir uns um das Datum des letzten Posts:

SELECT Erstellt,UserID FROM forum_posts ORDER BY Erstell DESC LIMIT 1

Anhand der UserID kannst du jetzt noch den Usernamen bestimmen.

SELECT Nickname FROM forum_users WHERE UserID = [die UserID die wir vorher ermittelt haben.

Das war es doch schon, oder?

Tschau

tobias

ZeroCCC · 31 Januar 2007

denormalisierung heißt das zauberwort

und das haste ja auch schon richtig angewendet...

PHP:

SELECT
	forum_threads.ThreadID,
	forum_threads.Titel,
	forum_threads.Erstellt,
	forum_threads.lastPosterTime,
	
	forum_users.Nickname
FROM
	forum_threads INNER JOIN
	forum_users ON (forum_threads.lastPosterID = forum_users.UserID)

TriloByte · 1 Februar 2007

Also ich würde im Sinne der Performanz die zwei Attribute redundant in jedem Eintrag in der Thema-Tabelle speichern. Grund: Joins fressen gut Rechenzeit besonders wenn du sie im WHERE - Konstrukt umsetzt anstatt die JOIN-Syntax zu verwenden. Gerade bei einem Forum, wo du sehr viele read-Anfragen hast, ist Redundanz zugunsten von Effizienz eher hinnehmbar. Netter Nebeneffekt: deine Anfragen werden einfacher

.

(Jedesmal, wenn in einem Thema ein neuer Beitrag geschrieben wird, mußt du dann natürlich die themen-Tabelle updaten und username/date vom letzten Post eintragen)

bisselbock · 2 Februar 2007

Es war in der Tat nicht so einfach, wie man es denken würde. Schon ziemlich knifflig, aber ich hatte auch in anderen Foren um Hilfe gebeten.

@TriloByte
Genauso habe ich es letztendlich auch gemacht. Ich sträube mich nur grundsätzlich gegen solche Redundanzen, weil es prinzipiell genau das Gegenteil davon ist, was man in den Datenbank-Vorlesungen der Universitäten so gelehrt bekommt :biggrin:

.

Also vielen Dank nochmal für die Hilfe und wenn ich nochmal Hilfe brauche (durchaus nicht augeschlossen ^^), wende ich mich wieder an euch :ugly:

.

ZeroCCC · 2 Februar 2007

bisselbock schrieb:
was man in den Datenbank-Vorlesungen der Universitäten so gelehrt bekommt .

und was hat ne datenbankvorlesung inner universität mit ner datenbank zu tun geschweige denn mit der praxis

PS: du kannst auch die datenbank dazu bringen die daten redundand zu speichern

da brauchste noch nichtmal selbst nen finger krum machen...

bisselbock · 2 Februar 2007

PS: du kannst auch die datenbank dazu bringen die daten redundand zu speichern da brauchste noch nichtmal selbst nen finger krum machen...

Tell me more!

ZeroCCC · 2 Februar 2007

moderne datenbanken bieten dir die möglichkeit trigger zu erstellen. trigger sind nix andere als wie "wenn x passiert mache y". sprich x könnte zb sein das ein neuer datensatz in posts hinzugefügt wird.

mal nen code bsp für mysql...

PHP:

CREATE TRIGGER 
    t_autolasttime
AFTER INSERT ON
    forum_posts
FOR EACH ROW
    UPDATE 
       forum_threads 
    SET
       lastPosterId = NEW.UserID
       lastPosterTime = NOW()
    WHERE
       forum_threads.ThreadID = NEW.ThreadID

damit sagt man erstmal man möchte ein trigger erstellen der t_autolasttime heißt und immer nach einen INSERT in forum_posts ausgeführt wird. Als aktion wird hier für jede neue zeile die eingefügt wurde ein UPDATE von forum_threads gemacht...

das ganze muss man dann nochmal mit DELETE machen...

PHP:

CREATE TRIGGER 
    t_autolasttime
AFTER DELETE ON
    forum_posts
FOR EACH ROW
    BEGIN
       SELECT
           @lastPosterID := UserID,
           @lastPosterTime := Erstellt
       FROM 
           forum_posts
       WHERE
       	   forum_posts.ThreadID = OLD.ThreadID
       ORDER BY
           Erstellt DESC
       LIMIT
       	   1;

       UPDATE 
           forum_threads 
       SET
           lastPosterId = @lastPosterID
           lastPosterTime = @lastPosterTime
       WHERE
           forum_threads.ThreadID = OLD.ThreadID;
   END

und schon übernimmt die datenbank die komplette arbeit für dich

nen update trigger kann man sich ersparen... ist in dem fall ja sinnlos, ansonsten würde das genauso wie das INSERT aussehen.

bisselbock · 2 Februar 2007

Das ist eine sehr geile Sache. Trigger kenne ich von Oracle-Datenbanken, aber ich wusste nicht, dass es die auch so komfortabel bei MySQL gibt. Funktioniert das auch problemlos bei jedem Hoster? In meinem speziellen Fall handelt es sich um all-incl.com, die ja einen sehr guten Ruf haben. Man kann Trigger ja sicher auch deaktivieren. :roll:

ZeroCCC · 2 Februar 2007

bisselbock schrieb:
Das ist eine sehr geile Sache. Trigger kenne ich von Oracle-Datenbanken, aber ich wusste nicht, dass es die auch so komfortabel bei MySQL gibt. Funktioniert das auch problemlos bei jedem Hoster? In meinem speziellen Fall handelt es sich um all-incl.com, die ja einen sehr guten Ruf haben. Man kann Trigger ja sicher auch deaktivieren.

trigger sind relativ neu in mysql... die gibts erst aber mysql 5. und all-inkl setzt mysql4 ein (jedenfalls auf dem server auf dem ich space hab), also wirds damit leider pustekuchen. extra deaktivieren kann man die glaub ich nicht...

bisselbock · 2 Februar 2007

Daran liegt es also. Sie sind noch neu. Dass all-incl.com MySQL 4 einsetzt, habe ich leider kürzlich erfahren müssen, als ich schon mal eine etwas neuere Funktion nutzen wollte.

Nun ja, ist jetzt kein Beinbruch, wobei die Lösung mit den Triggern natürlich extrem genial, elegant und sicher wäre...

. Aber nun ja, ich kann damit leben

TriloByte · 3 Februar 2007

Ja, so langsam macht sich mysql. Auch Views, Prozeduren und ein effizienteres Transaktionsmanagement machen das DBMS jetzt durchaus attraktiv. Leider läuft die 5er version noch bei den wenigsten Hostern.

ZeroCCC · 3 Februar 2007

TriloByte schrieb:
Ja, so langsam macht sich mysql. Auch Views, Prozeduren und ein effizienteres Transaktionsmanagement machen das DBMS jetzt durchaus attraktiv. Leider läuft die 5er version noch bei den wenigsten Hostern.

von daher mal ab und zu den eigenen hoster ein weniger nerven

wenns keiner will dann stellt auch der hoster nicht so schnell um...

ice-breaker · 3 Februar 2007

ZeroCCC schrieb:
von daher mal ab und zu den eigenen hoster ein weniger nerven wenns keiner will dann stellt auch der hoster nicht so schnell um...

das stimmt, man muss den Hoster nur mal Nerven und er machts^^
Natürlich geht das mit keinen Massenhostern

[MySQL] Forum: Letzter Beitrag von... am...?

bisselbock

Fußballtrainer

finswimmer

Well-known member

ZeroCCC

wasn das?

TriloByte

Well-known member

bisselbock

Fußballtrainer

ZeroCCC

wasn das?

bisselbock

Fußballtrainer

ZeroCCC

wasn das?

bisselbock

Fußballtrainer

ZeroCCC

wasn das?

bisselbock

Fußballtrainer

TriloByte

Well-known member

ZeroCCC

wasn das?

ice-breaker

return void

Ähnliche Themen