frizzle wrote:[color=blue]
> 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() )
>[/color]
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\"> --> Sorry, no forums</td></tr>";
} else {
while ($row1 = mysql_fetch_row($result1)) {
echo "<tr><td> --> {$row1[0]}</td>";
echo "<td>{$row1[1]} threads.</td></tr>";
}
}
echo "<tr><td colspan=\"2\"> </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)
