Mit PHP & SQL relevante Blogeinträge zeigen

Rein optisch hat sich bis auf einen dunkleren Hintergrund und grafischen Social-Bookmark-Icons an der Seite nichts geändert. Die größere Neuerung hat sich hinter den Kulissen abgespielt, so werden in der Detailansicht zu einem Eintrag jetzt ähnliche Beiträge angezeigt - zumindest sofern diese eine gewisse Relevanz erreichen.

Funktionieren tut dies über die Tags oder auch Schlagworte, die einem Beitrag zugeordnet sind. Wenn Beiträge eine bestimmte Anzahl an gleichen Tags haben, werden sie als verwandt betrachtet. Wobei sich diese Grenze dynmasch nach oben verschiebt, je mehr übereinstimmende Tags der Beitrag mit den meisten gleichen Tags aufweist.

Von der Datenbank-Struktur her habe ich 2 Tabellen: Einmal die Tabelle in der die Blogeinträge sind (eld_blog, Spalten: id, ueberschrift) und dann noch die Tabelle, in der die zugehörigen Tags gespeichert sind (eld_tags, Spalten: tag, blogeintrag) - die für dieses Beispiel unrelevanten Felder in den Tabellen lasse ich mal aussen vor:

Ich muss also die Tags auslesen, die zu meinem gerade angezeigten Beitrag gehören. Über diese kann ich dann prüfen ob andere Beiträge dieselben Tags benutzen und wieviele davon mit denen von meinem gerade angezeigten Beitrag übereinstimmen. Mit einem Join auf die blog-Tabelle kann ich dann die Überschriften und anderen Daten der jeweiligen Blogeinträge auslesen. In SQL ausgedrückt, geht das zum Beispiel so:

SELECT DISTINCT (blog.id), blog.ueberschrift, 
(
SELECT COUNT( a.id ) FROM eld_tags AS a 
LEFT JOIN eld_tags AS b ON a.tag = b.tag
WHERE a.blogeintrag = t1.blogeintrag
AND b.blogeintrag = t2.blogeintrag
) 
AS hits FROM eld_tags AS t1, eld_tags AS t2
LEFT JOIN eld_blog AS blog ON t2.blogeintrag = blog.id
WHERE t1.blogeintrag = XX AND t1.tag = t2.tag
AND t2.blogeintrag != XX ORDER BY hits DESC
-- XX ist die ID des Blogeintrags der abgefragt wird.

Das alles sieht zwar nach einer Menge Holz aus und man hätte es natürlich auch auf mehrere Abfragen splitten und die Berechnungen in PHP machen können. Die Lösung oben ist aber deutlich effektiver und schneller, weil man folgende Ergebnis-Tabelle zurückkriegt:

Das Ergebnis der Datenbank Abfrage lässt sich anschließend relativ leicht mit PHP weiterverwursten. So kann man prüfen welcher Beitrag am meisten hits hat und daraus eine beliebige Relevanzgrenze berechnen, usw. Die SQL-Abfrage kann natürlich auch um beliebige Felder erweitert werden, so dass man noch nach Datum sortieren kann, etc.

Wenn ihr Fehler, Verbesserungen oder Anmerkungen habt, her damit!

Daniel, 30. August 2008 um 15:54 Uhr // 0 Kommentare