phpBB.de

Code: Alles auswählen

SELECT c.cat_title Kategorie,
       f.forum_name Forum,
       f.forum_posts 'Anzahl Beiträge',
       f.forum_topics 'Anzahl Threads',
       FROM_UNIXTIME(p.post_time, '%Y-%m-%d %H:%i:%s') 'letzter Beitrag am',
       datediff(now(), FROM_UNIXTIME(p.post_time, '%Y-%m-%d %H:%i:%s')) AS 'kein_Beitrag_seit x Tagen',
       sum(t.topic_views) 'Anzahl Aufrufe'
from categories c, forums f, posts p, topics t
WHERE f.cat_id = c.cat_id
  AND f.forum_last_post_id = p.post_id
  AND t.forum_id = f.forum_id
GROUP BY f.forum_name
ORDER BY c.cat_order, f.forum_order, f.forum_name

Code: Alles auswählen

SELECT c.cat_title Kategorie,
       f.forum_name Forum,
       f.forum_posts 'Anzahl Beiträge',
       f.forum_topics 'Anzahl Threads',
       FROM_UNIXTIME(p.post_time, '%Y-%m-%d %H:%i:%s') 'letzter Beitrag am',
       datediff(now(), FROM_UNIXTIME(p.post_time, '%Y-%m-%d %H:%i:%s')) AS 'kein_Beitrag_seit x Tagen',
       sum(t.topic_views) 'Anzahl Aufrufe'
from categories c
INNER JOIN forums f
  ON f.cat_id = c.cat_id
LEFT JOIN posts p
  ON f.forum_last_post_id = p.post_id
LEFT JOIN topics t
  ON t.forum_id = f.forum_id
GROUP BY f.forum_name
ORDER BY c.cat_order, f.forum_order, f.forum_name