Posted by L�pher Cypher on 10/08/26 11:36

frizzle wrote:
> Hi there,
>
> Still building my forum.
> I have a certain mysql-query i just can't figure out.
> These are my tables (simplified):
> categories -> id, description
> forums -> id, cat_id, description
> threads -> id, forum_id, date, title, text
>
> I suppose each speaks for itself.
>
> What i want is below, but i just somehow cannot fix the query. I've
> tried AND, GROUP BY, etc.
>
>
> Table Contents:
> Table 'categories'
> - id - description
> 1 Category One
> 2 Category Two
> 3 Category Three
>
>
> Table 'forums'
> - id - cat_id - description
> 1 1 Forum 1
> 2 2 Second Forum
> 3 3 Another Forum in
> category two!
>
> Table 'threads'
> - id - forum_id etc.
> dozens of records.
>
>
> What i need as an output is the following:
> ***************************************
>
> Category One
> --> Forum 1 4567 threads
> --> Second Forum 867 threads
>
> Category Two
> --> Another Forum in category two! 67 threads
>
> Category Three
> --> Sorry, no forums at the moment.
>
> ***************************************
>
> I hope it's clear. I need to get the Categories first,
> then the forums belonging to them (grouped with them)
> and then the number of threads belonging to those forums. ( Count() )
>


Hmm, typos? Category One id=1, Second Forum has id=2 (Category Two).
Same for Another Forum - id=3 - Category Three. Assuming

Table 'categories'
- id - description
1 Category One
2 Category Two
3 Category Three


Table 'forums'
- id - cat_id - description
1 1 Forum 1
2 1 Second Forum
3 2 Another Forum in
category two!

Table 'threads'
- id - forum_id etc.
dozens of records.


and the mentioned output, I'd use two queries, the first to get
categories, and the second to get forums and number of threads:

SELECT * FROM categories

SELECT forums.description,COUNT(threads.id) FROM threads LEFT JOIN
forums ON threads.forum_id=forums.id WHERE forums.cat_id={$cat} GROUP BY
threads.forum_id


So the PHP would look like this:

$query = "SELECT * FROM categories";
$result = mysql_query($query);
if (!$result) { echo mysql_error(); exit; }
echo "<table>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td colspan=\"2\">{$row["description"]}</td></tr>";
$query = "SELECT forums.description,COUNT(threads.id) FROM threads ".

"LEFT JOIN forums ON threads.forum_id=forums.id ".
"WHERE forums.cat_id={$row["id"]} ".
"GROUP BY threads.forum_id";
$result1 = mysql_query($query);
if (!$result) { echo mysql_error(); exit; }
if (mysql_num_rows($result) == 0) {
echo "<tr><td colspan=\"2\"> --&gt; Sorry, no forums</td></tr>";
} else {
while ($row1 = mysql_fetch_row($result1)) {
echo "<tr><td> --&gt; {$row1[0]}</td>";
echo "<td>{$row1[1]} threads.</td></tr>";
}
}
echo "<tr><td colspan=\"2\">&nbsp;</td></tr>";
}
echo "</table>";


Alternatively, you can combine the two queries into one, but you'll get
data redundancy - you can also select categories.description, but it'll
repeat for each forum in the same category, plus you'll have to check
whether next row is in a new category.. So, I'd go with two queries :)



--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)

[Back to original message]