Posted by frizzle on 01/10/06 13:28

Hi there,

I'm building a forum with a mysql backend. The forum has the following
structure:
- category
|--> forum
|--> thread
|--> replies

Once the user has defined a forum (id), i first need to get the name of
the forum,
the category it's in, and how many threads there are in the current
forum.
I use the query below, which works fine, *but* ( shame, shame ) i don't
really understand how/why it works.

I tried to read the mysql manual and everything, but since my native
language isn't english, and there are quite a lot of technical related
words in it, it doesn't really make sence to me.
I built this query myself, with a lot of trial & error work ...

Again, the query works fine, but i don't understand how, and i don't
know wether or not it can be optimized ... (especially the GROUP BY
part i seize to understand)


Frizzle.

**** Q U E R Y *****************************************

mysql_query("

SELECT
f.`title` AS 'forum_title',
c.`title` AS 'cat_title',
COUNT( t.`id` ) AS 'number_of_threads'
FROM
`fo_cats` c, `fo_threads` t
LEFT JOIN `fo_forum` f
ON f.`cat_id` = c.`id`
WHERE f.`id` = $forum_id
AND t.`forum_id` = $forum_id
GROUP BY 'cat_id', 'cat_title'
ORDER BY `cat_id` ASC
LIMIT 1");


**** E N D O F Q U E R Y *****************************************

[Back to original message]