Datenbankabfragen eines Forums

Dear visitor, welcome to Coder Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

philippgerard

Professional

Posts: 292

Tuesday, April 22nd 2008, 7:55pm

Datenbankabfragen eines Forums

Hallo allerseits,

für ein privates Projekt habe ich vor geraumer Zeit ein eigenes Forum programmiert, das lediglich über einen Bruchteil der in kommerziellen Foren angeboteten Funktionen verfügen soll. Da mein Wissen über komplexe SQL-Abfragen leider noch begrenzt ist, scheitere ich derzeit an der Datenbankabfrage, die mir in der Forenübersicht die vorhandenen Foren auslesen soll und ausgeben soll, ob in einem der Foren ein Thema noch ungelesene Beiträge enthält.

Ich speichere derzeit die Forendaten in folgenden Tabellen:

SQL Code

---- Tabellenstruktur für Tabelle `forums`
--
 
CREATE TABLE `forums` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `lastthread` int(11) NOT NULL,
  `private` int(2) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
--
-- Tabellenstruktur für Tabelle `postings`
--
 
CREATE TABLE `postings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `threadid` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
--
-- Tabellenstruktur für Tabelle `threads`
--
 
CREATE TABLE `threads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `forumid` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `sticky` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
--
-- Tabellenstruktur für Tabelle `threadstatus`
--
 
CREATE TABLE `threadstatus` (
  `userid` int(11) NOT NULL,
  `threadid` int(11) NOT NULL,
  `time` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Die Tabelle threadstatus enthält, wie leicht zu sehen, den Zeitpunkt des letzten Zugriffs eines Benutzers x auf ein Thema y. Sofern kein Eintrag zu einem Thema von einem Benutzer vorhanden ist, wurde das Thema noch gar nicht besucht und gilt folglich als ungelesen.

Folgende SQL-Abfragen nutze ich derzeit:

Liste aller Foren

SQL Code

SELECT f.id, f.ORDER, f.name, f.lastthread AS threadid, (
 
SELECT t.title
FROM threads t
WHERE t.id = f.lastthread
) AS threadtitle, (
 
SELECT t.time
FROM threads t
WHERE t.id = f.lastthread
) AS threadtime
FROM forums f
ORDER BY f.ORDER ASC

Liste aller Themen in einem Forum

SQL Code

SELECT t.id, t.sticky, t.userid, t.forumid, t.title, t.time AS threadtime, (
 
SELECT IF( s.time < t.time, 0, 1 ) 
FROM threadstatus s
WHERE s.userid =  '1'
AND t.id = s.threadid
) AS 
STATUS FROM threads t
WHERE t.forumid =  '1'
ORDER BY t.sticky DESC , t.time DESC

Ich möchte nun gern gewissermaßen die Funktionalität beider Abfragen kombiniert wissen, eben um in der Forenübersicht herauszufinden, ob in diesem Forum noch Themen mit ungelesenen Beiträgen bzw. ungelesene Themen enthalten sind und diese als Variable (wie bei der Forenansichts-Abfrage: STATUS) ausgeben.

Kann mir jemand helfen, das zu bewerkstelligen, oder ist meine Datenbankkonzeption sowieso falsch gedacht?

Danke vorab,
Philipp

Torben Brodt

Administrator

Posts: 8,454

Tuesday, April 22nd 2008, 9:15pm

Hast du schon eine Funktion um alle ungelesenen Threads eines Benutzers anzuzeigen? Das wäre wohl der erste Schritt. Das ginge in etwa so:

SQL Code

SELECT 		* 
FROM 		threads t 
LEFT JOIN 	threadstatus ts 
ON 		t.id=ts.threadid 
AND 		ts.userid=** 
WHERE 		ISNULL(ts.threadid)

Danach kannst du dich an die Foren wagen.

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

philippgerard

Professional

Posts: 292

Tuesday, April 22nd 2008, 9:24pm

Vielen Dank für den Hinweis, aber ich wollte es vermeiden, nun alle Foren einzeln in einer Schleife durchlaufen zu müssen. Kann man das in eine - wenn auch komplexe - Abfrage integrieren, die auch die Foren entsprechend des oben angegebenen Musters ausließt?

Torben Brodt

Administrator

Posts: 8,454

Wednesday, April 23rd 2008, 12:00am

Step by Step... wenn die Abfrage funktioniert, joinst du einfach noch die forums davor.
Dann hast du alle Foren die ungelesene Foren enthalten.

Nächster Schritt ist es alle Foren zu erhalten... dann schränkst du nicht mehr mit WHERE ein, sondern machst eine boolean Spalte draus.

Latest blogs: Neuer EX4 Server, Java EE Summit im Juni in München, Kostenlos zur Cebit + Windows 8 Entwickler Schulung

philippgerard

Professional

Posts: 292

Wednesday, April 23rd 2008, 12:45am

Die Abfrage funktioniert leider nicht wie erwartet, was aber auch kein Wunder ist. Ich schrieb oben, dass ungelesene Themen keinen Eintrag haben. Es kann jedoch auch Themen mit Beiträgen geben, die neuer sind als der Zeitpunkt des letzten Zugriffs, sprich ein altes Thema enthält neue Beiträge. Diese Möglichkeit wird derzeit nicht in der Abfrage genutzt (ist jedoch in 95% der Fälle relevant).

Beispiel:

	SQL Code
1	INSERT INTO `threadstatus` (`userid`, `threadid`, `time`) VALUES (1, 1, 1208733525);

SQL Code

INSERT INTO `threads` (`id`, `userid`, `forumid`, `time`, `title`, `sticky`) VALUES (1, 1, 1, 1208733599, 'Todo fürs Forum', 1),

In diesem Fall gibt es einen Eintrag zu dem Thema von dem Benutzer mit der #1. Das Thema wurde folglich schon einmal gelesen. Der letzte Beitrag der eingefügt wurde, ist jedoch jüngeren Datums als der Zeitpunkt des letzten Themenbesuchs des Benutzers #1. Folglich muss das Thema als ungelesen markiert werden.